0

Hi Group!

I've created a stored SQL procedure that will need to be modified to lock the table, run the commands and then unlock the table. I need to do this because I want any other users/programs that will need access to "wait in line" until the procedure is finished. It's short a very short procedure. So in essence, I want to

'Lock the table
'run the commands here
SELECT @today = GETDATE(), @ordno = OrderNo FROM ORDRNUMBERREC
SELECT MAX(OrderNumberID), @ordno FROM ORDRNUMBERREC
SELECT @ordno1 = @ordno + 1
INSERT INTO ORDRNUMBERREC
(OrderNo, UserID, CreateDate)
VALUES(@ordno1, 'system', @today)
'now Unlock the table

I've been reading on this and find the command, TABLOCKX, and this may be what I'm looking for.

My questions are:

1) where is this command inserted (within the SQL procedure or within the VB code)?
2) is there a "unlock" version of this or does the table unlock when the routine is exited?

In advance, thanks for the help.

Don

Edited by Reverend Jim: wrong forum

4
Contributors
8
Replies
29
Views
4 Years
Discussion Span
Last Post by doncwilson_1
0

I will flag your post to be moved the the SQL forum where you may receive more help.

Edited by Begginnerdev

0

You can do it in one statement by

INSERT INTO ORDERNUMBERREC
    (OrderNo,UserId,CreateDate)
SELECT MAX(OrderNo)+1,'system',GETDATE()
    FROM ORDERNUMBERREC
WITH (TABLOCK)

Edited by Reverend Jim

0

Gee.... that's much shorter than I was thinking... I certainly like that better.

To make sure I'm correct, TABLOCK will ensure any other user attempts to access the stored procedure will have to get in line, and won't have access until the first user is finished. Correct?

In this you're using TABLOCK. Is there a reason you're not using TABLOCKX?

Thanks, Jim, for the help.

0

You're right. TABLOCKX would be preferred.

TABLOCK forces a full table lock rather than whatever the lock manager would have used but can create contention problems if other users want to modify data in the table.

TABLOCKX creates an exclusive lock that locks all other users out of the table for the duration of the statement.

I have to wonder why anyone would use TABLOCK over TABLOCKX.

0

I have to wonder why anyone would use TABLOCK over TABLOCKX.

Technically, the lock will only lock the rows in question (if possible), so other data can still be read and modified.

Lockx will block all other queries (select queries too).

Edited by pritaeas

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.