need help understanding row-level locking

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

Answers

Jeff,

You are allowed to read the row while it is locked.

Hope this helps,

Eric

-- Anonymous, August 21, 2001


Moderation questions? read the FAQ