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

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

Thanks. I didn't know there was a "SQL forum"!

No problem! I hope you get the answers you need!

You can do it in one statement by

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

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.

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.

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).

I'm glad I asked! Group, as always, thanks again!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.