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