upd1: I’ve read PostgreSQL docs Transaction Isolation
what isolation level is applied if we use the DB Transaction toggle?
Hi, didn’t find info on this feature in manuals or faqs of Appmaster. Could you please provide more info on what exactly it does.
In my specific case I have data in a DB that can be edited simultaneously by several users/processes. I therefore need to maintain the integrity of the data.
If I switch this toggle on, will it only rollback the partial changes to the database if not all of them within this business process are successful or will it also check if the data in the db has been altered after the db search in the beginning of the process and before the process tries to update this data in the DB?
I see a “single thread” option in the BP settings, but this would slow down the app, while not being necessary when this function manipulates different data blocks of the data base. For example if I were a bank and the function that updates the balance of the account were a single thread function, then updating account balance of different accounts would be executed consecutively, which is not needed - I only need to check if the balance of an account has been updated after I db searched it in the process and before I try to update it.
@S.V Very good question & we have a number of solutions to choose from.
Standard solutions using BP blocks:
Transaction mode toggle at a BP level wraps all DB calls in that BP into a single transaction. In a case if there will be an error in DB ops, it will automatically rollback changes. We need to double check behavior to clarify about types of errors that will trigger rollback (DB errors or BP errors as well).
You can use TRX blocks in BP to manually create transactions, savepoints and rollback/commit. It gives more granular control.
Singleton mode toggle at a BP level makes all call to that BP sequential, effectively disabling parallelism. It’s ideal when you deal with not thread-safe logic and has significant performance penalty in some cases.
But in exactly your case you can go one of this two ways: 1. By using SQL exec make thread-safe operations to manipulate balance in DB:
UPDATE account SET balance = balance + 20 WHERE id = 55
Everything is handled by DB, no need to lock anything.
2. By using SQL exec work with SELECT FOR UPDATE statements.
BEGIN;
SELECT * FROM account WHERE id = 55 FOR UPDATE;
-- Perform your ops
COMMIT;
Please note, that with FOR UPDATE statement if record is locked by another transaction it will wait. To immediately fail query if record is already locked use FOR UPDATE NOWAIT