Zero SQL deadlock by design – any coding patterns?

by

Writing deadlock-proof code is really hard. Even when you access the tables in the same order you may still get deadlocks [1]. I wrote a post on my blog that elaborates through some approaches that will help you avoid and resolve deadlock situations.

If you want to ensure two statements/transactions will never deadlock you may be able to achieve it by observing which locks each statement consumes using the sp_lock system stored procedure. To do this you have to either be very fast or use an open transaction with a holdlock hint.

Notes:

  1. Any SELECT statement that needs more than one lock at once can deadlock against an intelligently designed transaction which grabs the locks in reverse order.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. How to select just the row in the second table?
  4. Need SQL Script
  5. SQL update from one Table to another based on a ID match
  6. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  7. SQL JOIN vs IN performance?
  8. Can a foreign key be NULL and/or duplicate?
  9. How to generate a range of dates in SQL Server
  10. SQL RANK() versus ROW_NUMBER()
  11. Generate SQL Create Scripts for existing tables with Query
  12. Check if a row exists, otherwise insert
  13. Export SQL query data to Excel
  14. Insert Picture into SQL Server 2005 Image Field using only SQL
  15. Execute Stored Procedure from a Function
  16. How do I use ROW_NUMBER()?
  17. How to check if a string is a uniqueidentifier?
  18. How do I remove extended ASCII characters from a string in T-SQL?
  19. Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
  20. How to sum up time field in SQL Server
  21. How can I see all the “special” characters permissible in a varchar or char field in SQL Server? [closed]
  22. Get unique values using STRING_AGG in SQL Server
  23. The most elegant way to generate permutations in SQL server
  24. What’s the difference between a Table Scan and a Clustered Index Scan?
  25. How do I use alias in where clause? [duplicate]
  26. SELECT max(x) is returning null; how can I make it return 0?
  27. How to return default value from SQL query
  28. Group consecutive rows of same value using time spans
  29. Conditional aggregation performance
  30. SQL add filter only if a variable is not null

Leave a Comment