Hi Informix community,

We are facing an issue to create trigger on a table , always get error saying not able to obtain exclusive lock. I did try to set lock mode to wait, it didn't help.

( db runs on 14.10.fc10w2 ,linux)

When I checked onstat -k and onstat -g ses 0 output, there is no lock to that table, no session accessing it at all.

This table does replicate data via cdr to another informix server though. So I think cdr puts locks and prevents schema change to this table.

Raised a case to ibm and got advice to use NONEXCLTRIG parameter. However it requires db restart to take effective.

It's an undocumented parameter that I didn't use before. I don't know if any potential impact if I set it to 1 in $ONCONFIG (then bounce db).

May I ask for advice on whether it's safe to use this parameter?

Is there any better option to create trigger in my case?

 Thank you!

Hi,

the error you're getting (106 - non-exclusive access) does not mean "not able to obtain exclusive lock", in fact it has nothing to do with locks. Instead it means this statement is not the only one currently having this table open, and there seem to be other queries or cursors currently having this table open.

In such situation, try running "onstat -g opn" and search its output for the respective table.  This onstat option gives you the open partitions per thread (identified by rstcb and tid at the start of each section), so for every match you'd take the respective rstcb address, minus the leading 0x, and search onstat -u for it to obtain the thread's session ID (sessid).  This way you'd learn which session(s) is/are in your way.  onstat -g stm [<sessid>]  would even tell you the sessions' statements which you could use to verify which ones exactly still have the table open.

onstat -g ses <sessid>  will tell you who (UID, process, host) owns the session in case you want to locate them.  You now can either shut those applications down or, a little more brute-force, terminate those sessions using onmode -z <sessid>.

As for NONEXCLTRIG, this is an old, yet hardly used (since undocumented and off by default) feature, so it's also a little hard to know its outcome, for instance whether those concurrent queries, when continuing to run, will notice and execute your new trigger.  On the other hand, after a restart, you might not even have this problem unless those concurrent applications immediately start accessing your table again.

HTH,
 Andreas

thank you so much!

We have this set at several sites and it works just fine:

https://www.ibm.com/support/pages/dropping-or-disabling-trigger-fails-error-242-106

See also:

https://www.oninitgroup.com/faq-items/informix-stored-procedure-to-list-open-tables