Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Deadlock !!!! :(

    Hi

    Sorry for bombading the forum with all these questions, but i am relatively new to sql 2000.

    I am getting dead lock on the following procedure.

    important background information
    1. this is a multi user web-based call centre application
    2. this procedure loads up a new contact based on priority

    I see no reason how a dead lock could occur.
    does any one have any idea. could it be something else that is locking up resource used by this procedure?


    CREATE PROCEDURE topcat.getNewContactInfo
    (
    @contact_id int
    )
    AS
    BEGIN
    begin transaction

    declare @id int

    set @id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)

    UPDATE class_contact SET checked_in = 1 WHERE _id = @id
    SELECT TOP 1 * FROM class_contact
    WHERE _id = @id

    commit
    END
    GO



    wat i dont' get is that, this procedure only has one update statement, this is the only statement that could possibly hold a lock on another resource (i think) , i can't see how a dead lock can happen in this case since this procedure doesn't hold up 2 resources at a time.


    James

  2. #2
    Join Date
    Oct 2003
    Location
    Sofia
    Posts
    16
    Put option (nolock) on your select statement.

  3. #3
    Join Date
    Aug 2003
    Posts
    111
    Thank you


    I hope it works. I have checked it out in books online and according to that, what you suggest should resolve our dead lock issue.

    Cheers

    James

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    dont begin your transactions until you are ready to actually change the data.

    try this instead of nolock. see if it works.

    CREATE PROCEDURE topcat.getNewContactInfo
    (
    @contact_id int
    )
    AS
    BEGIN


    declare @id int

    set @id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)

    begin transaction --begin xact here!!!!

    UPDATE class_contact SET checked_in = 1 WHERE _id = @id
    SELECT TOP 1 * FROM class_contact
    WHERE _id = @id

    -- i would test for an error here with a rollback if @@error > 0 etc...
    this will shorten the time that the actual xact runs lessening contention.
    Last edited by Ruprect; 04-02-04 at 02:57.

  5. #5
    Join Date
    Aug 2003
    Posts
    111
    True

    I have thought of doing that after i found out that beginning a transaction would implicitly imply that the select queries within that transaction could also lock resources.

    The system was built in a hurry by a very busy programmer and it's some what chaotic, especially the database itself.

    now because the system is a multi-user call centre application, originally the "begin transaction" was used to group the select query and update query so to ensure that while someone is bringing up a particular record, another person can't bring up the same record, this was meant to be achieved by setting the "checked_in" flag to "1" with the update statement.

    as of now i still can't see how the procedure above could cause a dead lock. i will repeat the simplified version of the store procedure above

    begin transaction
    STEP1 - set @id = select _id from table1 where blah blah blah;
    STEP2 - update table1 set blah blah blah where _id = @id
    STEP3 - select * from table1 where _id = @id
    commit

    if you look at STEP1, STEP2 and STEP3, they are all refering to the same record, i.e. even if they try to lock some record, they will lock the same record, how is a dead lock possible in this situation? well unless if STEP1 lock out STEP2 and STEP3 within that same execution of the procedure, this is highly unlikely tho, 1st because it doesn't make sense for one statment to lock out another statement within the same transactoin, and 2ndly because these DEAD LOCK only occur very occasionally.

    any ideas why it would cause a dead lock?


    Cheers, sorry for the length (thank you)

    james

  6. #6
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by Ruprect
    dont begin your transactions until you are ready to actually change the data.

    try this instead of nolock. see if it works.

    this will shorten the time that the actual xact runs lessening contention.

    i will try your suggestoin tho, it may very well work out for us.

    thank you

  7. #7
    Join Date
    Mar 2004
    Posts
    25
    Another option - if you do want to hold the lock on the record from the point that you perform the select, is to write a cursor that will select the record for the id, and then you can specifically update the table using:

    UPDATE <table_name>
    SET <field_name> = <new value>
    WHERE CURRENT OF <cursor_name>


    just a thought...

    (also, while i know you can use CURRENT OF in SQL Server, the example I've given here is Sybase syntax - because that's where I've used it before - but the two are usually the same )

Posting Permissions

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