Help: Table Lock Confirmation

I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!

This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.

BEGIN TRANSACTION

UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @.forum_id

SELECT @.new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @.forum_id

COMMIT TRANSACTIONI would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..|||Originally posted by Kaiowas
I would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..

Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.|||The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by stevenpath
Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.

You're right! Sorry I missed that, but why not stick with it?|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Pat why the extra BEGIN TRAN?

Is that a type o?

Geez what a way to hit 2000|||Originally posted by Brett Kaiser
Pat why the extra BEGIN TRAN? It is a nifty little trick that I dreamed up one night in a haze...

When using nested stored procedures, things got really, really complicated if the transaction level got puckered up, and things just went to heck in a handcart. I had to find some way that I could rollback without blowing the whole tamale out of the water. Necessity being a mother (as you so recently pointed out), I came up with a deviant solution.

The code is two transactions when life is good, with an empty one being rolled back, which has no impact on the database. When life is hard, it is only one transaction, which is also rolled back so it has no impact on the transaction count either...

The net result is that it is an odd bit of code, but it works nicely in all of the peculiar ways that we need code to function. Someday I'll have to post a little diatribe about the bad old days, when Sybase wanted considerably more dollars for each replicated database (per year) than they wanted for the license for the database! That drove us to some peculiar work arounds, this being one of them.

-PatP|||OK, I see it now...but why do it that way?

Why not handle it like the code in this thread?

http://www.dbforums.com/showthread.php?threadid=988019&perpage=15&pagenumber=1

What's the difference...you're using Goto's anyway...|||As I said in the previous post, this was a side effect of the cost of using early (like 1994) versions of SQL replication. Our work around required each procedure have one entry point, and one exit point from a code execution perspective so we could effectively "bottle" the procedure with calls to other procedures that our work around required.

It wasn't necessarily pretty, but it saved us more than a million dollars per year in licensing fees.

-PatP