What is a Database Transaction?
A database transaction indicates a unit of work performed for data retrieval or updates to a database. If you are creating a record, updating a record, or deleting a record from a table, then you are performing a transaction on that table. The simplest example would be a bank transaction.
Suppose an employee transfers $100 from account A to account B; these would be the following tasks performed under the transaction :
Old_Balance = A.balance
New_Balance = Old_Balance - 100
A.balance = New_Balance
Old_Balance = B.balance
New_Balance = Old_Balance + 100
B.balance = New_Balance
- Atomicity – Atomicity requires that each transaction is “all or nothing”- if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.
- Consistency – The consistency property ensures that any transaction will bring the database from one valid state to another.
- Isolation – The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e. one after the other.
- Durability – Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.
Transaction control in SQL
Let’s say there is a need to save, undo or change the characteristic (read/write only) of the bank transaction example shown above. To enable such modifications, we would need the help of transaction controls. The following commands are used to control transactions in SQL:
- COMMIT – The COMMIT command is a transactional command used to save changes invoked by a transaction to a database.
- ROLLBACK – The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
- SAVEPOINT – The SAVEPOINT command is the transactional command used to roll the transaction back to a certain point without rolling back the entire transaction.
Syntax: SAVEPOINT SAVEPOINT_NAME;
- SET TRANSACTION – The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read-only or read-write.
Syntax: SET TRANSACTION [ READ WRITE | READ ONLY ];
A database transaction is essentially a unit of work that is either completed as a whole or undone as a whole. Proper database transaction processing is critical to maintaining the integrity of the databases.
Transactions in a database have two main purposes:
- To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure
- To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the program’s outcomes are possibly erroneous