2

Firstly 1 thread selecting a huge amount of data from a table.

Then, before the SELECT query is not yet finished, another thread deletes data from that table.

It causes SELECT query to failed with error errorCode=1205 sqlState=40001 and message that deadlock happened.

I thought that second transaction must wait until first is finished, or first transaction must return data without the changes that were made by the second transaction. How can I avoid this?

I can't change the order of transactions or control them in some way (there is many users of app, and they can do whatever they want in any order at any time). Why does it happen?

There is how queries looks like (i obfuscated them a little):

SELECT distinct P.pID, (LastNAME+' '+FirstName+' '+SurName) as NAME, 'person' as Type, CA.OrgName, PC.personemail as Email
FROM Persons P
INNER JOIN WORK PW ON (P.mainwork = PW.pwId)
INNER JOIN CONTACT PC ON (PC.pID = P.pID)
LEFT OUTER JOIN ORG CA ON (CA.orgId = PW.orgId)
WHERE (P.Status = ?)

and

DELETE FROM persons WHERE id = 1234
Improve this question
2
  • Can users begin and end database transactions as they please? It's the order inside a transaction that matters.
    – Tom V
    Mar 16, 2016 at 17:05
  • I'm assuming that the app runs the queries and the user's don't have any control over the queries themselves, and also that you have no control over the app's queries (correct me if I'm wrong). It's hard to tell if there's anything wrong with the query itself, it seems pretty straightforward. It will of course depend on what is in the tables and your setup. If you can't optimise queries, before changing isolation levels check other things about your setup and make sure everything is in order and optimised, ideally in a test environment before production.
    – Ian_H
    Mar 17, 2016 at 9:17

1 Answer 1

Reset to default
6

I thought that second transaction must wait until first is finished

Basically, this is not true. Your (or anyone's) database wouldn't be very efficient if only one person could do something with it at a time.

Why does it happen?

Simply:

A SELECT query is taking shared locks on any rows it's looking at, so although it's not going to conflict with another SELECT query, it definitely will conflict with exclusive locks from a DELETE. As the SELECT query is not updating anything, it's usually going to be the one to be the deadlock victim, as it'll likely be the least expensive to rollback.

Why it happens specifically in the case you're talking about is not possible to say without further information. I'll add some links to deadlock detection at the end but I suspect this isn't the main purpose of the question so...

What to do?

It's difficult to know what the best approach is going to be for you (without a lot more information). It is possible that you should have a look at your isolation levels.

You could use READ_COMMITTED_SNAPSHOT, but this could result in further overheads, or use READ_UNCOMMITED/use a NOLOCK hint - I'm not a fan of those options usually as the data returned might not be actually what you want, but they may be suitable in your case. It's hard to say exactly what would be best, as it'll depend on a lot of different variables about your system and it's use.

Best thing you can do in that regard though is read up about isolation levels, these are some similar posts/sources that will start you off and go into things in more detail.

MSDN - How can a select statement result in being chosen as deadlock victim?, SQL Server central , MSDN - Understanding Isolation Levels

But...

Don't rush to make a change just so you can get your queries to finish. A more appropriate avenue to explore may be your queries themselves.

Why is the SELECT statement taking so long to run? Why are things being deleted from the table so frequently? Maybe some simple query tuning will get them all to complete quicker and reduce the chance of a deadlock. Perhaps you need to do some general performance tuning on your database server (I won't go into detail on this here though as it's getting way off topic).

You've got to make sure your queries are fit for business purposes, rather than queries just 'completing at all costs', databases are a business tool first a foremost and need to work around that (however much we might not like that)!

Just going ahead and changing the isolation level, or even just using NOLOCK, can have big impacts on your database and also what exact results queries return - which could have business impacts.

Also...

To aid in deadlock detection, have a look at this post on StackOverflow and check out the Deadlock Information Tools on the MSDN site which will explain how to use trace flags to provide more information about your deadlocks.

Finally...

More specific advice can be offered if you put the SQL Server version in your post, and some more details about what exactly the SELECT query is (to see if there's anything that may be causing it to take so long and be open for being locked).

Improve this answer
2
  • @lan_H Ty for good answer. So, the default isolation will always behave like this? Fall with error in case of lock conflict? About query optimization - i think there is no way. There is grid, that displays data from this table (and selecting nearly all rows from it) and background task, witch deleting incorrect data. So when we accessing grid during the background task, error happens. I added queries to the topic.
    – saroff
    Mar 16, 2016 at 19:06
  • @saroff No problem. Yes, the default isolation is READ COMMITED and it'll behave as I described (the SELECT query losing out in most cases - I'd hesitate to commit to saying all cases though). Have a look at msdn.microsoft.com/en-gb/library/ms173763(v=sql.110).aspx.
    – Ian_H
    Mar 17, 2016 at 9:12

Your Answer

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

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