Row Locking ....

[ryan]

I know I can perform a row lock during a statement but here is a more indepth question which is I need to be able to:

1: Execute a SQL statement, say a select using row lock and
then return those results and KEEP THE ROW LOCKED.
2: Do some radom task.
3: UNLOCK that row that I've locked.

My question is can you row lock something and keep it row locked, like getting some row lock id from sql and then until you unlock it explicitly it remains locked?

Thanks for the help!
Ryan

 

[tlbroadbent]

I don't know of any way to obtain and release a lock by row lock ID. I've not tried the following but believe it should work.

If a lock is made and held inside a transaction, the lock will be held until the end of the transaction. You could do a begin transaction before selecting the record, do the select, do the random stuff and then commit the transaction. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ryan]

tlbroadbent ... yes this does work but you have to be within the same session. I'm doing the transaction in .NET using a Reader so I need to find out that from the time you open the db connection to the time you close does it keep the same session regardless of how many commands you run. I'll find out shortly, I'm using SQL Trace to find out.

Ryan

 

[fluteplr]

As long as you do not close the connection the session should be the same.

 

[joemcs]

You can do this using the following method:

First you need to begin a transaction then select the record with the UPDLOCK option. as soon as you commit or rollback the record is released

BEGIN TRANSACTION

SELECT SOMEFIELD FROM SOMETABLE WITH (UPDLOCK)

do some other processing

COMMIT
or ROLLBACK