Query to identify session holding lock@table
Poster couldn't ALTER TABLE because of error 242/106 "non-exclusive access" and wanted a query returning the sessions holding locks on a given table. Replies noted that exclusive access needs more than absence of locks: any session with the table merely open (e.g. a dirty-read/view select) blocks DDL, and syslocks won't show it. Suggested approaches: an IBM technote plus syslocks/syssessions queries and a shell script for lock owners; setting IFX_DIRTY_WAIT to block new readers and wait for existing ones; and finding the table's partnum and using 'onstat -g opn' to identify and kill sessions with the table open. Cesar added a trick of a dummy GRANT in a transaction to hold the queue. The poster never confirmed which worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear All,
I am trying to alter a table, but database is giving me the error of
non-exclusive access.
242: Could not open database table (informix.cust).
106: ISAM error: non-exclusive access.
I need to identify all those sessions who are either holding a shared or
exclusive lock on this particular table, so that I could kill those sessions
to release the lock and then could perform the alter.
Can someone share a query or procedure to which i passes the table-name and it
retrieves all those session-ids who are holding any kind of lock on that table?
It will be great help.
Thanks in advance for your anticipation.
Regards,
Khan
Check this IBM Technote:
http://www-01.ibm.com/support/docview.wss?uid=swg21226344
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Jun 7, 2013 at 1:52 PM, O KHAN <theultimateboy@hotmail.com> wrote:
> Dear All,
>
> I am trying to alter a table, but database is giving me the error of
> non-exclusive access.
>
> 242: Could not open database table (informix.cust).
> 106: ISAM error: non-exclusive access.>
> I need to identify all those sessions who are either holding a shared or
> exclusive lock on this particular table, so that I could kill those
> sessions
> to release the lock and then could perform the alter.
>
> Can someone share a query or procedure to which i passes the table-name
> and it
> retrieves all those session-ids who are holding any kind of lock on that
> table?
>
> It will be great help.
>
> Thanks in advance for your anticipation.
>
> Regards,
> Khan
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c26a30b7f35a04de96bff0
This works well for us ...:
#!/usr/bin/ksh
# who-lock.sh - Determine who has locked what rows in which tables
#
# Author: Jacob Salomon
# JakeSalomon@netscape.net
# Version: 2.0
# Change History:
# o Release 2.0: Added parameter handling capability
# --------------------------------------------------------------------
# Parameter:
# - Names of tables in form:
# o database:table
# o database
#
# Outputs to stdout.
#
UNLFILE=/tmp/lock-list-$$.unl
OUTFILE=/tmp/lock-list-$$.out
parse_params() # Function to handle the parameters.
# Output: A WHERE clause
{
LTOP=$# # Parameter count
LC=1 # Initialize array & loop counter
while [ $LC -le $LTOP ]
do
if (echo $1 | grep -q :) # If it in the form of dbs:table
then
echo $1 | tr : " "| read DN TN # Separate dbs from table
WCL[$LC]='(dbsname = "'${DN}'" and tabname = "'${TN}'")'
else
WCL[$LC]='(dbsname = "'$1'")'
fi
shift
LC=$(( $LC + 1 ))
done
WH="where "
LC=1 # Restart the loop counter for new loop
while [ $LC -le $LTOP ]
do
if [ $LC -gt 1 ]
then # If more than 1 part to the WHERE clause
WH=${WH}" or"
fi
WH=${WH}" "${WCL[$LC]}
LC=$(( $LC + 1 ))
done
echo $WH # Spout to the caller
}
if [ $# -eq 0 ]
then
WHERE_CLAUSE=""
else
# Find out the nature of the parameter
WHERE_CLAUSE=`parse_params $*`
echo WHERE_CLAUSE: 1>&2
echo $WHERE_CLAUSE 1>&2
fi
dbaccess sysmaster - <<%%
set isolation to dirty read;
select dbsname, tabname,
hex(rowidlk) lock_row, type,
s.username locked_by,
l.owner, s.hostname, s.tty,
l.waiter,
w.username waiter_name
from sysmaster:syslocks l,
sysmaster:syssessions s,
outer sysmaster:syssessions w
where l.owner = s.sid
and l.waiter = w.sid
and not ( (l.dbsname = "sysmaster")
and (l.tabname = "sysdatabases"))
union
select d.name database_name, "(database)",
hex(rowidlk) lock_row, type,
s.username locked_by,
l.owner, s.hostname, s.tty,
l.waiter,
w.username waiter_name
from sysmaster:syslocks l,
sysmaster:sysdatabases d,
sysmaster:syssessions s,
outer sysmaster:syssessions w
where l.owner = s.sid
and l.waiter = w.sid
and ( (l.dbsname = "sysmaster")
and (l.tabname = "sysdatabases"))
and l.rowidlk = d.rowid
into temp t_locked_rows
;
unload to $UNLFILE
select * from t_locked_rows$WHERE_CLAUSE
order by locked_by, owner, dbsname, tabname, lock_row
;
%%
cat >$OUTFILE <<%%
Database|Table|Row-ID|LK-type|Locked-by|Session|AtHost|TTY|Waiter|Wait-Name|
--------|-----|------|-------|---------|-------|------|---|------|---------|
%%
cat $UNLFILE >>$OUTFILE
beautify-unl.sh $OUTFILE
rm $UNLFILE $OUTFILE
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of O KHAN
Sent: Friday, June 07, 2013 3:53 PM
To: ids@iiug.org
Subject: Query to identify session holding lock@table [30455]
Dear All,
I am trying to alter a table, but database is giving me the error of
non-exclusive access.
242: Could not open database table (informix.cust).
106: ISAM error: non-exclusive access.
I need to identify all those sessions who are either holding a shared or
exclusive lock on this particular table, so that I could kill those sessions
to release the lock and then could perform the alter.
Can someone share a query or procedure to which i passes the table-name and it
retrieves all those session-ids who are holding any kind of lock on that
table?
It will be great help.
Thanks in advance for your anticipation.
Regards,
Khan
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Art, Thanks for your reply, but it still does not solve my problem. This information i already have, I need to dig down one more step to be able to findout the table name, which is being shared locked any any session. Here is what is returned to me: l informix:84 has S lock on sysmaster:sysdatabases-0x00000205 But, i need to know that which table in the respective database is locked by which user. I am trying to make a query, in which if i pass the table-name , it returns me the session-ids who are holding any kind of lock on this table. thanks.
Just an FYI, exclusive access requires more than the absence of locks.=
It
also means that users do not have the table open. This error can occur
because other sessions are querying the same table with their isolation=
level set to "dirty read" (reading without locks).
There is an environment variable IFX_DIRTY_WAIT that is used to define =
to
the number of seconds a DDL staement will wait for existing dirty reade=
rs
to finish their access to the targeet table. When set, the variable al=
so
prevents new dirty readers from accessing the table.
export IFX_DIRTY_WAIT=3D300
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 06/07/2013 01:52:58 PM:
> From: "O KHAN" <theultimateboy@hotmail.com>
> To: ids@iiug.org,
> Date: 06/07/2013 01:54 PM
> Subject: Query to identify session holding lock@table [30455]
> Sent by: ids-bounces@iiug.org
>
> Dear All,
>
> I am trying to alter a table, but database is giving me the error of
> non-exclusive access.
>
> 242: Could not open database table (informix.cust).
> 106: ISAM error: non-exclusive access.>
> I need to identify all those sessions who are either holding a shared=
or
> exclusive lock on this particular table, so that I could kill those
sessions
> to release the lock and then could perform the alter.
>
> Can someone share a query or procedure to which i passes the table-
> name and it
> retrieves all those session-ids who are holding any kind of lock on t=
hat
> table?
>
> It will be great help.
>
> Thanks in advance for your anticipation.
>
> Regards,
> Khan
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=
Ahh, that's a database lock it is held by anyone connected to the database, but that should not be preventing you from altering any table in the database. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jun 7, 2013 at 2:19 PM, O KHAN <theultimateboy@hotmail.com> wrote: > Hello Art, > > Thanks for your reply, but it still does not solve my problem. > > This information i already have, I need to dig down one more step to be > able > to findout the table name, which is being shared locked any any session. > > Here is what is returned to me: > l informix:84 has S lock on sysmaster:sysdatabases-0x00000205 > > But, i need to know that which table in the respective database is locked > by > which user. > > I am trying to make a query, in which if i pass the table-name , it > returns me > the session-ids who are holding any kind of lock on this table. > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d1a4a964b7104de972262
I am still getting the same issue after setting the environemnt variable
(export IFX_DIRTY_WAIT=3) and restarting the database instance after that.
Let me further explain you the scenario:
I have a view (v_customer) on a table customer (stores database)
In one session using dbaccess, I simply executes this query:
select * from v_customer
In the other session, I am trying to perform this alter:
alter table customer add (name_1 varchar(10));
And I am getting the following error:
242: Could not open database table (informix.customer).
106: ISAM error: non-exclusive access.
Now, I need a query, through which I could be able to identify all those
session-ids who might be holding locks or have been using this (opened it or
querying it).
So that I could identify & kill those sessions to complete my DDL.
Let me know if i have explained the scenario, and if you have any further
query, then let me know.
Thanks.
Let me further explain you the scenario:
I have a view (v_customer) on a table customer (stores database)
create view v_customer as select * from customer;
In one session using dbaccess, I simply executes this query:
select * from v_customer
In the other session, I am trying to perform this alter:
alter table customer add (name_1 varchar(10));
And I am getting the following error:
242: Could not open database table (informix.customer).
106: ISAM error: non-exclusive access.
Now, I need a query, through which I could be able to identify all those
session-ids who might be holding locks or have been using this (opened it or
querying it).
So that I could identify & kill those sessions to complete my DDL.
Let me know if i have explained the scenario, and if you have any further
query, then let me know.
Thanks.
Hi O Khan ,
I already went through this problem in the past.
I solved with a trick I learn with Fernando Nunes.
Is use the IFX_DIRTY_WAIT + dummy grant !!!
Then check for what session you are waiting.
My own basic script is :
* set ifx_dirty_wait=600
open dbacces:
> set lock mode to wait;> begin work;
> grant select on <table> to <anyuser>;-- here, probably your session will stuck...
-- Then I start to kill all users what are listed into : onstat -g opn
-- where I look for the partnum of this table.
> alter table <table> .....
> commit;
Check here:
http://informix-technology.blogspot.com.br/2006/10/when-exclusive-is-not-really-
exclusive.html
Plus: You can have some difficult if your table have FKs.. if yes, have the
possibility to kill all access for this tables too and if your environment
have high concurrency you can get into -710 error/issues forever (bug what
will be solved only on next fix of 11.70, I hope)... if you get into this
problem (-710) then probably you will need to bounce the instance (but your
alter already be done)
Regards
Cesar
Em 07/06/2013 18:54, "O KHAN" <theultimateboy@hotmail.com> escreveu:
> Let me further explain you the scenario:
>
> I have a view (v_customer) on a table customer (stores database)
> create view v_customer as select * from customer;>
> In one session using dbaccess, I simply executes this query:
> select * from v_customer>
> In the other session, I am trying to perform this alter:
> alter table customer add (name_1 varchar(10));>
> And I am getting the following error:
> 242: Could not open database table (informix.customer).
> 106: ISAM error: non-exclusive access.>
> Now, I need a query, through which I could be able to identify all those
> session-ids who might be holding locks or have been using this (opened it
> or
> querying it).
>
> So that I could identify & kill those sessions to complete my DDL.
>
> Let me know if i have explained the scenario, and if you have any further
> query, then let me know.
>
> Thanks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b624dfe7cca5004de9914ff
If you set the export IFX_DIRTY_WAIT=3D3 then all users currently readi=
ng
data must complete and close the table in 3 seconds. This env will no=
t
kick anyone out, but will prevent new users from accessing the table. =
All
current users must exit on their own or be kicked out.
So in session one, your select * from v_customer must complete in 3 sec=
onds
and then close the select. If you started a third window and tried to =
run
another select * from v_customer this would not proceed.
If you want to find the users with open counts and no locks on the tabl=
e
then you must use onstat -g opn and look for the partnumber of the tabl=
e
you are trying to gain access to.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 06/07/2013 02:45:45 PM:
> From: "O KHAN" <theultimateboy@hotmail.com>
> To: ids@iiug.org,
> Date: 06/07/2013 03:19 PM
> Subject: Re: Query to identify session holding lock@table [30461]
> Sent by: ids-bounces@iiug.org
>
> I am still getting the same issue after setting the environemnt varia=
ble
> (export IFX_DIRTY_WAIT=3D3) and restarting the database instance afte=
r
that.
>
> Let me further explain you the scenario:
>
> I have a view (v_customer) on a table customer (stores database)
>
> In one session using dbaccess, I simply executes this query:
> select * from v_customer>
> In the other session, I am trying to perform this alter:
> alter table customer add (name_1 varchar(10));>
> And I am getting the following error:
> 242: Could not open database table (informix.customer).
> 106: ISAM error: non-exclusive access.>
> Now, I need a query, through which I could be able to identify all th=
ose
> session-ids who might be holding locks or have been using this (opene=
d it
or
> querying it).
>
> So that I could identify & kill those sessions to complete my DDL.
>
> Let me know if i have explained the scenario, and if you have any fur=
ther
> query, then let me know.
>
> Thanks.
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=
In case you haven't got a solution, then try this query below:
database sysmaster;{type of locks: IS,S,IX,SIX,X... X=exclusive }
select username, sid, dbsname, tabname, rowidlk
from syslocks, syssessions
where syssessions.sid = syslocks.owner andtabname = "customer" and
type = 'X' {or you can use --> type in ('IS', 'S', 'IX', 'SIX', 'X') }
order by tabname
Good luck.
Let's go Green
This email contains 100% recycled electrons.
________________________________
From: O KHAN <theultimateboy@hotmail.com>
To: ids@iiug.org
Sent: Friday, June 7, 2013 4:45 PM
Subject: Re: Query to identify session holding lock@table [30461]
I am still getting the same issue after setting the environemnt variable
(export IFX_DIRTY_WAIT=3) and restarting the database instance after that.
Let me further explain you the scenario:
I have a view (v_customer) on a table customer (stores database)
In one session using dbaccess, I simply executes this query:
select * from v_customer
In the other session, I am trying to perform this alter:
alter table customer add (name_1 varchar(10));
And I am getting the following error:
242: Could not open database table (informix.customer).
106: ISAM error: non-exclusive access.
Now, I need a query, through which I could be able to identify all those
session-ids who might be holding locks or have been using this (opened it or
querying it).
So that I could identify & kill those sessions to complete my DDL.
Let me know if i have explained the scenario, and if you have any further
query, then let me know.
Thanks.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Get the partnum for the table and run onstat -g opn.
On 07 June 2013 at 22:53 O KHAN <theultimateboy@hotmail.com> wrote:
> Let me further explain you the scenario:
>
> I have a view (v_customer) on a table customer (stores database)
> create view v_customer as select * from customer;>
> In one session using dbaccess, I simply executes this query:
> select * from v_customer>
> In the other session, I am trying to perform this alter:
> alter table customer add (name_1 varchar(10));>
> And I am getting the following error:
> 242: Could not open database table (informix.customer).
> 106: ISAM error: non-exclusive access.>
> Now, I need a query, through which I could be able to identify all those
> session-ids who might be holding locks or have been using this (opened it or
> querying it).
>
> So that I could identify & kill those sessions to complete my DDL.
>
> Let me know if i have explained the scenario, and if you have any further
> query, then let me know.
>
> Thanks.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g