Deadlock detected

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Article Type: General
Product: Aleph
Product Version: 17.01

Description:
For the second time this month we have a deadlock error on production from the program that updates Z00R and Z13. The first time it happened was 3/6/07 and the second was yesterday 3/29/07. Both deadlocks occurred during deletes from Z00R. The full trace files on magnolia are:

/oracle/app/oracle/admin/aleph17/udump/aleph17_ora_27882.trc

/oracle/app/oracle/admin/aleph17/udump/aleph17_ora_14084.trc

An excerpt of yesterday's error trace file is below:

*** 2007-03-28 17:29:10.922
*** SESSION ID:(45.41697) 2007-03-28 17:29:10.889
DEADLOCK DETECTED
Current SQL statement for this session:
delete from fcc01.z00r where z00r_doc_number = :v1
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0001002f-00417c7c 224 45 X 43 33 X
TX-000b0008-0000b64d 43 33 X 224 45 X
session 45: DID 0001-00E0-0000126D session 33: DID 0001-002B-0000016B
session 33: DID 0001-002B-0000016B session 45: DID 0001-00E0-0000126D
Rows waited on:
Session 33: obj - rowid = 00018B85 - AAAYuFABWAAAJu7AAJ
(dictionary objn - 101253, file - 86, block - 39867, slot - 9)
Session 45: obj - rowid = 0000CEC3 - AAAM7DABEAAAYaxAAB
(dictionary objn - 52931, file - 68, block - 100017, slot - 1)
Information on the OTHER waiting sessions:
Session 33:
pid=43 serial=56487 audsid=2538867 user: 13/ALEPH
O/S info: user: aleph, term: , ospid: 22562, machine: magnolia
program: rts32@magnolia (TNS V1-V3)
application name: rts32@magnolia (TNS V1-V3), hash value=0
Current SQL Statement:

update fcc01.z13 set z13_rec_key = :r1:i1,z13_year = :r2:i2,z13_open_date = :r3:i3,z13_update_date = :r4:i4,z13_call_no_key = :r5:i5,z13_call_
no_code = :r6:i6,z13_call_no = :r7:i7,z13_author_code = :r8:i8,z13_author = :r9:i9,z13_title_code = :r10:i10,z13_title = :r11:i11,z13_imprint_
code = :r12:i12,z13_imprint = :r13:i13,z13_isbn_issn_code = :r14:i14,z13_isbn_issn = :r15:i15,z13_user_defined_1_code = :r16:i16,z13_user_defi
ned_1 = :r17:i17,z13_user_defined_2_code = :r18:i18,z13_user_defined_2 = :r19:i19,z13_user_defined_3_code = :r20:i20,z13_user_defined_3 = :r21
:i21,z13_user_defined_4_code = :r22:i22,z13_user_defined_4 = :r23:i23,z13_user_defined_5_code = :r24:i24,z13_user_defined_5 = :r25:i25 where z
13_rec_key = :v1
End of information on OTHER waiting sessions.

Resolution:
Ex Libris has decided that debugging this deadlock condition would likely require more resources than is justified by the possible benefit, since the Z00R table is not a critical table.

The worst case is that the delete/update of a single Z00R record doesn't occur. To counteract this possibility, you can periodically run p_manage_07 to regenerate the Z00R table from the Z00. (Note that it *can* be run to do just the Z00R -- and not the Z13.)

The fact that no other site has reported this problem and that your site will be going to version 18 -- where, we believe, there is a strong likelihood that the problem will not occur -- are other reasons for not devoting resources to this problem at this time.

Article last edited: 10/8/2013