Unanswered: SQL Server "transaction (process id 69) was deadlocked
My name is Fabio and I post from Italy.
First, I don't know if this argument was already discussed in the past, but I'm new in this group so ...
Second I'm not so expert in DB due to the fact that I'm using SQL for the first time in my life ...
I use a store procedure to pass to every single user in my intranet (more than 150), details of different clients taken from an SQL table containing around 30.000 names.
Users have an ASP page displaying the information Selected in the DB.
This means that 150 users display info of 150 different clients.
To to this I use this code in store procedure:
CREATE PROCEDURE sp_assign_name
if exists(select top 1 * from recallornotes where tmkoperator= @iduser)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7 where id in (select top 1 id from recallornotes where tmkoperator=@iduser)
if exists(select top 1 id from nonotes)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7, tmkoperator =@iduser where id in (select top 1 id from nonotes with (UPDLOCK) order by NewID())
This is working quite well when the number of users are more ore less around 50/60, when the number grows, on the IIS server (Pentium IV server, with Win 2000 in English, MS SQL 2000, and 1 Giga of ram), a file called DLLHOST.exe start to use the 100% of the CPU, and the users cannot display any other ASP page on their screens.
It is not a virus (some newsgroup report this problem connect to a worm virus, but we have latest antivirus files installed and spyware detect/delete on).
The SQL log reports this error:
"transaction (process id 69) was deadlocked on (lock) resources with another process and has been chosen as the deadlock victim. rerun the transaction".
Is there a way to avoid the conflict that occur when different users are trying to select the same record in the DB ?
In other terms, which process will you use in the same situation to select one record per user ?
"if exists(select top 1 * from recallornotes where tmkoperator= @iduser)"
...should just be written as
"if exists(select * from recallornotes where tmkoperator= @iduser)"
There is no need for the TOP clause.
"if exists(select top 1 id from nonotes)"
...which should just be:
"if exists(select * from nonotes)"
But actually, there is no need to check whether the record exists at all before applying your update. You can do the whole thing in a single statement:
set tmkmotrecall=convert(nvarchar(1), tmkstatus),
inner join recallornotes on nominativitelecom.id = recallornotes.id and tmkoperator=@iduser
Now for your second part - What the heck is THIS supposed to do:
"select top 1 id from nonotes with (UPDLOCK) order by NewID()"????
It appears as if you are using the NewID() function to randomly select a record, but every time you run this statement the server has to execute NewID against every record in the table and then order the results by the resulting 16 byte values. Get a million records in there and it gets really processor intensive. That could be causing your deadlocks.
If it's not practically useful, then it's practically useless.