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.

< Back     



© Microsoft. All Rights Reserved.