0

Consider a simple table with an Id and Item:

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?

1
  • Your second query doesn't have a where clause to filter to just id = 1, though that may not make a difference in this case. May 31, 2021 at 16:57

2 Answers 2

4

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 READ_COMMITTED_SNAPSHOT.

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.

1
  • 1
    On the final sentence, that would only work if it was a row-lock. If it's a page-lock (which it very well may be) and the SELECT requests the same page, you still get blocking May 31, 2021 at 21:16
2

Turn on READ_COMMITTED_SNAPSHOT if you want readers and writers to not block each other.

See: Row Versioning-based Isolation Levels in the SQL Server Database Engine

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.