0

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.

Álvaro García
  • 18,114
  • 30
  • 102
  • 193

1 Answers1

1

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.

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25
  • So there is any way to block a row in any case, when I use an index and when I don't use an index? Thanks. – Álvaro García Jun 20 '14 at 17:47
  • 1
    I think it depends on what operation you are doing. Selects are compatible locks and are not going to block each other. On the other hand if you were updating the row, the select would have to wait for that operation to finish. There is a nice table in here which helps to explain that: http://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx – Mark Wojciechowicz Jun 20 '14 at 17:53
  • I need to block in select because it's the way that I have to ensure that any other row related with this row is updating by another user and the the information is not coherent. I mean taht I have a group of rows that I need to update all or nothing, and blocking the reference row is the way that I find to do it. Because the other way it's doing a fake updating to block the row. But I don't like this solution because do another roundtrip to the database. – Álvaro García Jun 20 '14 at 17:58
  • Using foreign keys will insure the integrity between the two tables. This will take the appropriate locks on both. – Mark Wojciechowicz Jun 20 '14 at 18:04