wait on lock more than 30 seconds

If a session wait on lock more than 30 seconds then i want an alert.

On Sun, Dec 26, 2010 at 11:28 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > If a session wait on lock more than 30 seconds then i want an alert. > Assuming that you're requesting help, that's nearly impossible to do, although you can get close to it... Let's see... - If you don't want a session to wait for more than 30s than just don't use "SET LOCK MODE TO WAIT" and make sure all you "SET LOCK MODE TO WAIT n" use "n <= 30". - Assuming the above is not possible (you probably don't control your development), than it's possible to run a query and find all the sessions waiting for a session. You could automate that using a dbscheduler task for example. The issue here is the frequency that you use. dbscheduler allows a minute accuracy. And this may not be enough for your needs. Another issue is what to do with repeating alarms... You probably don't want to receive the same alarm every minute. I can get the query if that helps... (i'm growing my mailing list backlog....) Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd1fc10af1b4f04984fc4a3

kindly give me the query which can find all the sessions waiting for a session and also session which applied locks.

You can try this userlock.sh: (if you using Linux, Unix, Aix ...) #06. Lists all the users waiting for lock. #When some batch job runs, others have nothing but to wait for locks. From #onstat -u, letter "L" in first position of flag column indicates that the #session is waiting for lock and from corresponding address in onstat -k #output, you can get owner address of that lock and again from onstat -u, #finally, you can find out who's that culprit. I have tried to simplify ##this lengthy process in the following script. # #Run as - $ userlock.sh# # #---Cut here #--userlock.sh------------------------------------------------------------------ # # # Utility to Print sessions waiting for the lock # Written By - Prasad Mahale # Date - 05/02/1999 # Usage - userlock.sh # Friends, This utility may not be 100% correct. Feel free to modify this # according to your requirement. # If you find anything wrong in the logic of script or you have better version # of this utility or just even to send comments or suggestions, mail me at # pmahale@yahoo.com. # For sample output of this script, visit www.geocities.com/pmahale/inf/tools # ################################################################################ dbaccess sysmaster 2>/dev/null <<+ unload to userlock.out select a.us_name, a.us_sid, c.us_name, c.us_sid from sysuserthreads a, syslocktab b, sysuserthreads c where b.lk_addr = a.us_lkwait and b.lk_owner= c.us_txp; + echo "List of users waiting for Lock" echo "-------------------------------------------------------------------------" echo "USER SESSION USER SESSION" echo "-------------------------------------------------------------------------" cat userlock.out | awk -F "|" '{ printf("%-10s %10s waiting to release lock from %-10s %10s\\ ",$1,$2,$3,$4) }' rm userlock.out On 26.12.2010 15:13, KHURRAM SHAHZAD wrote: > kindly give me the query which can find all the sessions waiting for a session > and also session which applied locks. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Ivan Zaviç System& DB Administrator Mobile:+381-64-846-99-08 mail: ivan.zavis@mi-system.co.rs _________________________________________________________ M&I SYSTEMS CO. Cirila i Metodija 13/a, 21000 Novi Sad, Serbia Tel/Fax: +381-(0)21-68-98-600 Mail: info@mi-system.co.rs, URL: http://www.mi-system.co.rs

On Sun, Dec 26, 2010 at 2:13 PM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > kindly give me the query which can find all the sessions waiting for a > session > and also session which applied locks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > SELECT l.indx lock_id, f.txt[1,4] lock_type, l.rowidr rowidr, l.keynum keynum, EXTEND(DBINFO('utc_to_datetime', grtime), DAY TO SECOND) lock_establish, CURRENT YEAR TO SECOND - DBINFO('utc_to_datetime', grtime) lock_duration, a.dbsname dbsname, a.tabname tabname, t2.sid owner_sid, t2.username owner_user, h.hostname owner_hostname, h.pid owner_pid, t.sid waiter_sid, CURRENT YEAR TO SECOND - DBINFO('utc_to_datetime',g.start_wait) lock_wait, t.username wait_user, h2.hostname wait_hostname, h2.pid wait_pid FROM sysrstcb t, syslcktab l, sysrstcb t2, systxptab c, systabnames a, systcblst g, sysscblst h, sysscblst h2, flags_text f WHERE t.lkwait = l.address AND l.owner = c.address AND c.owner = t2.address AND l.partnum = a.partnum AND g.tid = t.tid AND h2.sid = t.sid AND h.sid = t2.sid AND f.tabname = "syslcktab" AND f.flags = l.type ORDER BY lock_duration desc, lock_id The output looks like: lock_id 11 lock_type X rowidr 256 keynum 0 lock_establish 28 00:07:04 lock_duration 0 00:52:28 dbsname stores tabname customer owner_sid 51 owner_user fnunes owner_hostname pacman.onlinedomus.net owner_pid 4546 waiter_sid 62 lock_wait 0 00:14:29 wait_user fnunes wait_hostname pacman.onlinedomus.net wait_pid 5139 Where...: - lock_id through tabname define the lock (it's type, rowid, keynum - if it's an index lock -, when it was established, how long it's there, the database name and the table name (or partition...) - owner* define the holder of the lock (session ID, username, hostname and pid) - lock_wait is how long the session has been waiting * - wait_* define the waiter (session ID, username, hostname and pid) *: Note that it's possible that lock_wait > lock_duration. How: 1- Session A establishes the lock 2- Session B tries to get hold of it 3- Session C tries to get hold of it 4- Session A releases the lock and Session B gets hold of it Session C wait time will keep increasing, but the lock establish will be the time of 4) Hope this helps. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd1d2d840f19504986e1100