I was looking into a deadlock caused by two concurrent UPDATE statements:
UPDATE [table] SET [column] = 0 WHERE [unindexed_column] = @id
My understanding is that because the column the
WHERE is predicated on is unindexed, a full table scan is performed. For each row, an update lock is acquired. If it matches the
WHERE clause, it then upgrades to an exclusive lock and holds it until the statement completes.
The deadlock occurs when session A has an exclusive lock on row 2 and is trying to acquire an update lock on row 1, while session B has an exclusive lock on row 1 and is trying to acquire an update lock on row 2.
The reason for the deadlock makes sense, but I don't understand exactly how the table scan is performed that makes this scenario possible. If the two queries perform the scan in the same order, it seems like the worst case scenario is that one of the queries is blocked on acquiring an update lock until the other query completes and releases it's locks.
How is a table scan performed? Is the order the table rows are scanned in inconsistent? If the update statement fails to acquire an update lock, does it go to the next row and try the previous row again later? What, exactly, makes this deadlock possible?