One Pager Cheat Sheet

• A SQL transaction is a collection of one or more SQL statements that communicate with a database, typically used for preserving database integrity in cases of concurrent execution of multiple related operations or interactions.
• By guaranteeing atomicity, ensuring consistency, allowing for isolation, and providing durability, SQL Transactions uphold the ACID properties.
• Atomicity is a ACID property that ensures that a transaction is either fully committed or not at all, preventing any inconsistent data from being stored in the database.
• The transaction control commands BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT and SET TRANSACTION are used to manage, save, reverse and name transactions.
• We will perform two SQL transactions on the table shown to illustrate examples of transactions.
• By running a BEGIN TRANSACTION, entering various SQL statements such as INSERT, UPDATE commands and then COMMIT TRANSACTION, all the changes will be saved to the database and cannot be reverted back.
• All changes during a SQL transaction are undone using the ROLLBACK command, so that the resulting table looks exactly the same as before.
• The only way to recover the database to its previous state after a COMMIT statement is executed is by executing a ROLLBACK command, however this will only reverse actions taken after the last COMMIT or ROLLBACK command.