Implementation of Transaction in SQL
Transaction is a logical unit of work and is guaranteed to be atomic. A single SQL statement is always considered to be atomic either it complete execution without an error or it fails and leave the database unchanged.
There is no explicit begin transaction statement with in SQL. The transaction initiation is done implicitly when particular SQL statements are encountered.
A transaction must have an explicit end statement, which is either a commit or rollback.
Characteristics of Transaction :
The transaction characteristics are specified by a set transaction statement in SQL. The various characteristics of the transaction are :
- Access mode
- Diagnostic area size
- Isolation level
Access mode :
There are two types of access mode read only and read write. The default is read write unless the isolation read uncommitted is specified.
- Read only : Read only access mode is simply used for data retrieval.
- Read write : Read write access mode allows select, update, insert, delete and create command to be executed.
Diagnostic area size :
It determine the number of error conditions that can be recorded on the user on the most recently executed SQL statement.
The ‘n’ is a parameter taking an integer value which indicates the number of conditions that can be held simultaneously in the Diagnostic area. This condition supply feedback information ( error or exceptions) to the user or program on the ‘n’.
Isolation level :
It controls the extent to which a transaction is exposed to the actions of other transactions executing concurrently. It is specified using the statement ISOLATION LEVEL . The value of can be
- Read uncommitted
- Read committed
- Repeatable read
The default isolation for some system is serializable and for some systems is read committed.
The serializable does not allow violations that cause dirty read, unrepeatable read, phantoms.
Dirty read :
A transaction T1 may read the update of a transaction T2, which has not yet completed. If T2 fails and is aborted then T1 would have read a value that does not exist and is incorrect.
Non repeatable read :
A transaction T1 may read a given value from a Table. If another transaction T2 letter update that value and T1 reads that value again, T1 will see a different value.
Transaction T1 may read a set of rows from a Table, perhaps based on some condition specified in the SQL WHERE clause. Now suppose that a transaction T2 insert a new row that also satisfies the WHERE clause condition used in T1, into the table used by T1. If T1 is repeated, then T1 will see a Phantom, a row that previously did not exist.
Possible Violations Based on Isolation Levels as Defined in SQL :
|Difference between Conflict and View Equivalent||Concurrency Control Protocol|