Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    125

    Unanswered: Stored Proc and Deadlock Error handling

    I have a Stored Proc that is called by a SQL Job in SQL Server 2000. This stored proc deadlocks once every couple of days. I'm looking into using the @@error and try to doing a waitfor .5 sec and try the transaction again. While looking around google I've come across a few articles stating that a deadlock inside a Stored Proc will stop all execution of the stored proc so I will not be able doing any error handling. Is this true? Does anyone have any experience that could help me out?

    I know the best solution would be to resolve why I get a deadlock. We are currently looking into that but until we can resolve those issues I would like to get some type of error handling in place if possible.

    Thank you,
    DMW

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Deadlocks occur because of DML

    You need to show us what you're doing....

    Some process is locking or it could be the sproc blocking itself

    How long are your transactions?
    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.

  3. #3
    Join Date
    May 2004
    Posts
    125
    The basic flow is:

    Create Procedure dbo.xxx

    parm1,
    parm2,
    ...

    as


    Begin Transaction Customer
    Insert statement
    commit transaction Customer

    Begin Transaction Order
    Insert statement
    commit transaction Order

    Begin Transaction OrderItem1
    Insert statement
    commit transaction OrderItem1

    Begin Transaction OrderItem2
    Insert statement
    commit transaction OrderItem2



    The transaction that is causes the most Fun is:

    Begin Transaction InsertOrder
    set @OrderID = (select max(_ID) from Order) + 1

    insert into Order ( _ID -- insert the new Order.
    , _ID_CUST
    , _ID_SRC
    , _ID_USER_SoldBy
    , _ID_PT
    , _ID_STYP
    , _ID_USER_EnteredBy
    , _ID_ADDR_Ship
    , _ID_CC
    , _CCNumber
    , _CCExpMonth
    , _CCExpYear
    , _CC_CVV2
    , _CheckBankName
    , _CheckRoutingNum
    , _CheckAccountNum
    , _CheckNumber
    , _MoneyOrderNumber
    , _SoldDate
    , _Web_Order_ID
    , _EnteredDate
    , _ID_DEPT
    )
    values ( @OrderID
    , @CustomerID
    , @SourceID
    , @created_by
    , @_ID_PT
    , @ShippingID
    , @created_by
    , @AddressID
    , @CCType
    , @CCNumber
    , @CCExpMonth
    , @CCExpYear
    , @CC_CVV2
    , @CBP_BankName
    , @CBP_RTNumber
    , @CBP_AcctNumber
    , @CBP_CheckNumber
    , ''
    , @created_date
    , @WebOrderID
    , getdate()
    , @dept
    )
    Commit Transaction InsertOrder

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Scary....

    You do know that if anyone fails...the rest may still commit

    potentially cause a data nightmare...

    anyway...

    Also, you've never seen a dup key problem?

    what if the sproc is fired at the same time, and grabs the same "next" id?

    Why aren't (and I can't believe I'm saying this) you using IDENTITY?

    Maybe try changeing to this

    Code:
    Begin Transaction InsertOrder
    
    INSERT INTO Order ( _ID -- insert the new Order. 
    , _ID_CUST
    , _ID_SRC
    , _ID_USER_SoldBy
    , _ID_PT
    , _ID_STYP
    , _ID_USER_EnteredBy
    , _ID_ADDR_Ship
    , _ID_CC
    , _CCNumber
    , _CCExpMonth
    , _CCExpYear
    , _CC_CVV2
    , _CheckBankName
    , _CheckRoutingNum
    , _CheckAccountNum
    , _CheckNumber
    , _MoneyOrderNumber
    , _SoldDate
    , _Web_Order_ID
    , _EnteredDate
    , _ID_DEPT
    )
    SELECT (SELECT MAX(_ID) FROM Order) + 1
    , @CustomerID
    , @SourceID
    , @created_by
    , @_ID_PT
    , @ShippingID
    , @created_by
    , @AddressID
    , @CCType
    , @CCNumber
    , @CCExpMonth
    , @CCExpYear
    , @CC_CVV2
    , @CBP_BankName 
    , @CBP_RTNumber 
    , @CBP_AcctNumber 
    , @CBP_CheckNumber 
    , '' 
    , @created_date 
    , @WebOrderID 
    , getdate()
    , @dept
    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.

  5. #5
    Join Date
    May 2004
    Posts
    125
    Thanks for the suggestion though, I'll do some testing and see if that will help a bit. As for your questions:

    potentially cause a data nightmare...
    Yeah it could be but we do some conditional checking since the next transation is depending on the previous one.

    Also, you've never seen a dup key problem?
    Being running about a month of heavy use and so far so good without any dups. The ID field does have a unique in it so it would not allow a dup anyway.

    what if the sproc is fired at the same time, and grabs the same "next" id?
    The powers to be tell me we are not using the identity field. Supposely what is going on so we do not get a dup id isthe transaction locks the table, get the max + 1 insert then release the table. As much as I gripe about it, I will not win a battle to switch to allowing SQL maintain the identity(Or GUID for that matter).

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DMWCincy
    As much as I gripe about it, I will not win a battle to switch to allowing SQL maintain the identity(Or GUID for that matter).
    Wow, they sound like my kindred spirits.....

    But if I had to want to make an artificial IDENTITY, I would the go with the IDENTITY...much less headaches...as you've seen...

    And as far as locking the table on a select? I wouldn't have thought so...

    Anyone?

    Geez what a scrub I yam....

    I'll test it out.....
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    -- Nope No locking...you got proof they're bs'ing you
    -- What the reason not to use identity...since they are using a surrogate key anyway?
    -- scrubs

    Code:
    --Connection 1
    
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int, Col2 char(1))
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1, 'A' UNION ALL
    SELECT 2, 'B' UNION ALL
    SELECT 3, 'C'
    GO
    
    BEGIN TRAN
      DECLARE @x int
      SELECT @x = (SELECT MAX(Col1) FROM myTable99) + 1
    GO
    
    -- In another Connection...Connection 2
    
      DECLARE @x int
      SELECT @x = (SELECT MAX(Col1) FROM myTable99) + 1
      SELECT @x
    GO
    
    -- Clean Up Connection 1 Done after executing code in Connection 2
    
    ROLLBACK TRAN
    DROP TABLE myTable99
    GO
    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.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Try to use HOLDLOCK in your SELECT when getting the MAX value.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Isn't the prefered solution for this scenario is to have a last_id table?

    CREATE LAST_ID ([ID] int, dummy char(1))
    GO

    in sproc
    BEGIN TRAN

    DEcLARE @ID int

    UPDATE LAST_ID SET dummy = 'x'

    SELECT @ID = ID + 1 FROM LAST_ID

    ...perform your queries...

    UPDATE LAST_ID SET [ID] = @ID

    COMMIT TRAN

    ???

    EDIT: I would also add a trigger to make sure there is only 1 row in that table...
    Last edited by Brett Kaiser; 06-04-04 at 15:28.
    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.

Posting Permissions

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