This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Apr 21, 2011 4:12 PM by 849886 Go to original post RSS
  • 15. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Hemant K Chitale wrote:
    Does that basically make sense now?
    No. Your description is more about in-memory linked lists than about relational tables and indexes !
    Ok, the cxml_foldercontent has a FK column called structuredDataId which has a unique constraint on it and points to records in the cxml_structureddata table.

    Each record in the cxml_structureddata table has a FK column called owningEntityId (not unique) that points to the record in cxml_foldercontent which "owns" the set of records in the cxml_structureddata table.

    Additionally, each cxml_structureddata record has a FK column called "structuredDataId" which points to another record in the cxml_structureddata table -- the next node in the list of nodes.

    So, for example you might have...

    cxml_foldercontent record with PK 'A' which is the parent record of two records in the cxml_structureddata table with PKs 'B' and 'C'

    Both B and C's owningEntity is 'A'
    A's structureddataId is 'B'
    B's structureddataId is 'C'

    Is that more clear?
    Does that mean there is a more verbose setting for these deadlock traces?
    No. Other SQLs would also be listed in the deadlock trace.
    In the traces we're seeing... only the most recent queries executed by each session are being listed right up to the point of the deadlock. I can't see any other DML that took place earlier in the session.
  • 16. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    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.
    Thanks! A quick question about this - is the dba_objects specific to the database and would it come with the database when it is exported. We're actually looking at this deadlock trace but using an export of the affected database so I'm not sure querying our local dba_objects table will give us the right information. Is that accurate?
    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.
    Yes, this description is almost 100% accurate. There is indeed a unique constraint/key on first_structureddata_id and FK.

    The one small difference is that "previous structured data id" is actually "next structured data id" so it's the last node in the list that has a NULL "next structured data id" rather than the first node. This is only relevant because the cxml_foldercontent points to the first node in the list.
    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 ?
    Yes, this was an omission. The linked list column cxml_structureddata.structureddataid is a FK and is indexed though not with a unique key. This could probably use a unique key but it doesn't seem like this would be the cause of my problem, right?
    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.
    Thanks for the reminder! You guys have been most helpful and I've been doing my best to mark your answers as helpful each and every time. I was under the impression that my thread would receive less attention if I pre-maturely marked it as answered if there were still outstanding questions.
  • 17. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    jgarry Guru
    Currently Being Moderated
    >
    Thanks! A quick question about this - is the dba_objects specific to the database and would it come with the database when it is exported. We're actually looking at this deadlock trace but using an export of the affected database so I'm not sure querying our local dba_objects table will give us the right information. Is that accurate?
    Entirely arbitrary, check as each user with select data_object_id from user_objects where object_name='your object name here';

    Imports recreate objects (unless you flag them to ignore errors on ones you already have created), clones will likely have the same numbers. So if you clone/truncate/import, they could have the same numbers. In other words, it depends. The point is to check the object id's in the database you are tracing.

    >
    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.
    Thanks for the reminder! You guys have been most helpful and I've been doing my best to mark your answers as helpful each and every time. I was under the impression that my thread would receive less attention if I pre-maturely marked it as answered if there were still outstanding questions.
    Depends how interesting the thread is, and different people pay different amounts of attention to the stars. Some people may even follow others just to correct their errors. Everyone wants to be helpful, some are a little "too helpful." Some people even think the rating system has negative value.
  • 18. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    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.
    Ok, I've confirmed that the TM lock with mode SX is being held on the cxml_foldercontent table. So, based on that information, I would guess that session 548 (the "delete from cxml_structureddata") is trying to lock the cxml_foldercontent in order to update the the cxml_foldercontent.structuredDataId column because it has a FK to cxml_structureddata.id that has an ON DELETE SET NULL trigger. Does that sound plausible? I'm not sure why in that case session 548 would already have an SX lock on the cxml_foldercontent table. Could this be due to the FK on cxml_structureddata.owneingEntityId perhaps? Or more likely due to some DML earlier in session 548?

    Also, is session 542 (the "update cxml_foldercontent...") blocked trying to acquire a row lock on rows in cxml_structureddata in the first line of the deadlock or is trying trying to acquire an exclusive (X) table lock on the whole cxml_structureddata?
  • 19. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    I'm marking the original questions about "identifying deadlocked resources" as answered as I have indeed figured out basically which resources are deadlocked. However, I have not figured out the underlying cause of the deadlock or which FK columns (if any) are involved.
  • 20. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    846883 wrote:

    Ok, I've confirmed that the TM lock with mode SX is being held on the cxml_foldercontent table. So, based on that information, I would guess that session 548 (the "delete from cxml_structureddata") is trying to lock the cxml_foldercontent in order to update the the cxml_foldercontent.structuredDataId column because it has a FK to cxml_structureddata.id that has an ON DELETE SET NULL trigger. Does that sound plausible? I'm not sure why in that case session 548 would already have an SX lock on the cxml_foldercontent table. Could this be due to the FK on cxml_structureddata.owneingEntityId perhaps? Or more likely due to some DML earlier in session 548?

    Also, is session 542 (the "update cxml_foldercontent...") blocked trying to acquire a row lock on rows in cxml_structureddata in the first line of the deadlock or is trying trying to acquire an exclusive (X) table lock on the whole cxml_structureddata?
    If you post "create table" and "create index" statements for the two tables, and any statements that are needed to create the primary, unique, and foreign keys - cutting out any irrelevant columns from the tables if they have long definitions - I'll see if I can find time to emulate the lockup.

    Regards
    Jonathan Lewis
  • 21. Re: Identifying deadlocked resources in graph with 1 row lock and 1 table lock
    849886 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    If you post "create table" and "create index" statements for the two tables, and any statements that are needed to create the primary, unique, and foreign keys - cutting out any irrelevant columns from the tables if they have long definitions - I'll see if I can find time to emulate the lockup.
    Thanks Jonathan. Here they are using: select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', 'SCHEMA') from dual;

    I've included a few other FK columns on the CXML_STRUCTUREDDATA table that also point to the CXML_FOLDERCONTENT table or back to the CXML_STRUCTUREDDATA itself. I've confirmed that all of these have indexes as well. The queries to create the indices are below the queries to create the table

    CREATE TABLE "<SCHEMA>"."CXML_FOLDERCONTENT"
    (     "ID" VARCHAR2(32) NOT NULL ENABLE,
         "PARENTFOLDERID" VARCHAR2(32),
         "STRUCTUREDDATAID" VARCHAR2(32),
         CONSTRAINT "UQ_FCE_PAGESTRUCTUREDDATA" UNIQUE ("STRUCTUREDDATAID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "<SCHEMA>" ENABLE,
         CONSTRAINT "PK_CXML_FOLDERCONTENT" PRIMARY KEY ("ID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "<SCHEMA>" ENABLE,
         CONSTRAINT "FK_FCE_PARENTFOLDER" FOREIGN KEY ("PARENTFOLDERID")
         REFERENCES "<SCHEMA>"."CXML_FOLDERCONTENT" ("ID") ON DELETE SET NULL ENABLE,
         CONSTRAINT "FK_FCE_STRUCTUREDDATA" FOREIGN KEY ("STRUCTUREDDATAID")
         REFERENCES "<SCHEMA>"."CXML_STRUCTUREDDATA" ("ID") ON DELETE SET NULL ENABLE,
    ) PCTFREE 10 PCTUSED 40 INITRANS 25 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "<SCHEMA>"


    CREATE TABLE "<SCHEMA>"."CXML_STRUCTUREDDATA"
    (     "ID" VARCHAR2(32) NOT NULL ENABLE,
         "FILEID" VARCHAR2(32),
         "STRUCTUREDDATAID" VARCHAR2(32),
         "GROUPSTRUCTUREDDATAID" VARCHAR2(32),
         "BLOCKID" VARCHAR2(32),
         "SYMLINKID" VARCHAR2(32),
         "ASSETTYPE" NUMBER(10,0) DEFAULT 1,
         "PAGEID" VARCHAR2(32),
         "OWNERENTITYID" VARCHAR2(32),
         CONSTRAINT "PK_CXML_STRUCTUREDDATA" PRIMARY KEY ("ID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "<SCHEMA>" ENABLE,
         CONSTRAINT "FK_SDATA_OWNERENTITY" FOREIGN KEY ("OWNERENTITYID")
         REFERENCES "<SCHEMA>"."CXML_FOLDERCONTENT" ("ID") ENABLE,
         CONSTRAINT "FK_SDATA_GROUP" FOREIGN KEY ("GROUPSTRUCTUREDDATAID")
         REFERENCES "<SCHEMA>"."CXML_STRUCTUREDDATA" ("ID") ENABLE,
         CONSTRAINT "FK_SDATA_BLOCK" FOREIGN KEY ("BLOCKID")
         REFERENCES "<SCHEMA>"."CXML_FOLDERCONTENT" ("ID") ON DELETE SET NULL ENABLE,
         CONSTRAINT "FK_SDATA_FILE" FOREIGN KEY ("FILEID")
         REFERENCES "<SCHEMA>"."CXML_FOLDERCONTENT" ("ID") ON DELETE SET NULL ENABLE,
         CONSTRAINT "FK_SDATA_PAGE" FOREIGN KEY ("PAGEID")
         REFERENCES "<SCHEMA>"."CXML_FOLDERCONTENT" ("ID") ON DELETE SET NULL ENABLE,
         CONSTRAINT "FK_SDATA_NEXT" FOREIGN KEY ("STRUCTUREDDATAID")
         REFERENCES "<SCHEMA>"."CXML_STRUCTUREDDATA" ("ID") ENABLE,
         CONSTRAINT "FK_SDATA_SYMLINK" FOREIGN KEY ("SYMLINKID")
         REFERENCES "<SCHEMA>"."CXML_FOLDERCONTENT" ("ID") ON DELETE SET NULL ENABLE
    ) PCTFREE 10 PCTUSED 40 INITRANS 25 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "<SCHEMA>"

    CREATE INDEX IDX_SD_OWNER ON CXML_STRUCTUREDDATA(OWNERENTITYID)
    CREATE INDEX IDX_SD_NEXT_GROUP ON CXML_STRUCTUREDDATA(GROUPSTRUCTUREDDATAID)
    CREATE INDEX IDX_SD_NEXT ON CXML_STRUCTUREDDATA(STRUCTUREDDATAID)
    CREATE INDEX IDX_SD_BLOCK ON CXML_STRUCTUREDDATA(BLOCKID)
    CREATE INDEX IDX_SD_FILE ON CXML_STRUCTUREDDATA(FILEID)
    CREATE INDEX IDX_SD_PAGE ON CXML_STRUCTUREDDATA(PAGEID)
    CREATE INDEX IDX_SD_SYMLINK ON CXML_STRUCTUREDDATA(SYMLINKID)
1 2 Previous Next

Legend

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