This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Apr 21, 2011 4:12 PM by 849886 RSS

Identifying deadlocked resources in graph with 1 row lock and 1 table lock

849886 Newbie
Currently Being Moderated
Hi, I have run into repeated occurrences of the deadlock graph at the bottom of this post and have a few questions about it:

1. It appears that proc 44, session 548 is holding a row lock (X). Is the waiter, proc 30, session 542, trying to acquire a row lock (X) also or an exclusive table lock (X) on the table containing that row?
2. Under what circumstances would something hold a row exclusive table lock (SX) and want to upgrade that to a share row exclusive table lock (SSX)?
3. Our table cxml_foldercontent has a column 'structuredDataId' with a FK to cxml_structureddata.id and an ON DELETE SET NULL trigger. Would this help explain why an "update" to one table (i.e.g cxml_foldercontent) would also need to acquire a lock in a foreign table, cxml_structureddata?
4. What is the difference between "Current SQL statement:" and "Current SQL statement for this session:"? That terminology is confusing. Is session 542 executing the "update" or the "delete"?
5. In the "Rows waited on:" section is it saying that Session 542 is waiting on on obj - rowid = 0000BE63 - AAAL5jAAGAAA6tZAAK or that it is has the lock on that row and other things are waiting on it?

A couple of notes:
- the cxml_foldercontent.structuredDataId FK column has an index on it already
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name                    process session holds waits  process session holds waits
TX-003a0011-000003d0        44       548     X               30        542             X
TM-0000be63-00000000       30       542     SX              44        548     SX    SSX
session 548: DID 0001-002C-000002D9     session 542: DID 0001-001E-00000050
session 542: DID 0001-001E-00000050     session 548: DID 0001-002C-000002D9
Rows waited on:
Session 542: obj - rowid = 0000BE63 - AAAL5jAAGAAA6tZAAK
  (dictionary objn - 48739, file - 6, block - 240473, slot - 10)
Session 548: no row
Information on the OTHER waiting sessions:
Session 542:
  pid=30 serial=63708 audsid=143708731 user: 41/CASCADE
  O/S info: user: cascade, term: unknown, ospid: 1234, machine: 
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:

