dynamically allocated 1000000 locks

[t***@gmail.com]

question for the gurus

once we start seeing this lock message repeatedly in the online log, it means things are going south.

since this is all that gets posted into the log, how can I pin that directly back to the session that caused the increase in locks?

we will see a message later like this but that does not necessarily mean it caused anything but came in later asking for resources but they had already been used up prior

Lock table overflow - user id 21000, session id 388313
16:43:37 Who: Session(388684, ***@cre-ui-jboss1, -1, 7000000b0e58f48)


thanks,
tom

[t***@gmail.com]

version is 11.7 on AIX 6.1

[Jeff Filippi]

Here is something I did to automatically kill a session that was using to
many locks.
It would add a row to the ph_alert table showing what session caused the
issue.

In the sysadmin database add the following task:

unl_kill.unl
0|kill_session_highlocks|Terminate sessions with high number of
locks.|TASK|0|||sysadmin|kill_session_highlocks| 30 00:00:00|00:00:00|| 0
00:00:30|2012-09-12 16:00:00|0|0|t|t|t|t|t|t|t|4|SERVER|t|0|



load from unl_kill.unl insert into ph_task;
insert into ph_threshold values (0,'KILL HIGH
LOCKS','kill_session_highlocks',500000,'NUMERIC','Kill sessions with high
number of locks');


create function kill_session_highlocks(task_id INTEGER, task_seq INTEGER)
RETURNING INTEGER

define locks_allowed INTEGER;
define sys_hostname CHAR(256);
define sys_username CHAR(32);
define sys_sid INTEGER;
define rc INTEGER;


SELECT value::integer
INTO locks_allowed
FROM ph_threshold
WHERE name = "KILL HIGH LOCKS";

FOREACH highlocks_cur for
select admin("onmode","z",A.sid), A.username, A.sid, hostname
into rc, sys_username, sys_sid, sys_hostname
from sysmaster:sysrstcb A, sysmaster:systcblst B, sysmaster:sysscblst C
where A.tid = B.tid
and C.sid = A.sid
and lower(name) in ("sqlexec")
and nlocks > locks_allowed

IF rc > 0 THEN
INSERT INTO ph_alert (ID, alert_task_id, alert_task_seq, alert_type,
alert_color, alert_state,
alert_object_type, alert_object_name,
alert_message, alert_action)
VALUES (0, task_id, task_seq, "WARNING", "YELLOW", "NEW", "USER",
"TIMEOUT", "User
"||TRIM(sys_username)||"@"||TRIM(sys_hostname)||"sid("||sys_sid||")"||"
terminated due to large number of locks.",NULL);
END IF

end foreach;
RETURN 0;

end function;




Jeff

-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org]
On Behalf Of ***@gmail.com
Sent: Wednesday, March 26, 2014 10:27 AM
To: informix-***@iiug.org
Subject: Re: dynamically allocated 1000000 locks

version is 11.7 on AIX 6.1

[t***@gmail.com]

thanks much to all for the feedback as I am looking for not only while it happens but also after the fact

[Fernando Nunes]

Post by t***@gmail.com
version is 11.7 on AIX 6.1

It seems that the gateway between comp.databases.informix and an IIUG mailing list is not working properly.
On the mailing list, there was a reference about a non documented parameter that could be used to avoid this sort of situation (if you consider it acceptable of course).

I've expanded a bit that information, based on what John replied and a 2011 presentation where that functionality is referenced. See the details here:

http://informix-technology.blogspot.pt/2014/03/session-limit-locks-limite-de-locks-por.html

P.S.: I suppose this message will appear in he IIUG mailing list.

[jrenaut]

Post by t***@gmail.com
question for the gurus
once we start seeing this lock message repeatedly in the online log, it means things are going south.
since this is all that gets posted into the log, how can I pin that directly back to the session that caused the increase in locks?
we will see a message later like this but that does not necessarily mean it caused anything but came in later asking for resources but they had already been used up prior
Lock table overflow - user id 21000, session id 388313
thanks,
tom

I would think you should be able to just look at onstat -u (taken when the dynamically added locks message happens) and look at the "LOCKS" column and see which users/sessions were holding a large number of locks (would also suggest grabbing onstat -g ses 0 output at the same time so you can then hopefully see what the current statement is for the session/sessions using large amounts of locks).

Jacques Renaut
IBM Informix Advanced Support
APD Team