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 ;

READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

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.

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE