Re: SELECT locking: EXACTLY HOW?

We need to ensure atomicity of v in the block (i.e. another process
executing the same block will have to wait until the earlier process
finishes the transaction.) (in the real codes, of course, the table is
more complex and I just condition on the PK.)

This is supposed to work, but it does not unless I use "WITH
(HOLDLOCK, XLOCK)".

Shared locks are compatable with one another so it is perfectly fine for 2 concurrent sessions to hold shared locks on the same resource. That is why you need to specify an incompatible lock type like UPDLOCK or XLOCK.

However, this seems to lock more than one row.

There is no primary key or other indexes in your example script so a table level lock is needed. I would expect only a single shared key lock if you have a primary key on the table.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Hacking Bear" <hackingbear@xxxxxxxxx> wrote in message news:a4b80810-c881-4211-944a-c757807901cf@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

Do you know enough about SQL Server locking? I read much of it on the
web. But i still have no idea how to do locking with select statement

Say, i have a table

create table XT (v int)

insert into XT values (100)
insert into XT values (200)
insert into XT values (300)


then I have something like these in a transaction (isolation level =
READ COMMITTED)

-- n is input
BEGIN TRANSACTION
SELECT v FROM XT WITH (HOLDLOCK) WHERE v = n
-- do something with v
COMMIT

We need to ensure atomicity of v in the block (i.e. another process
executing the same block will have to wait until the earlier process
finishes the transaction.) (in the real codes, of course, the table is
more complex and I just condition on the PK.)

This is supposed to work, but it does not unless I use "WITH
(HOLDLOCK, XLOCK)". However, this seems to lock more than one row.
Then I tried "WITH (ROWLOCK)" or "WITH (HOLDLOCK, ROWLOCK)", it
doesn't seem to work at all. Also "WITH (XLOCK)" does not work either.
Weird.

The questions are
1) Why doesn't HOLDLOCK which is said to hold the "shared lock" used
by SELECT in all documents I read, including MSDN.

2) How to lock just a row?

Most web pages i read appear to just re-phrase what's in MSDN and SQL
Server documents without adding much valuable information.


.

Relevant Pages

  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Can extra processing threads help in this case?
    ... SQLite for just the financial aspect of the transaction. ... in DATAFILE sharing. ... CReader LOCK() ...
    (microsoft.public.vc.mfc)
  • Re: HOLDLOCK?
    ... You have to be in a transaction for HOLDLOCK to hold anything, ... explicitly create a transaction with BEGIN TRAN. ... > between the time the procedure acquires a lock on tableOne and it ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... Andrew J. Kelly SQL MVP ... > ROLLBACK TRANSACTION; ... > find in the clients sp that could be causing the dead lock is the join on ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)