How do I lock certain SQL rows while running a process on them?

Question

My work has a financial application, written in VB.NET with SQL, that several users can be working on at the same time. At some point, one user might decide to Post the batch of entries that they (and possibly other people) are currently working on. Obviously, I no longer want any other users to add, edit, or delete entries in that batch after the Post process has been initiated. I have already seen that I can lock all data by opening the SQL transaction the moment the Post process starts, but the process can be fairly lengthy and I would prefer not to have the Transaction open for the several minutes it might take to complete the function. Is there a way to lock just the records that I know need to be operated on from VB code?

Answer 1

add

with (rowlock)

to your SQL query

SQL Server Performance article

EDIT: ok, I misunderstood the question. What you want is transaction isolation. +1 to Joel :)

Answer 2

If you are using Oracle you would Select for update on the rows you are locking.

here is an example

SELECT address1 , city, country
FROM location
FOR UPDATE;

Answer 3

Jimmy, are you referring to the Query that retrieves the information then? How do I release the lock when I'm ready to write out the results from the Post process?

Answer 4

You probably want to set an isolation level for the entire transaction rather than using with (rowlock) on specific tables.

Look at this page:
http://msdn.microsoft.com/en-us/library/ms173763.aspx

Specifically, search within it for 'row lock', and I think you'll find that READ COMMITTED or REPEATABLE READ are what you want. READ COMMITTED is the SQL Server default. If READ COMMITTED doesn't seem strong enough to you, then go for REPEATABLE READ.

Update: After reading one of your follow up posts, you definitely want repeatable read. That will hold the lock until you either commit or rollback the transaction.

Answer 5

Ok, but that appears to only lock the rows for the duration of the read transaction. What I'm looking for is a "lock until I say unlock" type situation where I: 1) Read a bunch of data into memory 2) Perform a bunch of in-memory processing on those records 3) Write the modified records back into the table

I need the record lock to stay in place from the beginning of step #1 to the end of step #3.

Or am I missing something?

Answer 6

Any more info on this from anyone?

Answer 7

wrap it in a tran use an holdlock + updlock in the select

example

begin tran
select * from
SomeTable (holdlock,updlock)
where ....


processing here

commit

Answer 8

Ok, the common thread that I'm getting here is that I'll have to open a Transaction and keep it open for the whole time I'm doing the Post processing in order for the records to remain locked?