Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: DeadLock - analysis, please help

    I get a deadlock error.
    As i understand from the trace the deadlock resource is rowid,but there is no chance that 2 different request work on the same row-id.
    each request (transaction) work on different retail-transaction, so i dont underrated how i get deadlock on rowid in table retail-transaction.

    Attached the deadlock graph as a xml.

    PS - what is the meaning of owner mode X?

    Thank you very much!!!!!!

    The table:
    CREATE TABLE [dbo].[TRN_RETAIL_TRANSACTION_TAB](
    [TRANSCTION_ID] [int] IDENTITY(1,1) NOT NULL,
    [RETAIL_TRANSACTION] [varbinary](max) NULL,
    [UPDATED_DATE] [datetime] NOT NULL,
    [UPDATED_STORE_ID] [int] NULL,
    [UPDATED_POS_ID] [int] NULL,
    [IS_CLOSED] [bit] NOT NULL DEFAULT ((0)),
    [IS_SUSPENDED] [bit] NOT NULL DEFAULT ((0)),
    [SEQUENCE_NUMBER] [int] NOT NULL DEFAULT ((-1)),
    [IS_TRAINING_MODE] [bit] NOT NULL DEFAULT ((0))
    ) ON [PRIMARY]


    CREATE NONCLUSTERED INDEX [IX_TRN_RETAIL_TRANSACTION_TAB] ON [dbo].[TRN_RETAIL_TRANSACTION_TAB]
    (
    [SEQUENCE_NUMBER] ASC,
    [UPDATED_POS_ID] ASC,
    [UPDATED_STORE_ID] ASC
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


    CREATE NONCLUSTERED INDEX [IX_TRN_RETAIL_TRANSACTION_TAB_TRANSCTION_ID] ON [dbo].[TRN_RETAIL_TRANSACTION_TAB]
    (
    [TRANSCTION_ID] ASC
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
    Attached Files Attached Files
    Last edited by avihai; 08-01-10 at 17:25.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    mode X = Exclusive lock mode.

    Are you sure it is a row lock? Looks like it is a table lock!

    What are you inserting in to the RETAIL_TRANSACTION column? Is there any need for it to be varbinary(max)? A max column updated from a value that fits on row to one that spill over [a page] might cause this problem?
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Posts
    24

    Answers

    1. in the var there is a binary data in size around 4k.
    2. i dont sure that this is row lock, i just guess it by see in the file (deadlock) -ridlock
    3. i change the index on transaction_id to clustered and it solve the deadlock and dont know why it solve it (the lock was on row not on index . this is what i understand)



    4. I dont understand what happened here. how this happened? how working on differents rows cause deadlock. why one is in owner mode u and than need X?


    Thank you very much

Posting Permissions

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