In a multiuser database application, you must minimize blocking among concurrent transactions.
Keep transactions short
• Perform the minimum steps/logic within a transaction.
• Do not perform costly external activity within a transaction, such as sending acknowledgment email or performing activities driven by the end user.
Optimize queries using indexes
• Create indexes as required to ensure optimal performance of the queries within the system.
• Avoid a clustered index on frequently updated columns. Updates to clustered index key columns require locks on the clustered index and all nonclustered indexes (since their row locator contains the clustered index key).
• Consider using a covering index to serve the blocked SELECT statements.
Consider partitioning a contended table
Use query timeouts or a resource governor to control runaway queries
Avoid losing control over the scope of the transactions because of poor error-handling routines or application logic
SET XACTABORT ON to avoid a transaction being left open on an error condition within the transaction.
• Execute the following SQL statement from a client error handler (
TRY/CATCH) after executing a SQL batch or stored procedure containing a transaction:
IF @@TRANCOUNT > 0 ROLLBACK
Use the lowest isolation level required
• Use the default isolation level (Read Committed).
• Consider using row versioning to help reduce contention.