CREATE TABLE test ( ID int IDENTITY(1,1), mykey nvarchar(255) NOT NULL, exp_date datetime, PRIMARY KEY (ID)); GO CREATE INDEX not_expired_keys ON test (exp_date, mykey); INSERT INTO test (mykey, exp_date) VALUES ('A', NULL);
I start transaction 1:
-- add key B BEGIN TRANSACTION; INSERT INTO test (mykey, exp_date) VALUES ('B', NULL); ...
and then execute transaction 2 in parallel:
-- expire key A BEGIN TRANSACTION; UPDATE test SET exp_date = GETDATE() WHERE exp_date IS NULL AND mykey = 'A'; -- <-- Blocking ROLLBACK;
As it turns out, transaction 1's uncommitted INSERT blocks transaction 2's UPDATE, even though they affect disjoint sets of rows (
mykey = 'B' vs.
mykey = 'A').
- The blocking also occurs on the lowest transaction isolation level
- The blocking goes away if I put a unique index on
mykey. Unfortunately, I cannot do that, since key names can be reused once a key has expired.
(Out of curiosity:) Why do these statements block each other even on the
Is there an easy and reliable way to make them not block each other?
@get_locks = 1to see which locks are taken. I wrote a blog post pretty recently about a similar locking problem.