Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Red face Unanswered: SQL Server "transaction (process id 69) was deadlocked


    Hello Everybody,
    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
    @iduser int
    AS
    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)
    else
    begin
    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())
    end
    GO

    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 ?

    Thanks in advance for your precious help,
    Fabio

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, clean things up a bit:

    "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.

    Same for:

    "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:

    update nominativitelecom
    set tmkmotrecall=convert(nvarchar(1), tmkstatus),
    tmkstatus=7
    from nominativitelecom
    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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