how can I find who block current transaction?

I have a transaction(insert into tab values (...）)。 mostly, this transaction so quickly in 30 ms. but the transaction used 10s occasionally。 how can I found which session or whic resource block this transaction ??? ------------------------------ David Cui Technical support gbase.cn ------------------------------ #Informix

David: It might be something as simple as the table needed to add another extent or even a new partition depending on the partitioning scheme. And if adding the extent or partition caused a chunk to extend or a dbspace to be expanded with an additional chunk (assuming your chunks are marked extendable and/or you are using storage pools). Perusing the message log from that time period will tell you that as a message will have been written there. As far as tracking down what session or other resource might have caused a slow-down or pause in the transaction, an onstat -u will show if the session is waiting for any conditions or mutexes. Running onstat -k will show locks your session is waiting for. An onstat -g ioh report will tell you if the disk chunk the transaction is writing to is suffering from slow response times. An "mpstat -P ALL 5 5" report will tell you if your CPU cores are running close to 100%. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

The table have 8 index。 I use sqltrace to find something 。 The select statement is slow, but the insert statement blocks the select statement after it. so I truncate this table and reload the data。 Seems to be back to normal 。 I use oncheck -pt dbname:tabname to find extend is changed. ------------------------------ Because the trading time is very short, and the trading has been happening at a high frequency。 So I wanted to find a tool that could report why a transaction was blocked as soon as it was blocked. ------------------------------ ZhiWei Cui GBASE ShiJiaZhuang

As "informix" or other DBSA user, enter the command "onmode -I errno" once you know the error number from a failure. When it happens again, it will save an "assert file" - in the location given in configuration parameter DUMPDIR - containing full details on the database engine state including locks: search for "waiters".You may need documentation on the "onstat" commands that create each section to interpret the data. One you have an "assert file", cancel the instruction with "onmode -I" on its own before you get too many. https://www.ibm.com/docs/en/informix-servers/14.10?topic=utility-onmode-i-control-diagnostics-collection ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Is/was it a large table, with possibly millions of rows? Are varchars in the schema? Is MAX_FILL_DATA_PAGES on? And what version are we talking about? And was the problem reproducible easily, so all or many inserts incurring the problem? Such occasional lengthy insert can well be a problem with finding free space for the new row, with bitmap scan returning pages 'with room', but actual pages then still not offering sufficient room for the new row. In worst case such scan would traverse the entire table, only to then decide it needs to append to the end, allocate a new extent, and from there on it simply would continue at that end ... until space got eaten up again. To mitigate such problems run with MAX_FILL_DATA_PAGES off - will fill pages to lower levels, so cause higher space consumption, but bitmap's 'with room' status would be more accurate, esp. with variable length rows create your tables with sufficiently large (first and next) extent size, or alter their next extent size to be big take advantage of 14.10.xC2's new Boosted Partition Free Space Caches (PFSC) created for addressing exactly this kind of problems (s.a. Table and fragment pfsc_boost admin api). HTH, Andreas ------------------------------ Andreas Legner ------------------------------

3Q very much。 I think what you said is very reasonable. I guess it may also be that some exceptions were encountered in the BTREE updating the index ------------------------------ ZhiWei Cui GBASE ShiJiaZhuang ------------------------------