A transaction is a sequence of operations performed (using one or more SQL statements) on a database as a single logical unit of work. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database). A database transaction must be atomic, consistent, isolated and durable. Later we have discussed these four points.
To understand the concept of a transaction, consider a banking database. Suppose a bank customer transfers money from his savings account (SB a/c) to his overdraft account (OD a/c), the statement will be divided into four blocks:
- Debit SB a/c.
- Credit OD a/c.
- Record in Transaction Journal
- End Transaction
The SQL statement to debit SB a/c is as follows :
SET balance = balance - 1000
WHERE account_no = 932656 ;
The SQL statement to credit OD a/c is as follows :
SET balance = balance + 1000
WHERE account_no = 933456 ;
The SQL statement for record in transaction journal is as follows :
(100896, 'Tansaction on Benjamin Hampshair a/c', '26-AUG-08' 932656, 933456, 1000);
The SQL statement for End Transaction is as follows :
A database transaction must be atomic, consistent, isolated and durable. Bellow we have discussed these four points.
Atomic : A transaction is a logical unit of work which must be either completed with all of its data modifications, or none of them is performed.
Consistent : At the end of the transaction, all data must be left in a consistent state.
Isolated : Modifications of data performed by a transaction must be independent of another transaction. Unless this happens, the outcome of a transaction may be erroneous.
Durable : When the transaction is completed, effects of the modifications performed by the transaction must be permanent in the system.
Often these four properties of a transaction are acronymed as ACID.
We have explained the above four properties of a translation with the following example :
Example of Fund Transfer
- Transaction to transfer $1000 from account X to account Y:
- X = X –1000
- Y = Y + 1000
- Atomicity requirement — if the transaction fails after step 3 and before step 6, the system should ensure that its updates are not reflected in the database, else an inconsistency will result.
- Consistency requirement – the sum of X and Y is unchanged by the execution of the transaction.
- if between steps 3 and 6, another
transaction is allowed to access the partially updated database, it will
see an inconsistent database (the sum X + Y will be less than it
- Isolation can be ensured trivially by running transactions serially, that is one after the other.
- However, executing multiple transactions concurrently has significant benefits, as we will see later.
- Durability requirement — once the user has been notified that the transaction has completed (i.e., the transfer of the $1000 has taken place), the updates to the database by the transaction must persist despite failures.
Beginning a Transaction
A transaction is beginning to initiate the execution of multiple SQL statements. Beginning of a transaction guarantees the atomicity of a transaction. After beginning, either it can be committed to making the modifications permanent or rolled back to undo the changes to leave the database unaltered.
Committing a Transaction
By committing a transaction, it is closed explicitly and modifications performed by the transaction is made permanent.
Rolling Back a Transaction
By rolling back a transaction, a transaction is explicitly closed and any modifications made by the transaction is discarded.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [7 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Interview question/Tips of the Day
DML: Data Manipulation Language
DML contains statements to
- Insert data
- Delete data
- Change data
insert, delete, update
- New Content published on w3resource:
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework