Problems when Combining Explicit Lock Placing and Transaction Isolation

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:

Isolation level	DIRTY READ	NON-REPEATABLE READ	PHANTOM
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 transaction isolation level is set for each transaction separately.
The isolation level may be set on transaction execution using the START TRANSACTION statement or when the transaction is active using the SET TRANSACTION statement.
If no isolation level is specified, SERIALIZABLE will be used.

The following is true for default transactions:

You cannot set transaction isolation level for individual default transactions.
The transaction isolation level for previous (explicit or default) transactions is used.
If the previous rule is not applicable, READ COMMITTED will be used.

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.

Transactions and Savepoints

Problems when Combining Explicit Lock Placing and Transaction Isolation