Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: Locking table in a SP

    I have a SP for an e-commerce site that creates an order doing the following fashion:

    begin
    -Fill out some variables
    *Critical Section
    -Create Order number
    -While loop select on order table and recreate if ordernumber already exists
    -Insert into order table
    *End Critical Section
    -Insert into order lines table, address table, etc.
    commit

    The problem is the Create Order number and While loop used to be at the top and occasionally I would get duplicate order numbers if two submitted at the same time. I rearranged it to the above and it happens less now.

    My question is if there is a way to put an XLOCK on the orders table only during the critical section lines. Also my understanding is that once the insert into orders is done that the server will hold a lock until the commit in case of rollback.? I don't want to XLOCK the whole SP if I don't have to. It is quick though.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How difficult would it be to use a guid instead of an order number for your primary key? Once you got the row added, you could monkey with the order number in a much less time-critical fashion (if it still mattered).

    -PatP

  3. #3
    Join Date
    Nov 2005
    Posts
    3
    The order table has an identity field as the PK.

    BTW: The order number is based off the date and time. I am looking at maybe changing this but I would still run into the same problem if I used the identity field as part of the number.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Why not something like

    Code:
    insert into orders (fields)
    select (new data)
    where not exists (new orderID)
    
    if @@rowcount = 0
     update old record
    It is a bit pseudo-codish, but would save you the while loop.

Posting Permissions

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