Locked table problem

5. RE: Locked table problem

Like

Sergio Peres

Posted 03/15/20 04:14 PM

Thanks for reply Art,

I had already thought about this solution, but won't that make the process unreliable?

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

Original Message

6. RE: Locked table problem

Like

Art Kagel

IBM Champion

Posted 03/15/20 04:39 PM

How so? In what way unreliable? You indicated that apps are dyingd leaving locked rows.

First, that indicates that the application may be locking rows too soon, probably at fetch time rather than at the time data is modified. That's the first problem and can only be fixed by rewriting the apps to properly implement optimistic locking/transaction protocol.

Second, if multiple applications are being affected by one transaction's locks on a single table that may mean that a) the table has its lock mode set to page rather than row, or b) the query is not using an index to access the data and so multiple rows are being examined and the current isolation level is locking all visited rows. To resolve a) change the lock mode of the table. If b) use a different isolation level that only locks specific rows on update (committed read).

Third, if the apps are exiting without rolling back open transactions and the engine is not recognizing that the client app is gone, then the problem is the one I mentioned before, namely that the OS keepalive is set to the default (two hours on most systems) and needs to be shortened to something reasonable like 30 seconds. That way the OS will shutdown the network connection to the database soon after the app dies.causing the engine to rollback the open transaction and releasing the locks.

Art

Art S. Kagel, President and Principal Consultant

ASK Database Management

www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

------Original Message------

Thanks for reply Art,

I had already thought about this solution, but won't that make the process unreliable?

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

#Informix

7. RE: Locked table problem

Like

Sergio Peres

Posted 03/16/20 11:21 AM

Thanks for reply Art,

I am using one simple script to read locks over syslocks and syssessions:


select trim(s.hostname)||":"||s.sid||":"

||decode (trim(l.type),
"B", "'B-Byte_lock'",
"IS","IS-Intent_shared_lock",
"S","S-Shared_lock",
"XS","XS-Shared_key_value_held_by_a_repeatable_reader",
"U","U-Update_lock",
"IX",'IX-Intent_exclusive',
"SIX","SIX-Shared_intent_exclusive_lock",
"X","X-Exclusive_lock",

"XR","XR-Exclusive_key_value_held_by_a_repeatable_reader")||":"||trim(l.dbsname)||":"||trim(l.tabname)||":"||hex(l.rowidlk)

from sysmaster:syslocks l, sysmaster:syssessions s where s.sid = l.owner and dbsname <> 'sysmaster' order by l.type;

there is any other way/script to detect whats is locked and who is waiting?

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

Original Message

Original Message:
Sent: Sun March 15, 2020 04:38 PM
From: Art Kagel
Subject: Locked table problem

How so? In what way unreliable? You indicated that apps are dyingd leaving locked rows.

First, that indicates that the application may be locking rows too soon, probably at fetch time rather than at the time data is modified. That's the first problem and can only be fixed by rewriting the apps to properly implement optimistic locking/transaction protocol.

Second, if multiple applications are being affected by one transaction's locks on a single table that may mean that a) the table has its lock mode set to page rather than row, or b) the query is not using an index to access the data and so multiple rows are being examined and the current isolation level is locking all visited rows. To resolve a) change the lock mode of the table. If b) use a different isolation level that only locks specific rows on update (committed read).

Third, if the apps are exiting without rolling back open transactions and the engine is not recognizing that the client app is gone, then the problem is the one I mentioned before, namely that the OS keepalive is set to the default (two hours on most systems) and needs to be shortened to something reasonable like 30 seconds. That way the OS will shutdown the network connection to the database soon after the app dies.causing the engine to rollback the open transaction and releasing the locks.

Art

Art S. Kagel, President and Principal Consultant

ASK Database Management

www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Original Message------

Thanks for reply Art,

I had already thought about this solution, but won't that make the process unreliable?

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

#Informix

8. RE: Locked table problem

Like

SangGyu Jeong

Posted 03/16/20 11:55 AM

Sergio:
There is a good post from Doug showing the information of the session owning or waiting on the lock.
https://www.oninitgroup.com/listing-informix-locks

[informix@db2 ids1410fc3]$ echo "select * from v_locks" | dbaccess testdb

Database selected.



    session     process connected   host         user         database     table                          lock      number duration

        198       29039     0:11:21 db2          skjeong      stores_demo  catalog                        RBYT          14     0:11:14
        198       29039     0:11:21 db2          skjeong      stores_demo  catalog                        RX           148     0:11:14

2 row(s) retrieved.



Database closed.

[informix@db2 ids1410fc3]$ echo "select * from v_waiters" | dbaccess testdb

Database selected.



    session     process connected   host         user         database          locker

        216        7489     0:06:24 db2          informix     stores_demo          198

1 row(s) retrieved.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Original Message

Original Message:
Sent: Mon March 16, 2020 11:21 AM
From: Sergio Peres
Subject: Locked table problem

Thanks for reply Art,

I am using one simple script to read locks over syslocks and syssessions:


select trim(s.hostname)||":"||s.sid||":"

||decode (trim(l.type),
"B", "'B-Byte_lock'",
"IS","IS-Intent_shared_lock",
"S","S-Shared_lock",
"XS","XS-Shared_key_value_held_by_a_repeatable_reader",
"U","U-Update_lock",
"IX",'IX-Intent_exclusive',
"SIX","SIX-Shared_intent_exclusive_lock",
"X","X-Exclusive_lock",

"XR","XR-Exclusive_key_value_held_by_a_repeatable_reader")||":"||trim(l.dbsname)||":"||trim(l.tabname)||":"||hex(l.rowidlk)

from sysmaster:syslocks l, sysmaster:syssessions s where s.sid = l.owner and dbsname <> 'sysmaster' order by l.type;

there is any other way/script to detect whats is locked and who is waiting?

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

Original Message:
Sent: Sun March 15, 2020 04:38 PM
From: Art Kagel
Subject: Locked table problem

How so? In what way unreliable? You indicated that apps are dyingd leaving locked rows.

First, that indicates that the application may be locking rows too soon, probably at fetch time rather than at the time data is modified. That's the first problem and can only be fixed by rewriting the apps to properly implement optimistic locking/transaction protocol.

Second, if multiple applications are being affected by one transaction's locks on a single table that may mean that a) the table has its lock mode set to page rather than row, or b) the query is not using an index to access the data and so multiple rows are being examined and the current isolation level is locking all visited rows. To resolve a) change the lock mode of the table. If b) use a different isolation level that only locks specific rows on update (committed read).

Third, if the apps are exiting without rolling back open transactions and the engine is not recognizing that the client app is gone, then the problem is the one I mentioned before, namely that the OS keepalive is set to the default (two hours on most systems) and needs to be shortened to something reasonable like 30 seconds. That way the OS will shutdown the network connection to the database soon after the app dies.causing the engine to rollback the open transaction and releasing the locks.

Art

Art S. Kagel, President and Principal Consultant

ASK Database Management

www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Original Message------

Thanks for reply Art,

I had already thought about this solution, but won't that make the process unreliable?

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

#Informix

Informix

Informix