Hi Eric, I have the following SQL: BEGIN TRANSACTION if exists (select Status from CSSBatchLock TABLOCKX where Status='unlocked') begin select Status from CSSBatchLock update CSSBatchLock set Status='locked' where Status='unlocked' end else begin select Status from CSSBatchLock end

and what I would like to do is place an exclusive lock on the referenced table so that no one can read or update the table while the SQL is executing. I thought that the TABLOCKX command would allow me to do this but it doesn't seem to lock the table because I can still do a select. Could you tell me what I am doing wrong?

-- Anonymous, October 26, 1999

Answers

Kimberley,

First of all, I don't see the commit transaction. Second, the optimizer hints (that is, the TABLOCKX) needs to be in parentheses. Third, if you want the lock to be held through the end of the transaction, you need to add HOLDLOCK.

Therefore, you probably want to use (HOLDLOCK TABLOCKX)rather than simply TABLOCKX.

Good Luck,

Eric

-- Anonymous, October 26, 1999