This short write-up focuses on a different transaction control behavior of databases. Though this is not unusual, I decided to write an article on rolling back transactions to a particular point. I selected this topic because I found many people are not aware of this feature in databases.
Description
Every ACID-compliant RDBMS follows the “All or None” concept. In other words, all the changes made by transactions are written to the database, or none of them are reflected.
For example, in the below transaction:
1 2 3 4 5 6 7 8 9 | BEGIN; STATEMENT 1 STATEMENT 2 STATEMENT 3 STATEMENT 4 STATEMENT 5 STATEMENT 6 STATEMENT 7 END; |
If all the statements have been executed successfully, at the execution of COMMIT or END, all the changes made by 7 statements will be permanently written to the database. But if any of the 7 statements throws an error, it will be rolled back, and no changes will be written to the database.
Now, if we refer to the below set of statements.
1 2 3 4 5 6 7 8 9 | BEGIN; STATEMENT 1 STATEMENT 2 STATEMENT 3 STATEMENT 4 STATEMENT 5 STATEMENT 6 STATEMENT 7 ROLLBACK; |
Though the changes will be made to the database, none of them will be applied. This happens because of the very concept of atomicity (A of ACID).
Many people wonder if it’s possible to apply the transaction partially. For instance, if I want to rollback a transaction to, let’s say, statement 3 or 4 during a running transaction, it is possible. Indeed, it is.
The concept of savepoint
During a transaction, we can create different markers after the point till we want. Those are also called savepoints. Any transaction can be rolled back to a certain savepoint. Below is an example describing the same.
1. Start a transaction
1 2 3 4 5 6 7 8 9 10 | test=# begin; BEGIN test=*# insert into t1 values(generate_series(1,100)); INSERT 0 100 test=*# select count(*) from t1; count ------- 100 (1 row) |
2. Create a savepoint
1 2 3 4 5 6 7 8 9 10 11 | test=*# savepoint s1; SAVEPOINT test=*# delete from t1 where id > 50; DELETE 50 test=*# select count(*) from t1; count ------- 50 (1 row) |
3. Rollback to the created savepoint
1 2 3 4 5 6 7 8 | test=*# rollback to savepoint s1; ROLLBACK test=*# select count(*) from t1; count ------- 100 (1 row) |
Conclusion
Many people are unaware that transactions can be restored to a certain point. The savepoint feature can help us achieve this.
