Why is my complete table is locked instead of rows?

Question

I have run this SQL,

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

then I run,

select 1 from temp  where id = 1 

everything fine.

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 run,

select 1 from temp  where id = 1 

But this time nothing is returned. Why is my complete table locked instead of just second row?

Answer 1

SQL Server is not locking the entire table. I can see that a single row-id is locked by the writing transaction.

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

This means that it is blocked by the X-lock on the inserted row. Basically, the reader waits for the other transaction to decide whether it wants to actually commit this row or rollback.

Session 51 has inserted id 2. Session 54 is the blocked select. No page or table locks here (apart from the intent-locks which do not matter here).

The fact that the table is a heap (no unique CI like usual) causes unexpected locking here. This issue will go away by creating a unique CI on id.

Answer 2

My guess is that your table probably isn't locked per se (or perhaps it could be since you have so few rows). I think what's happening is that SQL Server, in order to know where to insert the new row, has to lock (write lock, which prevents reading) a range of rows around the value being inserted. Since you have so few rows in the table, the appearance is that the table is locked. As you add many more rows you should not see this behavior when inserting a single row.

By the way, your table should have a primary key and/or clustered index. This will help in the future, as you add more rows. Otherwise you're going to be doing scans, which will certainly lengthen the time it takes to do updates (and perhaps inserts).