If I have a transaction that saves data in the database, but this transaction is part of another transaction that needs the id(fk) of that data to create a new data, but an error occurs at that moment, all transactions are rolled back or just the last transaction?

CodePudding user response：

They will both rollback unless you set up savepoints. A transaction "within transaction" is actually the same transaction. A plain ROLLBACK will revert everything, even if there are savepoints set up. A ROLLBACK TO savepoint will only undo operations that occured from that point onwards, keeping everything that took effect beforehand, still within the same transaction.

Here's a demo - observe the state of the table compared to transaction id.

Psycopg2 lets you cur.execute('SAVEPOINT s1'), then cur.execute('ROLLBACK TO s1').