602SQL Documentation Index  

Transaction Isolation

A database client may set the transaction isolation level of transactions. Transaction isolation specifies how database modifications made by simultaneously running clients will affect each other.

Transaction isolation levels are defined by their ability to prevent the occurrence of three situations that may occur when two clients are working simultaneously (Client A and Client B):

DIRTY READ: Client A modifies data but does not terminate the transaction. Client B reads the modified data. Client A then issues a ROLLBACK. Client B has therefore read data that was not committed.

NON-REPEATABLE READ: Client A reads data but does not terminate the transaction. Client B modifies or deletes this data, then executes COMMIT. Client A tries to read the same data again in the transaction, but fails to find the deleted data.

PHANTOM: Client A executes a query, reads the result, but does not terminate the transaction. Client B inserts new rows into the database that fulfill the conditions in the query from Client A and runs COMMIT. Client A executes the same query again in the transaction and receives different results.

The individual transaction isolation levels are defined as follows:

READ UNCOMMITTED may occur may occur may occur
READ COMMITTED may not occur may occur may occur
REPEATABLE READ may not occur may not occur may occur
SERIALIZABLE may not occur may not occur may not occur

The SERIALIZABLE isolation level guarantees that simultaneous transactions will have the same results as if they were executed alone. However this isolation setting also reduces the server ability to execute actions simultaneously, therefore the server performance will decrease if more clients work with the same data. The count of temporary locks on read data also significantly increases.

The following is true for explicit transactions:

The following is true for default transactions:

602SQL server internally implements READ COMMITTED and SERIALIZABLE isolation levels only. If you set READ UNCOMMITTED, READ COMMITTED will be set internally. If you set REPEATABLE READ, SERIALIZABLE will be used internally. This behaviour is valid according to the SQL standard.