Table locking question

[jsteph]

Hi all,
I have a 'best practice' or at least a 'prevailing practice' question about locking tables.

I know this is a pretty common situation and I've just never know the right way to handle it.

Suppose I have a transaction:

Begin Tran
  SELECT values from table1;
  SELECT values from table2;
  Do logic and calc's based on values from tables;
  Update table1;
  Update table2;
Commit Tran

The time between the Select statements and the logic calculations will be minimal but either way, there is the chance that a value in one of the tables is changed externally before the update. So I want to lock the tables prior to or at the SELECTs, and hold the lock until the transaction is complete.

So can I put a with(holdlock) or something in the Select, or will that lock be released when the select is done and leave me in danger during the calculation phase? I basically want to 'reserve' the tables and prevent updates (but not reads) during the entire transaction.
Thanks,
--Jim

 

[robertfah]

You can use LOCK on the actual table:

SELECT values
FROM table1 (LOCK)

SELECT values
FROM table2 (LOCK)

That should lock the table and prevent updates.

 

[jsteph]

Robert,
In that scenario, when does the lock get released?
--Jim

 

[jsteph]

robert,
I tried (Lock) and it was unrecognized, but I tried
With(Xlock) and put it at the beginning of the transaction and it worked fine, held the lock from outside until the trans was over and then released it.

So that is the general way it's done then--one would just select the record or records one wants locked with sort of a dummy SELECT inside the transaction and then do their thing and thats it?
Thanks,
--Jim