I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables

At the same time there are read queries on the same tables using inner selectes - SELECT FROM table1... IN (SELECT from table 2...)

To clarity - there is one writer thread writing the transactions (each transaction writing to all tables) and many reader threads.

I am getting deadlock errors. 1. could the deadlock be related to the IN selection? 2. how can I make the read queries provided only commited data, but not necessarily wait for current transactions to commit (meaning it can return data previously committed) 3. could the deadlock be a result of a transaction that is simply taking too long to complete?


  • 3
    If you can capture the deadlock output and add to your question, we may be able to suggest workarounds. Alternatively, Snapshot isolation would likely resolve this particular problem. See dba.stackexchange.com/questions/6698/… Apr 19, 2012 at 12:59
  • 2
    If you plan to consider using Snapshot Isolation, be sure to also consider the caveats and it's suitability for your query logic/isolation requirements - stackoverflow.com/questions/1987152/… Apr 19, 2012 at 13:23
  • 3
    Read committed snapshot iso may help, but I had a case recently where it really should have helped but didn't. So per the other advice, Profile the activity to find out the elements and type of deadlock, then move forward from there. Apr 19, 2012 at 16:20

2 Answers 2


Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event.

How To Track Down Deadlocks in SQL Server using Profiler

Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.

  • Yes, thank you - I am obviously trying this but it reproduces rarely over load and I have yet to reproduce with the profiler (working on it) so theorizing is all I can do right now :) Apr 19, 2012 at 12:52
  • Consider looking at the execution plans for your queries. For example, look for Index/Table Scan operations. Consider whether or not these operators could be replaced with Seeks through the creation of some suitable indexes. You want to mimnimize the amount of locks and their duration that your potentially competing queries are taking. Apr 19, 2012 at 12:59

Snapshot Isolation (SI) is the correct way to go. Thanks to @JohnSansom and @EricHiggins for leading me to this information. As this may be one option to solve the deadlock (as it's non blocking access), I will also try to fix the deadlock itself, but that is for another question

From this page:

Snapshot Isolation (SI): This is new proprietary isolation level that provides non-blocking access for read operations. The transactions under SI see the snapshot of the database as of the beginning of the transaction. There is no automatic mapping of transaction isolation levels to SI so you must change your application to access data under SI isolation level.

  • Pros: Non-blocking access to transactionally consistent data. Higher consistency than repeatable read.
  • Cons: Some overhead of maintaining row versions.
  • Usage: To get non-blocking access to consistent data across multiple statements within a transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.