Compliant Database DevOps
Standardize team-based development
Automate database deployments
Monitor performance and availability
Protect and preserve data
From Jonathan Lewis’ Oracle Scratchpad blog.
Here’s a deadlock graph that appeared on Oracle-L and OTN a couple of days ago:
|
1 2 3 4 5 |
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-001a0002-0002a0fe 196 197 X 166 1835 S TM-0000c800-00000000 166 1835 SX 196 197 SX SSX |
It’s a little unusual because instead of the common TX mode 6 (eXclusive) crossover we have one TX and one TM lock, the TX wait is for mode 4 (S) and the TM wait is for a conversion from 3 (SX) to 5 (SSX).
The modes and types give us some clues about what’s going on: TX/4 is typically about indexes involved in referential integrity (though there are a couple of more exotic reasons such as wait for ITLs, Freelists or tablespace status change); conversion of a TM lock from mode 3 to mode 5 is only possible (as far as I know) in the context of missing foreign key indexes when you delete a parent row.
Here’s a simple data set to help demonstrate the type of thing that could have caused this deadlock:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
drop table child; drop table parent; create table parent ( id number(4), name varchar2(10), constraint par_pk primary key (id) ) ; create table child( id_p number(4), id number(4), name varchar2(10), constraint chi_pk primary key (id, id_p), constraint chi_fk_par foreign key(id_p) references parent on delete cascade ) ; insert into parent values (1,'Smith'); insert into parent values (2,'Jones'); insert into child values(1, 1, 'Simon'); insert into child values(2, 1, 'Janet'); commit; |
Note that I have defined the primary key on the child the “wrong way round”, so that the foreign key doesn’t have a supporting index. Note also that the foreign key constraint is defined as ‘on delete cascade’ – this isn’t a necessity, but it means I won’t have to delete child rows explicitly in my demo.
To find out Jonathan’s next steps, read the full post at: http://jonathanlewis.wordpress.com/2011/08/29/deadlock-2/
Load comments