How do I query only for committed data while avoid a deadlock

Question

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?

Thanks!

Answer 1

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.

Answer 2

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.