Why is my full table locked instead of rows?

Question

I ran this SQL,

create table temp ( id int, name varchar(10) ) insert into temp values(1,'a'); 

then i ran

 select 1 from temp where id = 1 

all perfectly.

Then I run an uncommitted insert,

 SET NOCOUNT ON; DECLARE @TranCount INT; SET @TranCount = @@TRANCOUNT; IF @TranCount = 0 BEGIN TRANSACTION ELSE SAVE TRANSACTION Insertorupdatedevicecatalog; insert into temp values(2,'b') 

then i ran

 select 1 from temp where id = 1 

But this time nothing returns. Why is my full table locked instead of the second row?

Answer 1

SQL Server does not lock the entire table. I see that one identifier row is locked by a write transaction.

The reader must scan the entire table because there are no indexes.

This means that it is locked by X-lock on the inserted row. Basically, the reader waits until another transaction decides whether he actually wants to commit this line or roll back.

In session 51, ID 2 is inserted. Session 54 is blocked. There are no page or table locks (except for locks that are not important here).

The fact that the table is a heap (there is no unique CI, as usual) causes an unexpected lock here. This problem will disappear by creating a unique CI.

Answer 2

I assume that your table is probably not locked by itself (or maybe maybe because you have so few rows). I think what is happening is that SQL Server, in order to know where to insert a new row, must block (block a record that prevents reading) the range of rows around the inserted value. Since there are so few rows in the table, the appearance is that the table is locked. When you add many more rows, you should not see this behavior when inserting one row.

By the way, your table should have a primary key and / or a clustered index. This will help in the future as you add more lines. Otherwise, you will perform a scan, which will certainly extend the time it takes to update (and possibly insert).