Problem(Abstract)
Prior to Informix Cluster environments, locks were ignored on an HDR secondary. With updateable secondary servers in 11.50.xC8 and above, the lock request is sent to the Primary. This document will help you identify and track these locks from a primary server back to the secondary server it came from.
Symptom
Lock requests showing up on a primary server that do not map to sessions on the primary.
Resolving the problem
To associate an open transaction on the primary with a session on the SDS node, we first start with 'onstat -k' from the primary, we see some locks that are causing problems:
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
443139e8 0 4b7d0bf0 0 HDR+S 100002 205 0
44313b68 0 4b7d99c8 4451cae8 HDR+X 1001c8 100 0
4444c168 0 4b7d24f8 0 S 100002 204 0
4444c7e8 0 4b7d99c8 4451c868 HDR+S 100002 206 0
444b43e8 0 4b7d2d50 0 HDR+S 100002 204 0
4451c668 0 4b7d35a8 0 S 100002 204 0
4451c868 0 4b7d99c8 0 HDR+X 649 3 0
4451cae8 0 4b7d99c8 4444c7e8 HDR+IX 1001c8 0 0
8 active, 20000 total, 16384 hash buckets, 0 lock table overflows
The owner '4b7d99c8' has been identified as a lock on a table we need access to, or maybe while running an onstat –x, you see a open transaction that looks older than it should:
$ onstat –x
Transactions
est.
address flags userthread locks begin_logpos current logpos isol rb_time
4b811af0 A-B-- 4b7d99c8 4 2500:0x1014018 2578:0x1014080
...
To find the session, on the primary, use the owner from ‘onstat -k’ and run:
$ onstat -g ath|grep 4b7d99c8
tid tcb rstcb prty status vp-class name
1770 4d0e9568 4b7d99c8 1 sleeping secs: 1 5cpu proxyTh
...make note of the 'tid' value, then run:
$ onstat -g proxy all
IBM Informix Dynamic Server Version 11.50.F -- on-Line
Secondary Proxy Reference Transaction Hot Row
Node ID Count Count Total
sds2 1609 0 1 0
TID Flags Proxy Source Proxy Current sqlerrno iserrno
ID SessID TxnID Seq
1770 0x00008224 1609 22 3 2 0 0
...here we see the Proxy ID of 1609, and the Source SessID of 3, and the ProxyTxnID of 2.
On the SDS node, using the ‘Source SessId’ from the ‘onstat –g proxy all’ output, run the following:
$ onstat -g sql 22
IBM Informix Dynamic Server Version 11.50.F -- Updatable (SDS) -- Up 06:08:18 – 443096 Kbytes
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
22 - pwhite DR Not Wait 0 0 9.24 Off
Last parsed SQL statement :
insert into tab1 values(0,"Howdy") { commit work; }
You can also find similar information using the ‘ProxyID’ and the ‘ProxyTxnID’ from the ‘onstat –g proxy all’ output from the primary:
$ onstat -g proxy 1609 3
IBM Informix Dynamic Server Version 11.50.F -- Updatable (SDS) -- Up 06:07:33 -- 443096 Kbytes
Sequence Operation rowid Table sqlerrno
Number Type Name
1 *Insert 0 pwhite:informix.tab1 0
Conclusion:
Using this method, you can track down user 'pwhite' and find out why he is still causing Informix problems?
http://www-01.ibm.com/support/docview.wss?uid=swg21659774