need help understanding row-level lockinggreenspun.com : LUSENET : SQL Server Database Administration : One Thread |
Hello Eric, et al --Thanks for the great forum!
I'm new to SQL Server. I'm trying to understand how row-level locking works. I have a table with 1 row:
create table llcApplicationIDsAssigned ( appId varchar(50) not null primary key );
I've populated the table with many values. For illustration, one of these values is '0000-01070068'.
What I'd like to do is read that row with an exclusive lock. So that until my transaction ends no other connections can read that row.
In query analyzer I do a:
begin tran; select appId from llcApplicationIDsAssigned with(XLOCK) where appId = '0000-01070068';
I then run sp_lock and sure enough an X lock has been granted of type KEY in my table. I'd assume that would be looking that specific row. Apparently not.
I open a new window in query analyzer to execute:
select appId from llcApplicationIDsAssigned where appId = '0000-01070068'
and it returns the row! I would expect it to block on the other transaction.
Now, if in the first transaction I do a with(TABLOCK,XLOCK) instead of a with(XLOCK) it will block. But I'm locking the entire table instead of just the row!
Anyone have any advice on how I can just lock a row?
Thanks,
Jeff
-- Anonymous, July 28, 2001
Jeff,You are allowed to read the row while it is locked.
Hope this helps,
Eric
-- Anonymous, August 21, 2001