Transaction within a transaction

Classic Servoy
1

Hi, does Servoy allow multiple transactions like transaction within a transaction.

Can we have two difference transaction or can we have transactions with IDs just like the example below?
Transaction1.startTransaction();
Transaction1.save(foundsetA.record1);
Transaction2.startTransaction();
Transaction2.save(foundsetB.record2);
Transaction2.commitTransaction();
Transaction1.commitTransaction();

Thanks.

2

Erik,

Servoy does not support nested transations.

Rob

3

Ok, thanks for the reply.

4

rgansevles:
Erik,

Servoy does not support nested transations.

Rob

Still not supported?
Any plans on including nested transactions in upcoming releases?

Thanks,
Maria

5

Hi Erik/Maria,

Nested transactions (i.e. autonomous transactions) are not in the SQL standard and the vendors that do have this implemented do it their own way.
What is in the SQL standard are nested savepoints which pretty much all vendors (including SQLite!) support.
You can set (multiple) savepoints during your transaction and rollback to a savepoint. You can’t however separately commit a savepoint (that would be an autonomous transaction).

But the real question is what is your use case for nested transactions? Perhaps it can easily be done using savepoints.

6

ROCLASI:
Hi Erik/Maria,

Nested transactions (i.e. autonomous transactions) are not in the SQL standard and the vendors that do have this implemented do it their own way.
What is in the SQL standard are nested savepoints which pretty much all vendors (including SQLite!) support.
You can set (multiple) savepoints during your transaction and rollback to a savepoint. You can’t however separately commit a savepoint (that would be an autonomous transaction).

But the real question is what is your use case for nested transactions? Perhaps it can easily be done using savepoints.

We are implementing multiple floating windows now (I have just created another topic (viewtopic.php?f=22&t=16408) for it to discuss a few questions).
Each window has its own separate foundset and one can be in edit mode while the other needs to be saved or rolled back.
If we had nested transactions I’d assume we could start a separate transaction each time we open a new window and save or cancel it accordingly.
Otherwise, we need a way to figure out how to save that particular foundset AND its related records - the related records are the biggest problem.

Perhaps, the definitions here are wrong. It’s not exactly about nested transaction but more about separate data environments.
For examples, if I had a separate foundset and wanted to save or rollback all of the foundset and its related records without interfering with other records of other foundsets that are in edit mode.
I hope I made myself clear.

Otherwise, we seem to have to figure out for each form what we should save/rollback except its main record.

Cheers,
Maria

7

Having separate transactions and then even more the 2 at the same time, and those are quite long (so it is not a simple sub second start,send edited data, commit)
But a way longer transaction as long as the window is open or as long as the user is editing and many edits can send to the db.
Then you will with many databases end up in maybe even deadlocks or at least one waiting on the other to commit…

Oracle and Postgresql are the 2 exceptions i think in this area where that last thing doesn’t happen, but the rest will wait quite fast when 1 transaction has uncommitted data on a table and the other wants to read from that table