update cascade.cxml_foldercontent set name=:1 , lockId=:2 , isCurrentVersion=:3 , versionDate=:4 , metadataId=:5 , permissionsId=:6 , workflowId=:7 , isWorkingCopy=:8 , parentFolderId=:9 , relativeOrder=:10 , cachePath=:11 , isRecycled=:12 , recycleRecordId=:13 , workflowComment=:14 , draftUserId=:15 , siteId=:16 , prevVersionId=:17 , nextVersionId=:18 , originalCopyId=:19 , workingCopyId=:20 , displayName=:21 , title=:22 , summary=:23 , teaser=:24 , keywords=:25 , description=:26 , author=:27 , startDate=:28 , endDate=:29 , reviewDate=:30 , metadataSetId=:31 , expirationNoticeSent=:32 , firstExpirationWarningSent=:33 , secondExpirationWarningSent=:34 , expirationFolderId=:35 , maintainAbsoluteLinks=:36 , xmlId=:37 , structuredDataDefinitionId=:38 , pageConfigurationSetId=:39 , pageDefaultConfigurationId=:40 , structuredDataId=:41 , pageStructuredDataVersion=:42 , shouldBeIndexed=:43 , shouldBePublished=:44 , lastDatePublished=:45 , lastPublishedBy=:46 , draftOriginalId=:47 , contentTypeId=:48  where id=:49
End of information on OTHER waiting sessions.
Current SQL statement for this session:
delete from cascade.cxml_structureddata where id=:1
  • 1. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    sb92075 Guru
    Currently Being Moderated
    does an index actually exist for the FK
  • 2. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Yes, in my "notes" section above I mentioned that there is a index on the FK column.
  • 3. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I have tried to re-format the output.
    Deadlock graph:
    ---------Blocker(s)-------- ---------Waiter(s)---------
    Resource Name            process session holds waits process session holds waits
    TX-003a0011-000003d0       44       548      X                  30      542              X
    TM-0000be63-00000000      30        542    SX                 44       548     SX SSX
    session 548: DID 0001-002C-000002D9 session 542: DID 0001-001E-00000050
    session 542: DID 0001-001E-00000050 session 548: DID 0001-002C-000002D9
    Rows waited on:
    Session 542: obj - rowid = 0000BE63 - AAAL5jAAGAAA6tZAAK
    (dictionary objn - 48739, file - 6, block - 240473, slot - 10)
    Session 548: no row
    Information on the OTHER waiting sessions:
    Session 542:
    pid=30 serial=63708 audsid=143708731 user: 41/CASCADE
    O/S info: user: cascade, term: unknown, ospid: 1234, machine:
    program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
    Current SQL Statement:

    update cascade.cxml_foldercontent set name=:1 , lockId=:2 , isCurrentVersion=:3 , versionDate=:4 , metadataId=:5 , permissionsId=:6 , workflowId=:7 , isWorkingCopy=:8 , parentFolderId=:9 , relativeOrder=:10 , cachePath=:11 , isRecycled=:12 , recycleRecordId=:13 , workflowComment=:14 , draftUserId=:15 , siteId=:16 , prevVersionId=:17 , nextVersionId=:18 , originalCopyId=:19 , workingCopyId=:20 , displayName=:21 , title=:22 , summary=:23 , teaser=:24 , keywords=:25 , description=:26 , author=:27 , startDate=:28 , endDate=:29 , reviewDate=:30 , metadataSetId=:31 , expirationNoticeSent=:32 , firstExpirationWarningSent=:33 , secondExpirationWarningSent=:34 , expirationFolderId=:35 , maintainAbsoluteLinks=:36 , xmlId=:37 , structuredDataDefinitionId=:38 , pageConfigurationSetId=:39 , pageDefaultConfigurationId=:40 , structuredDataId=:41 , pageStructuredDataVersion=:42 , shouldBeIndexed=:43 , shouldBePublished=:44 , lastDatePublished=:45 , lastPublishedBy=:46 , draftOriginalId=:47 , contentTypeId=:48 where id=:49
    End of information on OTHER waiting sessions.
    Current SQL statement for this session:
    delete from cascade.cxml_structureddata where id=:1
    The trace is for Session 548. 542 is the "Other" session running the UPDATE.


    Hemant K Chitale

    Edited by: Hemant K Chitale on Mar 24, 2011 11:46 AM
  • 4. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Thanks, Hermant, for the clarification on which query belongs to which session.

    Any ideas on the other questions above -- lock types, lock upgrades, foreign keys?
  • 5. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    cxml_structureddata is the "parent" and "cxml_foldercontent" is the child table ?

    Indexed foreign keys help avoid locking the child table. They do not necessarily prevent deadlocks.

    The deadlock occurs not because of one piece of DML but because each of the sessions has executed two DMLs (or DDL).

    Hemant K Chitale
  • 6. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Mohamed Houri Pro
    Currently Being Moderated
    Dear
    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name                    process session holds waits  process session holds waits
    TX-003a0011-000003d0        44       548     X               30        542             X
    TM-0000be63-00000000       30       542     SX              44        548     SX    SSX
    Your deadlock trace is showing to enqueue types
    TX-003a0011-000003d0        44       548     X               30        542             X
    and
    TM-0000be63-00000000       30       542     SX              44        548     SX    SSX
    when you have a TM-enqueue lock held on SX mode it is 90% a symptom of a DML operation on a parent table with a child table having a FK with out index.

    (a) to correct that you need to have all your FKs indexed

    And when you have TX-enqueue held on mode X and waited on mode X then you are faced to a row exclusive lock

    (b) you should then look to your application logic in order to avoid that two session can process the same row at the same time

    Best Regards

    Mohamed Houri
  • 7. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Hemant K Chitale wrote:
    cxml_structureddata is the "parent" and "cxml_foldercontent" is the child table ?

    Indexed foreign keys help avoid locking the child table. They do not necessarily prevent deadlocks.

    The deadlock occurs not because of one piece of DML but because each of the sessions has executed two DMLs (or DDL).
    Now that you mention it, we're doing something a little odd...

    cxml_foldercontent has a "structuredDataId" column with a FK to cxml_structureddata.id and a unique constraint on it. This FK has an ON DELETE SET NULL trigger and an index.

    But, cxml_structureddata also has an "owningEntityId" FK to cxml_foldercontent.id. One or more structureddata records can belong to a single foldercontent record. There is no ON DELETE trigger associated with this FK but it does have an index.

    I know this is not conventional, but we have not run into problems to this point. Any thoughts on this?
  • 8. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Mohamed Houri wrote:
    when you have a TM-enqueue lock held on SX mode it is 90% a symptom of a DML operation on a parent table with a child table having a FK with out index.

    (a) to correct that you need to have all your FKs indexed
    In this case, all of my FKs have indexes on them -- some unique and some not. Could the unique vs. non-unique make any difference?
    And when you have TX-enqueue held on mode X and waited on mode X then you are faced to a row exclusive lock

    (b) you should then look to your application logic in order to avoid that two session can process the same row at the same time
    Will a TX-enqueue held on mode X always be waited on by another TX-enqueue row lock X? Or can it be waited on by an Exclusive (X) table lock?

    Also, is there any way to definitively determine which table is locked by the TM-enqueue using the info from the deadlock graph?

    Lastly, what would cause the other process to want to upgrade its row exclusive table lock (SX) to a share row exclusive one (SSX)?
  • 9. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Sorry, but I fail to understand. Are you saying that the two tables have FKs to each other ? I wonder how that works ? How do you manage to insert records ? Are the constraints disabled or deferred ?


    An ON DELETE SET NULL would require Oracle to update every child row (column structuredDataId in cxml_foldercontent) that corresponds to the cxml_structureddata.id. Because you have an index, a table lock isn't necessary but rows in both tables have to be locked by the session running the DELETE on cxml_structureddata.
    So the DELETE by session 548 has to also update cxml_foldercontent while, at the same time, the UPDATE by session 542 is updating one or more of the same rows in cxml_foldercontent.
    'owningEntityId' is not being updated by session 542.

    Your deadlock would be because there was another DML from the same session that had also updated the table.
    You might be doing DML on the two tables in reverse order -- i.e one session updates cxml_structureddata and then cxml_foldercontent while the other session updates cxml_foldercontent and then cxml_structureddata (the "update" could be an INSERT or UPDATE or DELETE).

    The beginning of the trace only shows the latest/current SQL. However, further down it shows previous SQLs for the current session that wrote the trace. You should be able to identify the other DML done by the same session.


    Hemant K Chitale
  • 10. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Mohamed Houri Pro
    Currently Being Moderated
    In this case, all of my FKs have indexes on them -- some unique and some not. Could the unique vs. non-unique make any difference?
    What is important for a foreign key is to be indexed (of course if the parent table is deleted/merged/updated, or if a performance reason imposes it). Wether this index is unique or not doesn't matter (as far as i know).But, you should ask your self the following question : what is the meaning of having a 1 to 1 relationship between a parent and a child table ? if you succeed to create a unique index on your FK then this means that for each PK value corresponds at most one FK value!! Isn't it? is this what you want to have?
    Will a TX-enqueue held on mode X always be waited on by another TX-enqueue row lock X? Or can it be waited on by an Exclusive (X) table lock?
    Not really clear.
    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name             process session holds waits  process session holds waits
    TX-003a0011-000003d0      44       548     X             30     542             X
    TM-0000be63-00000000      30       542     SX            44     548     SX    SSX
    The first enqueue is a TX (Transaction Enqueue) held by session 548 on mode X (exclusive). This session represents the blocking session. At the same time the locked row is waited on by the blocked session (542) and the wait is on mode X (exclusive). So put it simply, we have here session 542 waiting for session 548 to release it lock (may be by commiting/roll backing). At this step we are not in presence of a deadlock.

    The second line of the deadlock graph shows that session 542 is the blocking session and it is doing a TM enqueue (DML lock) held on SX(Shared eXclusive). While session 548(which is the waiting session) is blocked by session 542 and is waiting on SSX mode.
    Here we see that 548 is blocking session 542 via a TX enqueue and session 542 is blocking session 548 via a TM enqueue ---> That is the deadlock. Oracle will then immediately choose arbitrarlly a victim session (542 or 548) and kill its process letting the remaining session continuing its work.

    That is your situation explained here.

    The deadlock graph is also showing you what was doing both session before they deadlock themselves
    /* Session 542 DML */
    UPDATE CASCADE.cxml_foldercontent
       SET NAME = :1,
           lockid = :2,
           iscurrentversion = :3,
           versiondate = :4,
           metadataid = :5,
           permissionsid = :6,
           workflowid = :7,
           isworkingcopy = :8,
           parentfolderid = :9
           ....
     WHERE ID = :49;
     
    /* session 548 */
    DELETE FROM CASCADE.cxml_structureddata
          WHERE ID = :1
    (a) Verify that all your FK are indexed (be carreful that the FK columns should be at the leading edge of the index)
    (b) Verify the logic of the DML against cxml_foldercontent

    Hope this helps

    Mohamed Houri
  • 11. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Hemant K Chitale wrote:
    Sorry, but I fail to understand. Are you saying that the two tables have FKs to each other ? I wonder how that works ? How do you manage to insert records ? Are the constraints disabled or deferred ?
    No, the constraints are neither disabled nor deferred.

    The "parent" table is cxml_foldercontent with many cxml_structureddata records belong to it via the cxml_structureddata.owningEntityId column.

    The relationship between cxml_foldercontent is more complicated. cxml_structureddata are linked to each other in linked-list. So all of the cxml_structureddata records that belong to a single cxml_foldercontent are chained together and cxml_foldercontent.structuredDataId points to the head of that chain.

    When we insert records into cxml_structureddata we know the owningEntityId for the parent cxml_foldercontent record. Then, after we've inserted the records into cxml_structureddata we locate the head of the chain of child records and set that in the cxml_foldercontent.structuredDataId column for the parent record.

    Does that basically make sense now? In retrospect, there are probably less confusing ways to implement this in the database than a linked-list and a head pointer and FKs going both ways.
    An ON DELETE SET NULL would require Oracle to update every child row (column structuredDataId in cxml_foldercontent) that corresponds to the cxml_structureddata.id. Because you have an index, a table lock isn't necessary but rows in both tables have to be locked by the session running the DELETE on cxml_structureddata.
    So the DELETE by session 548 has to also update cxml_foldercontent while, at the same time, the UPDATE by session 542 is updating one or more of the same rows in cxml_foldercontent.
    'owningEntityId' is not being updated by session 542.
    This seems unlikely as our application wouldn't allow a cxml_foldercontent to be updated while it's child cxml_structureddata records were being deleted. Could it be that the DELETE is updating rows in the same block in cxml_foldercontent as the row being updated?
    Your deadlock would be because there was another DML from the same session that had also updated the table.
    You might be doing DML on the two tables in reverse order -- i.e one session updates cxml_structureddata and then cxml_foldercontent while the other session updates cxml_foldercontent and then cxml_structureddata (the "update" could be an INSERT or UPDATE or DELETE).

    The beginning of the trace only shows the latest/current SQL. However, further down it shows previous SQLs for the current session that wrote the trace. You should be able to identify the other DML done by the same session.
    Does that mean there is a more verbose setting for these deadlock traces? Would you have any idea how to enable it or gather that information?
  • 12. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Mohamed Houri wrote:
    What is important for a foreign key is to be indexed (of course if the parent table is deleted/merged/updated, or if a performance reason imposes it). Wether this index is unique or not doesn't matter (as far as i know).But, you should ask your self the following question : what is the meaning of having a 1 to 1 relationship between a parent and a child table ? if you succeed to create a unique index on your FK then this means that for each PK value corresponds at most one FK value!! Isn't it? is this what you want to have?
    Thanks, as I mentioned above, cxml_structureddata is actually the child table of cxml_foldercontent with 1 or more records' owningEntityId referring to rows in cxml_foldercontent. The reason for the FK on cxml_foldercontent.structuredDataId is a little ambiguous but it explained above.
    Will a TX-enqueue held on mode X always be waited on by another TX-enqueue row lock X? Or can it be waited on by an Exclusive (X) table lock?
    Not really clear.
    Sorry, are you saying my question is unclear or it's not clear why type of eXclusive lock session 542 is trying to acquire in the first line of the trace? Do you think that the exclusive lock being held by session 548 in the first line is on rows in cxml_foldercontent (due to the ON DELETE SET NULL on these child rows) or rows in the cxml_structureddata that it's actually deleting?

    Is there any way for me to tell for certain?
    The first enqueue is a TX (Transaction Enqueue) held by session 548 on mode X (exclusive). This session represents the blocking session. At the same time the locked row is waited on by the blocked session (542) and the wait is on mode X (exclusive). So put it simply, we have here session 542 waiting for session 548 to release it lock (may be by commiting/roll backing). At this step we are not in presence of a deadlock.

    The second line of the deadlock graph shows that session 542 is the blocking session and it is doing a TM enqueue (DML lock) held on SX(Shared eXclusive). While session 548(which is the waiting session) is blocked by session 542 and is waiting on SSX mode.
    Here we see that 548 is blocking session 542 via a TX enqueue and session 542 is blocking session 548 via a TM enqueue ---> That is the deadlock. Oracle will then immediately choose arbitrarlly a victim session (542 or 548) and kill its process letting the remaining session continuing its work.

    That is your situation explained here.
    Thanks, any idea why session 542 (the DELETE from cxml_structureddata) would be trying to upgrade it's lock to SSX? Is this lock mode required to update a child tables foreign key columns when using an ON DELETE SET NULL trigger? Having read more about SSX, I'm not sure I understand in what cases it's used. Is there a way for me to confirm with 100% certainty specifically which tables in the TM enqueue locks are being held on? Is session 548 definitely trying to acquire an SSX mode on my cxml_foldecontent table or could it be cxml_structureddata table?
    (a) Verify that all your FK are indexed (be carreful that the FK columns should be at the leading edge of the index)
    Thanks, we've done this already. When you say the "leading edge" you mean for a composite index? These indexes are all single column.
    (b) Verify the logic of the DML against cxml_foldercontent
    Can you be more specific? Any idea what I'm looking for?
  • 13. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Does that basically make sense now?
    No. Your description is more about in-memory linked lists than about relational tables and indexes !

    Does that mean there is a more verbose setting for these deadlock traces?
    No. Other SQLs would also be listed in the deadlock trace.


    Hemant K Chitale
  • 14. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    846883 wrote:
    Is there a way for me to confirm with 100% certainty specifically which tables in the TM enqueue locks are being held on? Is session 548 definitely trying to acquire an SSX mode on my cxml_foldecontent table or could it be cxml_structureddata table?
    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name             process session holds waits  process session holds waits
    TX-003a0011-000003d0      44       548     X             30     542             X
    TM-0000be63-00000000      30       542     SX            44     548     SX    SSX
    The TM lock if for an object_id, which shows up as the be63 in the lock information. In decimal this is 48,739 - which is also the data_object_id listed a little further down your trace as the translation of the rowid. So you can check dba_objects for that object_id/data_object_id.

    SSX is almost certain to be a "foreign key locking" problem (and it's SSX (share sub exclusive, mode 5) rather than S (share, mode 4)) because the session has already modified the table before demanding the table-level lock.

    I've sketched the description of your data structures, and I think you are describing the following (table names and column names are only approximations because I haven't opened a second window to keep referencing back):

    Folder Content:
    primary key on folder id, indexed
    unique key (I hope) and foreign key on first_structureddata_id, indexed

    Structured data
    Primary key on structureddata id column, indexed
    Foreign key on folder id, indexed
    foreign key on "previous structured data id" (the column used to represent the linked list), indexed.

    You have stated that 'all foreign keys' are indexed - but I don't think you've mentioned the linked list column as a foreign key: was this a simple omission from the description (or me missing it, of course), or is it the source of your problem ?

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    A general reminder about "Forum Etiquette / Reward Points": http://forums.oracle.com/forums/ann.jspa?annID=718

    If you never mark your questions as answered people will eventually decide that it's not worth trying to answer you because they will never know whether or not their answer has been of any use, or whether you even bothered to read it.

    It is also important to mark answers that you thought helpful - again it lets other people know that you appreciate their help, but it also acts as a pointer for other people when they are researching the same question, moreover it means that when you mark a bad or wrong answer as helpful someone may be prompted to tell you (and the rest of the forum) what's so bad or wrong about the answer you found helpful.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points