OK, but why doesn’t SQL Server re-issue the transaction on my behalf, then? Automatic retries of some kind?
Because the transaction happens on the code, not on the database. For example:
x := select availability from inventory where item_id = XYZZY
y := select sum(quantity) from sale where item_id = XYZZY and sale_id = XYZZY
x := x-y
set availability := x
where item_id = Y
Ok, so you got through the first read and then deadlocked in the second. How can the database retry it for you? You already got the first read inside x. The database might recalculate that value, but it can’t tell your code to update x.
That’s why the code has to retry the transaction.
That’s also the why the read causes a lock. Suppose you read the availability, and then someone else tries to update it. If you hadn’t locked it in first place, how would you know, at the time you issue the update, whether anyone changed availability or not?
It becomes even more important if the database is not properly normalized, but as my example got big and confusing, I decided to leave it at that.