Chapter Summary

• The choice of transaction isolation level affects both query results and performance because of blocking and row versioning.

• Use the least restrictive transaction isolation level.

• When a more restrictive transaction isolation level is required, consider applying table locking hints rather than specifying transaction isolation level on the session level using the SET TRANSACTION ISOLATION LEVEL statement.

• Keep transactions short. Open the transaction as late as possible, and close it as early as possible.

• Design transactions to minimize deadlocks.

• Consider alternate solutions to locking by using the @@ROWCOUNT function and the OUTPUT clause.

• When not rolling back a transaction in a try/catch block, always verify that the transaction is not uncommittable by querying the XACT_STATE() function.