Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
2  
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/… –  Mark Storey-Smith Apr 19 '12 at 12:59
1  
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/… –  John Sansom Apr 19 '12 at 13:23
2  
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. –  Eric Higgins Apr 19 '12 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.

share|improve this answer
    
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 :) –  Amit Ben Shahar Apr 19 '12 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. –  John Sansom Apr 19 '12 at 12:59
up vote 0 down vote accepted

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. o Pros: Non-blocking access to transactionally consistent data. Higher consistency than repeatable read. o Cons: Some overhead of maintaining row versions. o Usage: To get non-blocking access to consistent data across multiple statements within a transaction.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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