HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whilecommitteddeadlockqueryavoidforhowdataonly

Problem

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!

Solution

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.

Context

StackExchange Database Administrators Q#16757, answer score: 8

Revisions (0)

No revisions yet.