|
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) |
