SET [AnswerCount] = @p1, [LastActivityDate] = @p2, [LastActivityUserId] = @p3
WHERE [Id] = @p0
WHERE [ParentId] = @p0
And, the problem is that you are seeing a deadlock…
First, we must consider WHY this is the case… Note that a SINGLE lock can’t deadlock, unless the resource is never returned. Neither of these SQL statements qualifies as an infinite loop. So, we must presume at least two resources. Note that, equally, we cannot assume NO locks, as that cannot deadlock either.
A possible answer to this is that there are separate READ and WRITE locks (there must be at least two locks). Any number of READ locks can be held by readers of a table. A WRITE lock is then exclusive (The WRITE lock can be obtained if there are no READERs). This would make sense; it is a normal design pattern.
This gives us the necessary two resources. Now, the READ resource should be acquired by statement 2. The WRITE resource should be acquired by statement 1. Aquiring WRITE of course implies ownership of READ as well. Note that the second statement is an UPDATE, which will of course use READ – but it isn’t needed because the WRITE resource implies the READ resource. Indeed, it would deadlock if this were not the case.
If the two resources (READ and WRITE) were not acquired atomically (relative to each other),then there may be a subtle race condition within the SQL service itself. Of course, this implies that the resources are implemented as two separate resources.
Another issue could be that the SQL optimizer could be mis-compiling the request and issuing a sequence like aquire(WRITE) … do something … aquire(READ) … which would also lead to the deadlock.
If the optimizer ASSUMED that no updates where going on, it may begin with aquisition of READ, and begin the transaction. Later, it may determine a result set, and attempt to aquire WRITE – which of course would fail (this would be a nasty bug indeed). Of course, it may assume that IT owns READ, and thus attempting to aquire WRITE is ok (and this naive optimization would work, AS LONG AS THERE ARE NOT CONCURRENT READERS).
Actually, there may be other causes to this. But, (and this is important) - if the problem is as a result of select/update, it is probably a defect within the database itself.
Indeed, there is NO direct solution to this deadlock, under the circumstances you have outlined. There is, however, a work-around.
Ensure that the database is NEVER UPDATEd if concurrent reads could be going on. Writes are fine, Deletes are fine, but ANY UPDATE would be suspect with this database library.
Alternatively (and my STRONG recommendation is to immediately get rid of the database), use explicit locking.