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 :
The SQL statement to debit SB a/c is as follows :
The SQL statement to credit OD a/c is as follows :
The SQL statement for record in transaction journal is as follows :
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 :
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.
By committing a transaction, it is closed explicitly and modifications performed by the transaction is made permanent.
By rolling back a transaction, a transaction is explicitly closed and any modifications made by the transaction is discarded.
Solve these problems: