All we need is an easy explanation of the problem, so here it is.
Consider a simple table with an
Column date type ------------------------ Id int primary key Item varchar(50)
Insert three items:
INSERT INTO [Items] ([Item]) VALUES ('first item'); // Id 1 INSERT INTO [Items] ([Item]) VALUES ('seconditem'); // Id 2 INSERT INTO [Items] ([Item]) VALUES ('third item'); // Id 3
Now I update a single row in a transaction (which I explicitely don’t commit for testing purposes):
// query 1 BEGIN TRANSACTION UPDATE Items SET Item = 'updated' WHERE Id = 2;
Now in parallel I run a second query:
// query 2 SELECT Id, Item From [Items];
query 2 hangs indefinitely. I don’t understand why. Shouldn’t this query be independent of
query 1, just reading committed data and ignoring the ongoing update? Any way to optimize/configure this? Or do I have to live with SQL Server blocking whole tables during updates?
How to solve :
As David Browne mentions, this is the default behavior of the default isolation level in SQL Server. You can look into using alternative isolation levels such as
Also to clarify,
UPDATE operations don’t lock the entire table necessarily, in the default isolation level. In your example, likely only a row level lock is occuring on the row with
Id = 2 but because your second query requests that row (since it’s selecting the entire table) it sits waiting on the lock for just the row with
Id = 2 before it can return any results. If you modified your test, so your second query said
SELECT Id, Item From [Items] WHERE Id = 1 then you’ll likely get results instantly.
Turn on READ_COMMITTED_SNAPSHOT if you want readers and writers to not block each other.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