Results 1 to 3 of 3

Thread: Processing a non-locked row update from within a TRANSACTION

  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Processing a non-locked row update from within a TRANSACTION

    I have a very large stored procedure (called pr_insert_order) that saves an order into our database. Since it has a lot of moving parts, it is wrapped in a BEGIN TRANSACTION and COMMIT TRANSACTION. That way if anything blows up in the middle of the procedure, the whole transaction is automatically rolled back.

    My problem is that we use counters in our system for things like "Next sequence number", "Next order number", "Next line item number", etc. So we have a stored procedure (called NextVal) that we call and it passes us the next available number in the sequence. In order to accomplish this, it throws a record lock on the row in a tiny utility table (SysData) that holds the next available sequence number. It grabs that number to return to the calling proc, then updates the row to the next number so the next time it is called it will return the next higher number.

    Since the NextVal proc is called from within pr_insert_order, and pr_insert_order starts with BEGIN TRANSACTION and ends with COMMIT TRANSACTION, the update that takes place within NextVal gets tied into the overall transaction of pr_insert_order. As a result, two people running pr_insert_order at the same time can get stacked behind one another, due to the open lock on SysData created by the first call to NextVal by the first user in the mix.

    I need a way to grab a next value without throwing locks on a table that will cause successive runs of a proc to get stalled behind one another. I considered doing an external procedure call to another instance of SQL server running on the same physical server, but that is way too much overhead for just one litttle table - i would have to have every customer re-install SQL to get the second instance - a nightmare.

    I do not want to use an identity field in the tables to get a sequence number. This would present many additional challenges that are worse than my current problem.

    I appreciate any help anyone can provide me. Below is a simplified code snippet from NextVal:

    CREATE PROCEDURE NextVal
    AS
    DECLARE @x INT
    UPDATE SysData set @x = val + 1, val = val + 1
    RETURN @x

    Thank You,

    John

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Check out this site, it has a neat trick to use identity to generate sequence. It makes use of the fact that identity value is not rolled back after rollback statement.

    http://www.mssqltips.com/tip.asp?tip=1293

  3. #3
    Join Date
    Dec 2008
    Posts
    3

    Thank You!

    This solution looks great. I will try it out and let you know how it works.

    Thanks skhanal!

Tags for this Thread

Posting Permissions

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