snippetMinor
How do I query only for committed data while avoid a deadlock
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.
Thanks!
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.
- could the deadlock be related to the IN selection?
- 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)
- 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.
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.