Hi David, the problem you described looks like an error, which was fixed with 7.4.3.20. See the following PTS note
http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1121565 The error was "implemented" with 7.4.3.17: http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1120641 I assume you are using 7.4.3, because the vtrace shows the following release and application information "70400-ODB". If not please send me the database release identification. If the new patch will not solve your problem, please send me the complete vtrace. Please do not edit the trace, because the problem is caused by the interaction of several tasks. BTW: I think you recognize the subsequent part of the vtrace you send to the mailing list. b03del_inv means that an index record should be removed from an index tree. The index record is specified by an index Key and a primary Key. After the operation is finished you can see b03del_inv root XXX e_ok. This means that the operation was finished successfully (e_ok) and XXX identifies the root page of the corresponding index tree. >b13exclusive pno 104371 means the exclusive access of index root page 104371 is not possible, because at least one share lock exist. Caused by the error described above, the sleep phase of the task "executing b13exclusive" could be extrem long. >b03del_inv Inv Key(9): ' New Form' PrimKey(3): 00C242 b03del_inv root 59709; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C242 b03del_inv root 74597; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C242 b03del_inv root 89483; e_ok >b03del_inv Inv Key(1): 00 PrimKey(3): 00C242 >b13exclusive pno 104371 <= WAIT for index root page access Select * from ROOTS where root = 104371 will show you which index of your base table (table root = 44819) causes the problem. Regards, Torsten SAP DB, SAP Labs Berlin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Montag, 21. Juli 2003 22:55 To: [EMAIL PROTECTED] Subject: Re: Bug with Update? Related to Locking? - GATING ISSUE, PLEASE HELP! Hi, We are still experiencing this problem, and it is readily reproducable on our end. As explained below, the database periodically gets "hung" performing an Update that never returns. I have added a Select on the relevant table before the Update, to make sure the code does have a lock on the table, and that works fine. I have also tried replacing the Update with an Insert, but this hangs periodically too. It **acts** as if the code is waiting for a lock on the tables that it already has a lock on, which is wierd. We only see three locks in the Locks table, which is correct. PLEASE could one of the SAP guys, (or anyone else!), take a look at the trace information provided below? Can anyone please explain what the statement in the trace >b13exclusive pno 104371 means? This is proving to be a BIG problem, so would be very grateful if someone could help. Please let me know if I can provide any more information. Many thanks in advance, David ---------------------- Forwarded by David Hay/Lex/Lexmark on 21/07/2003 04:48 PM --------------------------- David Hay 17/07/2003 03:25 PM To: "Becker, Holger" <[EMAIL PROTECTED]> cc: [EMAIL PROTECTED] Subject: Bug with Update? Related to Locking? (Document link: David Hay) Hi, We are seeing some strange behaviour. We issue some locks on 3 tables when we upload a form (as a lot of other processing goes on), so if someone else is trying to access the table they correctly have to wait. When the lock is released they get access to the table and everything is fine. EXCEPT in ***some*** situations when the first person (with the locks) performs an UPDATE call. We are seeing the update just hang and never return. Consequently we don't drop through our code to the unlock and everything hangs. But why should the Update call never return, and only in some situations? We have captured the vtrace, and the update that returns and the one that doesn't are both the same in this respect: REQUEST: ascii, normal_swap, 70400-ODB (1 segment, len: 512) (483.3282 page 483) dbs SEGMENT 1 (2 parts, len: 512) session_sqlmode, user_cmd with_info command PART (1 argument, size: 32248) buf(420): 'UPDATE forms SET Revision_Number = 0, CycloneFormSetName = 'k' 'i371058470053450', Pages = 2, Title = '30', Available =' resulttablename PART (1 argument, size: 31808) buf(16): 'JDBC_CURSOR_1458' >b07cget key(138): 00000000 00000000 00B5464F 524D5320 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 '..........FORMS ' b07cget root 76304; *** key_not_found *** >KB05 id1193/-2549 update qual TABID 0-02A0 zerokey >b02direct_ql key(3): 00C237 b02direct_ql root 44819; e_ok >b01t_reset tfnTemp; fileTfnNo = 0; ttfnTrigger session: 9; fid = 0 fn: 1A00 0000000000091B20 000000000000 b01t_reset root 17433; e_ok >b07cadd key(14): 00000001 00000000 000002A0 0005 b07cadd root 17433; e_ok >b03del_inv Inv Key(9): ' New Form' PrimKey(3): 00C237 b03del_inv root 59709; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C237 b03del_inv root 74597; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C237 b03del_inv root 89483; e_ok >b03del_inv Inv Key(1): 00 PrimKey(3): 00C237 >From there, though, the one does NOT work finishes that particular block (and >apparently hangs) with >b13exclusive pno 104371 whereas the one the DOES work continues with b03del_inv root 15045; e_ok >b02repl key(3): 00C235 b02repl root 44819; e_ok >b03add Inv Key(3): ' 24' PrimKey(3): 00C235 b03add root 59709; e_ok >b03add Inv Key(1): ' ' PrimKey(3): 00C235 b03add root 74597; e_ok >b03add Inv Key(1): ' ' PrimKey(3): 00C235 b03add root 89483; e_ok >b03add Inv Key(2): 0001 PrimKey(3): 00C235 b03add root 104371; e_ok >b03add Inv Key(9): 00C23500 00000000 01 PrimKey(3): 00C235 b03add root 15045; e_ok KB05 id1173/-10750 return_result >b07cnext zerokey b07cnext root 2431; *** key_not_found *** It appears that a return_result is never issued for the first one, but the question is why? Does anyone know what ">b13exclusive pno 104371" means? A fuller copy of the trace for each one is below. Many thanks in advance, David ***Update request that does NOT return REQUEST: ascii, normal_swap, 70400-ODB (1 segment, len: 512) (298.2426 page 298) dbs SEGMENT 1 (2 parts, len: 512) session_sqlmode, user_cmd with_info command PART (1 argument, size: 32248) buf(420): 'UPDATE forms SET Revision_Number = 0, CycloneFormSetName = 'k' 'i421058544455209', Pages = 2, Title = '26', Available =' resulttablename PART (1 argument, size: 31808) buf(14): 'JDBC_CURSOR_99' >b07cget key(138): 00000000 00000000 00B5464F 524D5320 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 '..........FORMS ' b07cget root 61423; *** key_not_found *** >KB05 id1175/-14592 get direct SYS1CAT key(12): 00000000 000002A0 00150005 >b02get key(12): 00000000 000002A0 00150005 b02get root 59552; e_ok KB05 id1175/-14592 return_result >b07cget key(12): 00000000 000002A0 00950005 b07cget root 61423; *** key_not_found *** >KB05 id1175/-14592 get direct SYS1CAT key(12): 00000000 000002A0 00180005 >b02get key(12): 00000000 000002A0 00180005 b02get root 59552; e_ok KB05 id1175/-14592 return_result >KB05 id1175/-14592 get direct SYS1CAT key(13): 00000000 000002A0 00180005 01 >b02get key(13): 00000000 000002A0 00180005 01 b02get root 59552; e_ok KB05 id1175/-14592 return_result >KB05 id1175/-14592 update qual TABID 0-02A0 zerokey >b02direct_ql key(3): 00C242 b02direct_ql root 44819; e_ok >b01t_reset tfnTemp; fileTfnNo = 0; ttfnTrigger session: 29; fid = 0 fn: 1A00 00000000001D1B20 000000000000 b01t_reset root 1891; e_ok >b07cadd key(14): 00000001 00000000 000002A0 0005 b07cadd root 1891; e_ok >b03del_inv Inv Key(9): ' New Form' PrimKey(3): 00C242 b03del_inv root 59709; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C242 b03del_inv root 74597; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C242 b03del_inv root 89483; e_ok >b03del_inv Inv Key(1): 00 PrimKey(3): 00C242 >b13exclusive pno 104371 ===== T44 ===== id164 ================================299.220 page 299 * * * *** Update request that finishes normally. REQUEST: ascii, normal_swap, 70400-ODB (1 segment, len: 512) (384.5922 page 384) dbs SEGMENT 1 (2 parts, len: 512) session_sqlmode, user_cmd with_info command PART (1 argument, size: 32248) buf(420): 'UPDATE forms SET Revision_Number = 0, CycloneFormSetName = 'k' 'i351058542041839', Pages = 2, Title = '24', Available =' resulttablename PART (1 argument, size: 31808) buf(15): 'JDBC_CURSOR_354' >b07cget key(138): 00000000 00000000 00B5464F 524D5320 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 '..........FORMS ' b07cget root 46809; *** key_not_found *** >KB05 id1173/-10750 update qual TABID 0-02A0 zerokey >b02direct_ql key(3): 00C235 b02direct_ql root 44819; e_ok >b01t_reset tfnTemp; fileTfnNo = 0; ttfnTrigger session: 58; fid = 0 fn: 1A00 00000000003A1B20 000000000000 b01t_reset root 2431; e_ok >b07cadd key(14): 00000001 00000000 000002A0 0005 b07cadd root 2431; e_ok >b03del_inv Inv Key(9): ' New Form' PrimKey(3): 00C235 b03del_inv root 59709; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C235 b03del_inv root 74597; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C235 b03del_inv root 89483; e_ok >b03del_inv Inv Key(1): 00 PrimKey(3): 00C235 b03del_inv root 104371; e_ok >b03del_inv Inv Key(8): 00C23500 00000000 PrimKey(3): 00C235 b03del_inv root 15045; e_ok >b02repl key(3): 00C235 b02repl root 44819; e_ok >b03add Inv Key(3): ' 24' PrimKey(3): 00C235 b03add root 59709; e_ok >b03add Inv Key(1): ' ' PrimKey(3): 00C235 b03add root 74597; e_ok >b03add Inv Key(1): ' ' PrimKey(3): 00C235 b03add root 89483; e_ok >b03add Inv Key(2): 0001 PrimKey(3): 00C235 b03add root 104371; e_ok >b03add Inv Key(9): 00C23500 00000000 01 PrimKey(3): 00C235 b03add root 15045; e_ok KB05 id1173/-10750 return_result >b07cnext zerokey b07cnext root 2431; *** key_not_found *** REQUEST: ascii, normal_swap, 70400-ODB (1 segment, len: 512) (384.5922 page 384) dbs SEGMENT 1 (2 parts, len: 512) session_sqlmode, user_cmd with_info command PART (1 argument, size: 32248) buf(420): 'UPDATE forms SET Revision_Number = 0, CycloneFormSetName = 'k' 'i351058542041839', Pages = 2, Title = '24', Available =' resulttablename PART (1 argument, size: 31808) buf(15): 'JDBC_CURSOR_354' >b07cget key(138): 00000000 00000000 00B5464F 524D5320 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 '..........FORMS ' b07cget root 46809; *** key_not_found *** >KB05 id1173/-10750 update qual TABID 0-02A0 zerokey >b02direct_ql key(3): 00C235 b02direct_ql root 44819; e_ok >b01t_reset tfnTemp; fileTfnNo = 0; ttfnTrigger session: 58; fid = 0 fn: 1A00 00000000003A1B20 000000000000 b01t_reset root 2431; e_ok >b07cadd key(14): 00000001 00000000 000002A0 0005 b07cadd root 2431; e_ok >b03del_inv Inv Key(9): ' New Form' PrimKey(3): 00C235 b03del_inv root 59709; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C235 b03del_inv root 74597; e_ok >b03del_inv Inv Key(1): FF PrimKey(3): 00C235 b03del_inv root 89483; e_ok >b03del_inv Inv Key(1): 00 PrimKey(3): 00C235 b03del_inv root 104371; e_ok >b03del_inv Inv Key(8): 00C23500 00000000 PrimKey(3): 00C235 b03del_inv root 15045; e_ok >b02repl key(3): 00C235 b02repl root 44819; e_ok >b03add Inv Key(3): ' 24' PrimKey(3): 00C235 b03add root 59709; e_ok >b03add Inv Key(1): ' ' PrimKey(3): 00C235 b03add root 74597; e_ok >b03add Inv Key(1): ' ' PrimKey(3): 00C235 b03add root 89483; e_ok >b03add Inv Key(2): 0001 PrimKey(3): 00C235 b03add root 104371; e_ok >b03add Inv Key(9): 00C23500 00000000 01 PrimKey(3): 00C235 b03add root 15045; e_ok KB05 id1173/-10750 return_result >b07cnext zerokey b07cnext root 2431; *** key_not_found *** REQUEST: ascii, full_swap, 70403-XCI (1 segment, len: 0) (385.700 page 385) execute SEGMENT 1 (2 parts, len: 256) session_sqlmode, user_cmd parsid PART (1 argument, size: 16296) mess_type: update buf(12): 0000003A 00004B01 4C000000 data PART (1 argument, size: 16264) buf(168): ' 24 ' ' 24 ' >b03eval zeroStartKey Stop Key(1023): FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF b03eval root 44819; e_ok PageCnt 1; AllPageCnt 1 >b03eval zeroStartKey Stop Key(1023): FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF b03eval root 59709; e_ok PageCnt 1; AllPageCnt 1 >b03eval StartKey(3): ' 24' Stop Key(3): ' 24' b03eval root 59709; e_ok PageCnt 1; AllPageCnt 1 >b03eval zeroStartKey Stop Key(1023): FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF b03eval root 74597; e_ok PageCnt 1; AllPageCnt 1 >b03eval zeroStartKey Stop Key(1023): FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF b03eval root 89483; e_ok PageCnt 1; AllPageCnt 1 >b03eval zeroStartKey Stop Key(1023): FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF b03eval root 104371; e_ok PageCnt 1; AllPageCnt 1 >b03eval zeroStartKey Stop Key(1023): FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF b03eval root 155; e_ok PageCnt 1; AllPageCnt 1 >b03eval zeroStartKey Stop Key(1023): FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF b03eval root 15045; e_ok PageCnt 1; AllPageCnt 1 >KB05 id1173/-10238 update qual TABID 0-02A0 zerokey >b02first_ql zerokey b02first_ql root 44819; e_ok >b02repl key(3): 00C235 b02repl root 44819; e_ok >b02next_qual key(3): 00C235 b02next_qual root 44819; *** no_next_record *** KB05 id1173/-10238 return_result RECEIVE: ascii, full_swap, 70403-XCI (1 segment, len: 64) (385.2992 page 385) ok / RETURN SEGMENT 1 (1 part, len: 64) update_fc, sqlstate: '00000' external WARNING 0: warning_exists external WARNING 8: table_scan resultcount PART (1 argument, size: 35752) result_count: 1 >b01destroy tfnTemp; fileTfnNo = 0; ttfnTrigger session: 58; fid = 0 fn: 1A00 00000000003A1B20 000000000000 b01destroy root 2431; e_ok RECEIVE: ascii, normal_swap, 70400-ODB (1 segment, len: 64) (385.3210 page 385) ok / RETURN SEGMENT 1 (1 part, len: 64) update_fc, sqlstate: '00000' resultcount PART (1 argument, size: 35784) result_count: 1 REQUEST: ascii, normal_swap, 70400-ODB (1 segment, len: 96) (385.3354 page 385) dbs SEGMENT 1 (2 parts, len: 96) session_sqlmode, user_cmd with_info command PART (1 argument, size: 32248) buf(6): 'COMMIT' resulttablename PART (1 argument, size: 32224) buf(15): 'JDBC_CURSOR_355' >KB05 id1173 commit ===== T2 ===== nil.idnil ============================385.3586 page 385 _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
