Member Avatar for Papa_Don

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 because: wrong forum
  • 4 Contributors
  • 8 Replies
  • 1K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Papa_Don

Dani AI

Generated

Short summary tied to the thread: the table-lock hint belongs in the T‑SQL inside your stored procedure (not in the VB client) and its effect is governed by SQL Server locking rules and the transaction scope — this is why suggested the concise, single-statement approach and why pointed out the read-blocking risk. See the official table-hints reference for details. (Table hints) Table hints (Transact-SQL)

Practical recommendations you can apply now:

  • If the goal is just to generate unique, ever-increasing order numbers, avoid manual table locking. Use an IDENTITY column or a SEQUENCE (SQL Server 2012+) and consume it with NEXT VALUE FOR; that gives atomic number generation without full-table contention. (CREATE SEQUENCE) CREATE SEQUENCE (Transact-SQL) (NEXT VALUE FOR) NEXT VALUE FOR (Transact-SQL)
  • If you must serialize multi-statement work (read, compute, then insert), prefer an application-level lock (sp_getapplock) instead of forcing an exclusive table lock; it acts like a mutex for a named resource and is less invasive. See the docs for mode, owner, and timeout options. sp_getapplock (Transact-SQL)

Example pattern (safe, minimal):

BEGIN TRAN;
DECLARE @rc INT;
EXEC @rc = sp_getapplock @Resource='OrderNoGen', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout=10000;
IF @rc < 0 BEGIN ROLLBACK TRAN; RETURN; END
-- acquire sequence value or perform the insert here (use NEXT VALUE FOR dbo.YourSeq)
COMMIT;

Keep transactions as short as possible; long-held locks cause blocking and deadlocks — monitor and test under concurrency. For guidance on blocking behavior and troubleshooting, see Microsoft’s blocking article. Understand and resolve blocking problems

Thanks to and for the helpful starting points; the fastest, most scalable fixes are usually SEQUENCE/IDENTITY or sp_getapplock rather than persistent table-level exclusive locks.

Recommended Answers

All 8 Replies

Member Avatar for Begginnerdev

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

Edited by Begginnerdev
Member Avatar for Papa_Don

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

Member Avatar for Begginnerdev

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

Member Avatar for Reverend Jim

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
Member Avatar for Papa_Don

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.

Member Avatar for Reverend Jim

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.

Member Avatar for pritaeas

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
Member Avatar for Papa_Don

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.