Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: 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 TRANSACTION

  2. #2
    Join Date
    Feb 2004
    Posts
    492

    Re: Help: Table Lock Confirmation

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

  3. #3
    Join Date
    Mar 2004
    Posts
    5

    Re: Help: Table Lock Confirmation

    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code that I used to use was:
    PHP Code:
    BEGIN TRANSACTION

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

    IF <> @@error GOTO bail

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

    IF <> @@error GOTO bail

    COMMIT TRANSACTION
    BEGIN TRANSACTION

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

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    Originally posted by Pat Phelan
    The code that I used to use was:
    PHP Code:
    BEGIN TRANSACTION

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

    IF <> @@error GOTO bail

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

    IF <> @@error GOTO bail

    COMMIT TRANSACTION
    BEGIN TRANSACTION

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

    -PatP
    Thank you very much Pat!

  6. #6
    Join Date
    Mar 2004
    Posts
    5
    Originally posted by Pat Phelan
    The code that I used to use was:
    PHP Code:
    BEGIN TRANSACTION

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

    IF <> @@error GOTO bail

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

    IF <> @@error GOTO bail

    COMMIT TRANSACTION
    BEGIN TRANSACTION

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

    -PatP
    Thank you very much Pat!

  7. #7
    Join Date
    Feb 2004
    Posts
    492

    Re: Help: Table Lock Confirmation

    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?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    The code that I used to use was:
    PHP Code:
    BEGIN TRANSACTION

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

    IF <> @@error GOTO bail

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

    IF <> @@error GOTO bail

    COMMIT TRANSACTION
    BEGIN TRANSACTION

       bail
    :
    ROLLBACK TRANSACTION 
    This 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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.p...5&pagenumber=1

    What's the difference...you're using Goto's anyway....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •