Informix - Problem description
Problem IT28305 | Status: Closed |
DEADLOCK (143 /622) ON SYSFRAGMENTS & SYSINDICES WHEN CONCURRENTTRUNCATE & SET INDEXES W/ WAIT & NO LOG | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
On a non-logged database, customer is running HPL to load some data into table A and another session is truntating table B. While HPL is setting indexes enabled on some timing occasions gets the deadlock and process is aborted. Both sessions have lock mode to wait. Since the deadlock is on sysfragments & sysindices hence is defect. 622: Error on locating constraint index (idx_xxx_table_01). 143: ISAM error: deadlock detected If error is trapped you'll see: 12:48:25 Stack for thread: 2330 sqlexec base: 0x000000004a583000 len: 102400 pc: 0x000000000141f989 tos: 0x000000004a596b40 state: running vp: 1 0x000000000141f989 (oninit) afstack 0x0000000001422961 (oninit) afhandler 0x0000000001423142 (oninit) affail_interface 0x0000000000e24aa7 (oninit) check_traperror 0x0000000000da6f27 (oninit) lkdeadlock 0x0000000000dae229 (oninit) lkmanagex 0x0000000000db2ad0 (oninit) lkrow 0x0000000000d3006c (oninit) rsread 0x00000000014ff777 (oninit) fmread 0x000000000072fa87 (oninit) lkidxrd 0x00000000009ab9ae (oninit) sm_updcat 0x00000000009c3da6 (oninit) setobjmode 0x00000000006eaf8e (oninit) aud_setobjmode 0x00000000005fd589 (oninit) excommand 0x0000000000a47d8c (oninit) sq_execute 0x0000000000b0b011 (oninit) sqmain 0x000000000154ca1b (oninit) spawn_thread 0x0000000001411450 (oninit) th_init_initgls 0x0000000001456d88 (oninit) startup ===========------------- - - - - - - /home/informix/prods/12.10/FC12/bin/onstat -u: IBM Informix Dynamic Server Version 12.10.FC12 -- On-Line -- Up 15 days 01:26:00 -- 3196552 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites 45201028 ---P--D 1 informix - 0 0 0 403 1451 45201918 ---P--F 0 informix - 0 0 0 0 816997 45202208 ---P--F 0 informix - 0 0 0 0 1025124 45202af8 ---P--- 9 informix - 0 0 0 0 0 452033e8 ---P--B 10 informix - 0 0 0 15689 0 45203cd8 Y--P--D 11 informix - 473e46b0 0 0 45050347 11 452045c8 ---P--D 12 informix - 0 0 0 4 9905 45204eb8 L-BPR-- 168 informix 3 441a6b30 -1 15 1843 2048 452057a8 ---P--D 16 informix - 0 0 0 0 0 45206098 *--BPR-- 170 informix 1 0 0 25 7416 1792 45206988 ---P--D 17 informix - 0 0 0 5 1 45207278 ---P--D 18 informix - 0 0 0 0 0 45207b68 ---P--D 19 informix - 0 0 0 0 0 45208458 ---P--- 31 informix - 0 0 1 10383 28391 45208d48 ---P--- 30 informix - 0 0 2 350 4335 45209638 ---P--- 29 informix - 0 0 1 11 0 45209f28 ---P--- 32 informix - 0 0 1 12886 21986 4520a818 Y--P--D 67 informix - 44116d50 0 0 0 0 18 active, 128 total, 34 maximum concurrent Offending session shows: IBM Informix Dynamic Server Version 12.10.FC12 -- On-Line -- Up 15 days 01:26:00 -- 3196552 Kbytes session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 170 informix - 1 29002 oc8630882863.ibm.com 1 155648 145816 off Program : /home/informix/prods/12.10/FC12/bin/dbaccess tid name rstcb flags curstk status 2330 sqlexec 45206098 --BPR-- 20496 running- Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 170 V 49c85040 151552 9064 164 12 170*O0 V 4a08b040 4096 768 1 1 name free used name free used overhead 0 6656 scb 0 144 opentable 0 11376 filetable 0 2400 ru 0 600 log 0 16536 temprec 0 22688 keys 0 832 ralloc 0 42408 gentcb 0 1760 ostcb 0 2992 sqscb 0 22648 hashfiletab 0 552 osenv 0 2960 sqtcb 0 8808 fragman 0 2312 sapi 0 144 sqscb info scb sqscb optofc pdqpriority optcompind directives 45b861c0 4a568028 0 0 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 170 SET OBJMODE usda CR Wait 0 0 9.24 Off Current SQL statement (128) : set indexes for xxx_table enabled Last parsed SQL statement : set indexes for xxx_table enabled onstat -k shows: /home/informix/prods/12.10/FC12/bin/onstat -k: IBM Informix Dynamic Server Version 12.10.FC12 -- On-Line -- Up 15 days 01:26:01 -- 3196552 Kbytes Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 441a4028 0 45209638 0 HDR+S 100002 204 0 441a42d0 0 45208d48 0 S 100002 204 0 441a4358 0 45208d48 441a42d0 HDR+S 100002 201 0 441a43e0 0 45208458 0 S 100002 204 0 441a4468 0 45209f28 0 S 100002 204 0 441a4d70 0 45204eb8 441b7f70 HDR+X 400044 a0c 0 441a5568 0 45206098 441bd250 HDR+X 400045 170a 0 441a6228 0 45206098 441ab9d0 HDR+X 40005c 301 0 441a6338 0 45206098 441a5568 HDR+X 40005c 304 0 441a6a20 0 45206098 441bd1c8 HDR+X 40005b 203 K- 1 I 441a6b30 45204eb8 45206098 441a86d0 HDR+X 40005c 303 0 441a73b0 0 45206098 441a6b30 HDR+X e00006 0 0 441a7d40 0 45206098 0 S 100002 209 0 441a86d0 0 45206098 441bc7b0 HDR+IX 40005c 0 0 441a8d30 0 45204eb8 441bd2d8 HDR+X 400085 0 0 441a9060 0 45204eb8 441b9a00 IX 40005c 0 0 441aa270 0 45206098 441aa8d0 HDR+X 40005b 201 0 I 441aa408 0 45206098 441b1ca0 HDR+IX 400045 0 0 441aa5a0 0 45206098 441a73b0 HDR+X 40005b 202 0 I 441aa8d0 0 45206098 441bb0d8 HDR+IX 40005b 0 0 441ab9d0 0 45206098 441bbea8 HDR+X 40005c 305 0 441ae010 0 45204eb8 441b5ce8 HDR+X 400046 2301 0 441af990 0 45206098 441bab00 HDR+X 400045 1708 0 441b1ca0 0 45206098 441be0a8 HDR+IX 400044 0 0 441b4720 0 45204eb8 441bd470 HDR+X 40005c 205 0 441b54f0 0 45204eb8 441b9a88 HDR+X 400046 2303 0 441b5688 0 45206098 441a6338 HDR+X e00007 0 0 441b5ce8 0 45204eb8 441a4d70 HDR+IX 400046 0 0 441b7f70 0 45204eb8 441a8d30 IX 400044 0 0 441b8548 0 45206098 441a7d40 HDR+X 400086 0 0 441b9a00 45206098 45204eb8 441b54f0 HDR+U 400046 2304 0 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of IDS prior to 12.10.xC13. * **************************************************************** * PROBLEM DESCRIPTION: * * Deadlock (-143 / -622) on sysfragments & sysindices * * concurrently when TRUNCATE & SET INDEXES FOR with lock mode * * WAIT & no log DB. * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 05.03.2019 26.09.2019 26.09.2019 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC13 | |
Problem solved according to the fixlist(s) of the following version(s) |