Re: [Q] Record Locks - Continuing Saga... (RE: Art S. Kagel)

Your engine is blocked waiting for a checkpoint to complete (see the (CKPT REQ) in the header and the "Blocked:CKPT" on the second line!) and session 20 is waiting for the checkpoint to complete (see the 'C' in the flags column 1). You cannot kill that session until it tries to continue after the checkpoint. The real problem you have is why is the engine blocked!?!? Does onstat -l show all logs are full (or all but one if you have LBU_PRESERVE set)? What else might be happening? This blocked checkpoint is the trouble not the locks. They are just a symptom not a cause. It is possible that the checkpoint is waiting for another session to complete a critical section, what does onstat -s show? Any latches shown there? A user process that is hung in a critical section can hang the server though this is unlikely. You've been on a wild goose chase. Shift to tracking this one down. Art S. Kagel Steven Wong wrote: > > Hi All... This tread is kind of a continuation on an earlier posting about > how Informix locks records during inserts and deletes. I find I am running > into Informix lock-outs quite often. Art mentioned in an earlier posting > about using onmode -z <spid> and onstat -k to reset locks. It seems after > performing these tasks, the locks remain. See attached log below: > > D:\\INFORMIX\\bin>onmode -z 20 > > D:\\INFORMIX\\bin>onstat -k > > Informix Dynamic Server Version 7.30.TC3 -- On-Line (CKPT REQ) -- Up > 00:22:38 > -- 9536 Kbytes > Blocked:CKPT > > Locks > address wtlist owner lklist type tblsnum rowid key#/bsiz > c0200e4 0 c18f738 0 HDR+S 100002 204 0 > c020118 0 c18f738 c0200e4 HDR+IX 200048 0 0 > c02014c 0 c18f738 c020118 HDR+X 200048 100 0 > 3 active, 2000 total, 2048 hash buckets > > D:\\INFORMIX\\bin>onstat > Informix Dynamic Server Version 7.30.TC3 -- On-Line (CKPT REQ) -- Up > 00:23:04 > -- 9536 Kbytes > Blocked:CKPT > > Userthreads > address flags sessid user tty wait tout locks nreads > nwrites > c18e018 ---P--D 1 informix - 0 0 0 12 1 > c18e4b8 ---P--F 0 informix - 0 0 0 0 2 > c18e958 ---P--- 6 informix - 0 0 0 0 0 > c18edf8 ---P--B 7 informix - 0 0 0 0 0 > c18f738 C--P--- 20 informix DEV01 c01290c 0 3 259 88 > c18fbd8 ---P--D 10 informix - 0 0 0 0 0 > 6 active, 128 total, 18 maximum concurrent > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 400 437 9531 95.80 3 3 2 0.00 > > isamtot open start read write rewrite delete commit > rollbk > 8285 490 896 4475 0 1 0 0 0 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 7.90 1.62 0 2 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 56 0 3644 0 0 1 0 53 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 30 10 133 166 0 > > As you can see, I have 3 locks from DEV01 that will not go away! I have to > reboot the PC to get them to clear. How can these locks be cleared? And > is there a faster way of stopping ALL informix services then restarting > them? (like an off/on switch?) > > Windows NT 4.0 Server SP3 (Server) - Informix Dynamic Server Version > 7.30.TC3 > Windows NT WS 4.0 SP4 (Client) - Intersolv ODBC 3.01 Driver > > Thanks, > Steve

Thank you Art for your feedback... I think the logs are full, but I can't seem to clear them. Since this is only a test machine, I am really not concerned with the Log files. I have tried to set the ONCONFIG tape setting to #NUL but the system still seems to generate log files. How does one go about resetting or clearing the log files? I've attached the output to both "onstat -l" and "onstat -s". Thanks for your advice, Steve Art S. Kagel <kagel@bloomberg.net> wrote in message news:378BA0A4.2D45F593@bloomberg.net... > Your engine is blocked waiting for a checkpoint to complete (see the > (CKPT REQ) in the header and the "Blocked:CKPT" on the second line!) > and session 20 is waiting for the checkpoint to complete (see the 'C' > in the flags column 1). You cannot kill that session until it tries > to continue after the checkpoint. The real problem you have is why is > the engine blocked!?!? Does onstat -l show all logs are full (or all > but one if you have LBU_PRESERVE set)? What else might be happening? > > This blocked checkpoint is the trouble not the locks. They are just a > symptom not a cause. It is possible that the checkpoint is waiting for > another session to complete a critical section, what does onstat -s > show? Any latches shown there? A user process that is hung in a > critical section can hang the server though this is unlikely. > > You've been on a wild goose chase. Shift to tracking this one down. > > Art S. Kagel > > Steven Wong wrote: > > > > Hi All... This tread is kind of a continuation on an earlier posting about > > how Informix locks records during inserts and deletes. I find I am running > > into Informix lock-outs quite often. Art mentioned in an earlier posting > > about using onmode -z <spid> and onstat -k to reset locks. It seems after > > performing these tasks, the locks remain. See attached log below: > > > > D:\\INFORMIX\\bin>onmode -z 20 > > > > D:\\INFORMIX\\bin>onstat -k > > > > Informix Dynamic Server Version 7.30.TC3 -- On-Line (CKPT REQ) -- Up > > 00:22:38 > > -- 9536 Kbytes > > Blocked:CKPT > > > > Locks > > address wtlist owner lklist type tblsnum rowid key#/bsiz > > c0200e4 0 c18f738 0 HDR+S 100002 204 0 > > c020118 0 c18f738 c0200e4 HDR+IX 200048 0 0 > > c02014c 0 c18f738 c020118 HDR+X 200048 100 0 > > 3 active, 2000 total, 2048 hash buckets > > > > D:\\INFORMIX\\bin>onstat > > Informix Dynamic Server Version 7.30.TC3 -- On-Line (CKPT REQ) -- Up > > 00:23:04 > > -- 9536 Kbytes > > Blocked:CKPT > > > > Userthreads > > address flags sessid user tty wait tout locks nreads > > nwrites > > c18e018 ---P--D 1 informix - 0 0 0 12 1 > > c18e4b8 ---P--F 0 informix - 0 0 0 0 2 > > c18e958 ---P--- 6 informix - 0 0 0 0 0 > > c18edf8 ---P--B 7 informix - 0 0 0 0 0 > > c18f738 C--P--- 20 informix DEV01 c01290c 0 3 259 88 > > c18fbd8 ---P--D 10 informix - 0 0 0 0 0 > > 6 active, 128 total, 18 maximum concurrent > > > > Profile > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > > 400 437 9531 95.80 3 3 2 0.00 > > > > isamtot open start read write rewrite delete commit > > rollbk > > 8285 490 896 4475 0 1 0 0 0 > > > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > > 0 0 0 0 0 0 0 > > > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > > 0 0 0 7.90 1.62 0 2 > > > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > > 56 0 3644 0 0 1 0 53 > > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 30 10 133 166 0 > > > > As you can see, I have 3 locks from DEV01 that will not go away! I have to > > reboot the PC to get them to clear. How can these locks be cleared? And > > is there a faster way of stopping ALL informix services then restarting > > them? (like an off/on switch?) > > > > Windows NT 4.0 Server SP3 (Server) - Informix Dynamic Server Version > > 7.30.TC3 > > Windows NT WS 4.0 SP4 (Client) - Intersolv ODBC 3.01 Driver > > > > Thanks, > > Steve