A SQL transaction is a grouping of one or more changes to the database. Transactions help ensure a consistent state in your database. The common terms in SQL transactions are COMMIT and ROLLLBACK. Commit makes the changes permanent and rollback cancels the changes.
There are four properties of transactions to keep in mind:
- Atomicity: This ensures all changes in a transaction are completed successfully. If they are successful, the changes are committed. If any change isn't successful, all the changes are rolled back.
- Consistency: This ensures any changes can't violate the database's integrity, including constraints. Changes interrupted by errors due to violations of database integrity are rolled back. This includes any changes that don't violate database integrity.
- Isolation: All transactions are isolated from each other so that no other transaction can interfere with the other transactions that are running.
- Durability: Once a transaction is committed, any interruption to the database's availability, such as a restart or system failure, will not affect the consistency of the data.
These four properties are referred to as ACID. To understand ACID a bit better, let's go through a couple of examples. Let's say you want to transfer money from your checking account into your savings account. For atomicity, the transfer fails unless the balance is updated in the checking account and in the savings account. You don't want the money to go missing because only the checking account is reduced by the amount but the savings account isn't increased by the same amount. For consistency, any error or database integrity violation that happens while the transfer completes causes a rollback to ensure the checking and savings balances are unaffected. For isolation, all banking transactions are isolated from each other, so another person's transfer doesn't affect yours. Lastly, for durability, once the transfer is committed to the database, any interruption in the database's availability doesn't affect your bank balances.