- 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.
© Microsoft. All Rights Reserved.