Transaction Isolation on select, insert, delete

Question

What could possibly go wrong with the following transaction if executed by concurrent users in the default isolation level of READ COMMITTED?

BEGIN TRANSACTION

SELECT * FROM t WHERE pid = 10 and r between 40 and 60
-- ... this returns tid = 1, 3, 5
-- ... process returned data ...
DELETE FROM t WHERE tid in (1, 3, 5)
INSERT INTO t (tid, pid, r) VALUES (77, 10, 35)
INSERT INTO t (tid, pid, r) VALUES (78, 10, 37)
INSERT INTO t (tid, pid, r) VALUES (79, 10, 39)

COMMIT

Answer 1

You could having serious performance problems from dead locks

The SELECT will obtain a shared lock on a page and then the DELETE would attempt to upgrade those locks to exclusive locks.

If another user was executing the same query, it might obtain shared locks on the same page at the same time another user does. Then when one tries to upgrade to an exclusive lock, it will wait for all other shared locks to be released. The other will also be waiting for all shared locks to be released. Both will have a shared lock and waiting for the other to release that shared lock so itself can obtain an exclusive lock. Other queries will pile up trying to do the same, and soon the deadlocks will begin to be detected and the queries will begin to get cancelled and rolled back. Depending on the frequency of the queries the dead lock detection of the DB engine may not be killing off queries as fast as new ones are coming in, meaning none of the queries will succeed.

You would need to add something like a hint in the select to request that an exclusive lock be obtained from the get-go. Or you could move the select outside of the transaction and use concurrency conflict detection in your other statement's where criteria.