Post reply

why deadlock?

  • January 30, 2008 at 7:50 am

    #119736

    I have a stored procedure that does this:

    create procedure MyProcedure

    @orderid int

    as

    begin tran

    insert into tblMytable select @orderid, getdate()

    ......

    .......

    .........

    delete from tblMytable with (rowlock) where orderid = @orderid

    commit

    whenever two processes running at the same time, with/without the same @orderid, Running profiler shows the delete statement is where they run into deadlock, the lock resource is on rid of tblMytable. They both shows owning U lock and requesting X lock.

    I dont get it. Could someone please explain it to me? Why there is U lock?

    Thanks!

  • January 30, 2008 at 8:20 am

    #773323

    Do you have index on orderid? If not, tablescan can try to access the same row in both transactions I think, leading to deadlock.

    Piotr

    ...and your only reply is slàinte mhath

  • January 30, 2008 at 8:23 am

    #773328

    Thanks!

    I do not have index on orderid, let me add it and see it fix it.

  • January 30, 2008 at 8:29 am

    #773335

    It works now with the index! Thanks for the help!

  • January 30, 2008 at 9:27 am

    #773381

    What if you remove BEGIN TRAN and COMMIT?

  • January 30, 2008 at 12:22 pm

    #773463

    Sorry it has to be wrapped up in one transaction.

    Thanks for the reply.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply