Search code examples
sql-servertransactionsblockhint

why the row is not blocked?

I am using this transaction in SQL Server Managemnet studio 2012

begin transaction
Select *
from tabl1 with(xlock, rowlock)
where ID = 1153;

select * from Table2;
rollback

I put a breakpoint in the second query. The first query would block the row of the Pieza which ID is 1153 while the transaction is not commit or rollback, so when the code stop in the breakpint, in another instance of SQL Server Management studio I do:

select * from Table1

This query I think that it would be wating until the transaction of the first SQL Server management studio will finish, but the las query can finish without problem.

However if I do that in a T-SQL in a transaction with EF the row is blocked.

I have tried too:

begin transaction Select * from tabl1 with(xlock, rowlock) where ID = 1153; go select * from Table2; rollback

But this does not solve the problem.

How can I try the hints of SQL Server in management studio?

Thanks.

EDIT:

This transaction blocks the row:

begin transaction Select * from tabl1 with(xlock, rowlock);

select * from Table2;
rollback

So when I set an condition like the ID the row is not blocked.

Solution

  • From: http://msdn.microsoft.com/en-us/library/ms187373.aspx

    Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on      
index keys rather than the actual data rows. For example, if a table has a nonclustered index,     
and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on    
the index key in the covering index rather than on the data row in the base table.

    So it could be that an index is satisfying the first query, but the second (SELECT *) can only be satisfied by the clusterd index.