Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 16 June 2015

To resolve Deadlock other than Nolock hints

Please do not use NOLOCKs blindly. NOLOCKS results in dirty reads which can become a problem for your application. NOLOCKS are suggested when the tables thay are used against are seldom changed. If your tables have frequent transactions on them, NOLOCKS are not recommended.
To help minimize deadlocks, you can use any of the below suggesstion-
  1. Access objects in the same order.
  2. Avoid user interaction in transactions.
  3. Keep transactions short and in one batch.
  4. Use a lower isolation level.
  5. Use a row versioning-based isolation level. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning. OR. Use snapshot isolation.
  6. Use bound connections.
Check this link for further details in achieveing this.