[Info-Ingres] Kill processes that causes lock in the database

Wed Oct 24 18:30:37 CDT 2007

Paul

So you dont recommend ever using the ima to remove sessions? eg.
"execute procedure ima_remove_session"?
 from an OpenROAD app which does all the donkey work descibed below
using ima  and replaces ipm for most purposes.

Robert Allely
POAL NZ

________________________________

From: info-ingres-bounces at kettleriverconsulting.com
[mailto:info-ingres-bounces at kettleriverconsulting.com] On Behalf Of Paul
White
Sent: Thursday, 25 October 2007 11:58 a.m.
To: info-ingres at kettleriverconsulting.com
Subject: Re: [Info-Ingres] Kill processes that causes lock in the
database

Oscar writes...
>> ...Does anyone know how to kill a process thats causing a lock to the
Ingres database? ..

I wrote the following for my trainee DBAs. It is based on Ingres 2.6 on
Solaris. There is mention of an iisess 
command. This is my utility which filters the output of iimonitor - show
sessions formatted.
The same info can be gleaned by looking at vdba performance monitor
under windows.

Warning -  DBA stuff only  - (Ingres 2.6 on Solaris)

If you intend using these tools then please read the documentation and
practice in the play area.   
As a rule, only qualified DBAs should be running these commands.
Good info can be found here:

DBA.pdf 
Chapter 14 - Ingres Locking

SYSADM.pdf 
Chapter 7 - Basic Troubleshooting
Chapter 8 - Monitoring and Tracing tools

Unix locks procedure

a) Check the ingres error log.
If you dont know where this is by now then you should sack your self and
go back to ingres school.
The command is:
    more $II_SYSTEM/ingres/files/errlog.log

Check for record and table lock messages to get an idea of the type of
lock and how long it has been a problem.
If there are no lock messages then you are in the wrong environment or
it is a different problem.
Note the table and database.  The messages might look something like
this:

SHARK   ::[32942             , 0b302060]: Mon Mar 27 08:40:41 2006
E_CL1002_LK_TIMEOUT  Lock timed out
SHARK   ::[32942             , 0b302060]: Mon Mar 27 08:40:41 2006
E_DM9043_LOCK_TIMEOUT        Timeout occurred during TABLE lock request
on table ingres.sorder_detail in database fitzy with mode 6. Resource
held by session [2991 b4fd320] Mode 2.

SHARK   ::[32942             , 0b4fd320]: Mon Mar 27 08:42:16 2006
E_DM9044_ESCALATE_DEADLOCK   Deadlock encountered while escalating to
table level locking on table sorder_detail in database fitzy with mode
6.
SHARK   ::[32942             , 0b4fd320]: Mon Mar 27 08:42:16 2006
E_DM0042_DEADLOCK    Resource deadlock.
SHARK   ::[32942             , 0b4fd320]: Mon Mar 27 08:42:16 2006
E_QE002A_DEADLOCK    Deadlock detected.

The above errors show two separate lock problems.  If you do not
understand what these messages mean, then you need to go back to the
manuals.

b) Find the locks using ipm 
You must be logged in as ingres.  Either start a new telnet session or
if you are logged in as someone else then use:
    su - ingres

If you have a telnet window keyboard mapped correctly ...
The command is:
    ipm

But, if you are running from some other terminal emulator eg via citrix,
you may have to force vt100. 
Menu and field navigation is tricky in this case. Please practise this.
The command is:
    TERM=vt100 TERM_INGRES=vt100   ipm

If you cant run ipm or it hangs then there is some other problem - this
procedure will not help.

Select Lock Info
Select Locks

Look at the "Logical" column for anything <> 0
Refresh to see if the number changes. 

If there are no locked records then it is likely the problem is
intermittent, you may have to refresh several times. Or it could be some
other problem. Check errlog.log.

Select the row.
Note the lock id. eg 4a2d0077
Select Examine to display the lock information.
Select End to view another row

c) find the session using lockstat
In another window - again as ingres type:
    lockstat -user_lists |more

Search for the lock id (it will be in caps). Use forward slash. The
output will be something like this:

Id: 4A2D0077 Tran_id: 000043C346E7FB7F R_llb: 180B00D7 R_cnt: 0 S_llb:
00000000
S_cnt: 0 Wait: 00000000 Locks: (3,0/1000)
    PID:2991 SID:0B9D0100 Status:

    Id: 00000F4A Rsb: 00000D0A Gr: S   Req: S   State: GR PHYS(1)
KEY(CONTROL,DB
=41CE8184,TABLE=[597,0])
    Id: 000010F3 Rsb: 00000EB3 Gr: S   Req: S   State: GR PHYS(1)
KEY(CONTROL,DB
=41CE8184,TABLE=[15903,0])
    Id: 00000F63 Rsb: 00000D23 Gr: S   Req: S   State: GR PHYS(1)
KEY(CONTROL,DB
=41CE8184,TABLE=[13566,0])

Note the session id SID

d) find the session using iisess  

Type:
    iisess - c | more

Search (forward slash) to find the session id. The output will look
something like this:

0B9D0100:12563 coral       estreet  batch  CS_EVENT_WAIT (BIOR)
    Conn : cbryant:ASSET-515:ASSET-515:924:comp2::estreet

e) remove the session 
Before you get to this step you should visit the PC where the problem is
happening. 
Use proxy or take a walk. It is important to verify a process has
completed. For example,
killing an invoicing process can generate a lot of work doing the
cleanup because 
there are so many related processes tables involved.

If you are absolutely sure the session can be removed you can use ipm or
iimonitor. 
Never remove ingres sessions. Too risky. I use iimonitor because it is
quicker.

Run iisess to get the server process ID. It looks something like this:

======Server :  32942
07B74100:18 ingres      imadb    batch  CS_EVENT_WAIT (BIOR)
07B83B60:19 ingres      tmstreet batch  CS_EVENT_WAIT (BIOR)
07904100:20 ingres      mstreet  batch  CS_EVENT_WAIT (BIOR)
07913A20:21 ingres      estreet  batch  CS_EVENT_WAIT (BIOR)

Remove the sessions using iimonitor <serverid> like this:
(ingres 2.6)/home/ingres> iimonitor 32942
IIMONITOR> remove 0B9D0100
removed
IIMONITOR> quit

Paul

Peerless IT Pty Ltd
21 Evans Street,
Braybrook VIC 3019
Phone : 03 9214 7777
Mobile : 0414 681 799
Fax : 03 9318 5870
Internet : www.peerlessit.com.au
<file:///U:/Documents%20and%20Settings/pwhite/Application%20Data/Microso
ft/Signatures/www.peerlessit.com.au> 

***
This e-mail is privileged and confidential.  If you are not the intended recipient please delete the message and notify the sender at Ports of Auckland Limited.
***
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.kettleriverconsulting.com/mailman/private/info-ingres/attachments/20071025/100b5f02/attachment-0001.html 