Why is my complete table is locked instead of rows?
sql, sql-server, sql-server-2008-r2
Solution
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.
Problem
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?