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 :(Put option (nolock) on your select statement.|||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 :)|||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.|||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 :)|||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|||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 :) )