Record Locks - Continuing Saga... (RE: Art S. Kagel)

Record Locks - Continuing Saga... (RE: Art S. Kagel)

Post by Steven Won » Sun, 31 Dec 1899 09:00:00



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

 
 
 

Record Locks - Continuing Saga... (RE: Art S. Kagel)

Post by Art S. Kage » Sun, 31 Dec 1899 09:00:00


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


> 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


 
 
 

Record Locks - Continuing Saga... (RE: Art S. Kagel)

Post by Steven Won » Sun, 31 Dec 1899 09:00:00


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



> 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


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

 
 
 

1. Mr Art Kagel

The IIUG would like to inform all of comp.database.Informix that Mr Art
Kagel and Mr Carlton Doe are the recipients of the IIUG Directors award for
excellence in their work in the Informix Community.

Here is the release

http://www.iiug.org/members/directors_award.html

Thank you Art and Carlton!!!

Stuart Litel
IIUG Board of Directors

2. JBuilder/SQLServer DateTime problem

3. About field...

4. Where is Art Kagel?

5. Q: Oracle8 for NT4 Alpha?

6. for Art S Kagel--iiug prog compilaton errors

7. VB doesn't recognize ODBC stuff.

8. Record locking saga

9. CREATE VIEW - saga continues ?

10. Major Bug in DTS - The Saga Continues

11. Table Full -- the saga continues

12. NOT NULL columns and BEFORE triggers......saga continues....