The degree to which one transaction can interfere with other transactions by accessing the same rows concurrently is determined by setting the transaction isolation level in the program.
This is the syntax for the SET TRANSACTION ISOLATION LEVEL statement:
Syntax
SET TRANSACTION ISOLATION LEVEL isolation_level_name ;
SQL defines isolation levels in terms of the inconsistencies they allow:
READ UNCOMMITTED
Also called a "dirty read." When this isolation level is used, a transaction can read uncommitted data that later can be rolled back. A transaction that uses this isolation level can only fetch data but cannot update, delete, or insert data.
READ COMMITTED
With this isolation level dirty reads are not possible, but if the same row is read repeatedly during the same transaction, its contents can be changed or the entire row can be deleted by other transactions.
REPEATABLE READ
This isolation level guarantees that a transaction can read the same row many times, and it will remain intact. However, if a query with the same search criteria (the same WHERE clause) is executed more than once, each execution can return different sets of rows. This can happen because other transactions are allowed to insert new rows that satisfy the search criteria or update some rows in such way that they now satisfy the search criteria.
SERIALIZABLE
This isolation level guarantees that none of the above happens. In addition, it guarantees that transactions that use this level will be completely isolated from other transactions.
The following table identifies which phenomena are either permitted or prevented by each isolation level.
Table 10. Transaction isolation levels
Isolation
Dirty read
Nonrepeatable read
Phantom read
READ UNCOMMITTED
Permitted
Permitted
Permitted
READ COMMITTED
Prevented
Permitted
Permitted
REPEATABLE READ
Prevented
Prevented
Permitted
SERIALIZABLE
Prevented
Prevented
Prevented
Note: The isolation levels are ordered according to the phenomena they either permit or prevent. The first one, READ UNCOMMITTED, is the isolation level providing the highest level of concurrency but with the lowest level of consistency.Each subsequent level provides at least as much data consistency as the one before but will result in less concurrency. As a general rule, the more data consistency that is provided by the isolation level used from an application, the less concurrency is allowed between this application and other applications connected to the same database.
