table level lock
An ODBC application on IDS 7.31 intermittently failed with -244/ISAM -107 (record is locked), and onstat -k appeared to show an exclusive table-level lock whose owner showed as 0, so the poster couldn't identify a session to kill. Respondents explained these are normally brief locks held by other sessions, and advised issuing SET LOCK MODE TO WAIT n (e.g. 5-30 seconds) so the app waits instead of erroring; also suggested row-level rather than page-level locking, and checking that updates use an index rather than a sequential scan. If a lock really persisted, the holding session could be traced via the lock address in syslocks and killed with onmode -z. The poster concluded that adding a short lock wait in the application was the best fix.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
We recently have some issue with the table level lock.
We have application connect to database using ODBC to update the some
tables.
Sometimes the application will report such error.
244 : Could not do a physical-order read to fetch next row.
107 : ISAM error: record is locked.
> onstat -k
IBM Informix Dynamic Server Version 7.31.UD10 -- On-Line -- Up
00:04:41 -- 1905888 Kbytes
Locks
address wtlist owner lklist type
tblsnum rowid key#/bsiz
3543e688 0 0 0 HDR+IX
400052 0 0
3543e6c0 0 0 3543e688 HDR+X
400052 101 0
368455c8 0 90064468 0 HDR+S
100002 203 0
39053448 0 90064e70 0 S
100002 203 0
4 active, 3000000 total, 262144 hash buckets
Lock check shows there is exclusive table level lock placed on that
particular table. Since the owner information only shows "0". I am not
able to find corresponds session. How can I release the lock? "unlock
table" would not work.
Thanks,
Denny
Guo, Denny wrote:
> Hi,
>
You are not hitting a table level lock (or at least not necessarily),
what you are hitting is some momentary lock held by another session.
The locks are likely short lived. You need to have this ODBC session
run: SET LOCK MODE TO WAIT <nseconds>; with nseconds replaced by a small
but reasonable value like 5, 10, or 30 seconds. Then the session will
wait that long for momentary locks to be released (and will likely
continue on fine) before returning an error. If locks are indeed being
held for long periods by poorly behaved applications, you will get a
different SQL error indicating a Lock timeout with the appropriate ISAM
error indicating what the lock was.
Art S. Kagel
Oninit
> We recently have some issue with the table level lock.
> We have application connect to database using ODBC to update the some
> tables.
> Sometimes the application will report such error.
>
> 244 : Could not do a physical-order read to fetch next row.
> 107 : ISAM error: record is locked.
>
>
>> onstat -k>>
>
> IBM Informix Dynamic Server Version 7.31.UD10 -- On-Line -- Up
> 00:04:41 -- 19> 05888 Kbytes
>
> Locks
> address wtlist owner lklist type
> tblsnum rowid key#/bsiz
> 3543e688 0 0 0 HDR+IX
> 400052 0 0
> 3543e6c0 0 0 3543e688 HDR+X
> 400052 101 0
> 368455c8 0 90064468 0 HDR+S
> 100002 203 0
> 39053448 0 90064e70 0 S
> 100002 203 0
> 4 active, 3000000 total, 262144 hash buckets
>
> Lock check shows there is exclusive table level lock placed on that
> particular table. Since the owner information only shows "0". I am not
> able to find corresponds session. How can I release the lock? "unlock
> table" would not work.
>
> Thanks,
> Denny
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
Thanks Art,
Say the lock been hold by poorly behaved applications for a long period,
can we do something on database side in case we can not restart the
application?
Say issue some command to rollback or commit the transaction. The key
problem is the owner information is not available.
Denny
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art S. Kagel (Oninit)
Sent: Wednesday, April 02, 2008 12:19 PM
To: ids@iiug.org
Subject: Re: table level lock [11768]
Guo, Denny wrote:
> Hi,
>
You are not hitting a table level lock (or at least not necessarily),
what you are hitting is some momentary lock held by another session.
The locks are likely short lived. You need to have this ODBC session
run: SET LOCK MODE TO WAIT <nseconds>; with nseconds replaced by a small
but reasonable value like 5, 10, or 30 seconds. Then the session will
wait that long for momentary locks to be released (and will likely
continue on fine) before returning an error. If locks are indeed being
held for long periods by poorly behaved applications, you will get a
different SQL error indicating a Lock timeout with the appropriate ISAM
error indicating what the lock was.
Art S. Kagel
Oninit
> We recently have some issue with the table level lock.
> We have application connect to database using ODBC to update the some
> tables.
> Sometimes the application will report such error.
>
> 244 : Could not do a physical-order read to fetch next row.
> 107 : ISAM error: record is locked.
>
>
>> onstat -k>>
>
> IBM Informix Dynamic Server Version 7.31.UD10 -- On-Line -- Up
> 00:04:41 -- 19> 05888 Kbytes
>
> Locks
> address wtlist owner lklist type
> tblsnum rowid key#/bsiz
> 3543e688 0 0 0 HDR+IX
> 400052 0 0
> 3543e6c0 0 0 3543e688 HDR+X
> 400052 101 0
> 368455c8 0 90064468 0 HDR+S
> 100002 203 0
> 39053448 0 90064e70 0 S
> 100002 203 0
> 4 active, 3000000 total, 262144 hash buckets
>
> Lock check shows there is exclusive table level lock placed on that
> particular table. Since the owner information only shows "0". I am not
> able to find corresponds session. How can I release the lock? "unlock
> table" would not work.
>
> Thanks,
> Denny
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
Guo, Denny wrote:
> Thanks Art,
>
> Say the lock been hold by poorly behaved applications for a long period,
> can we do something on database side in case we can not restart the
> application?
> Say issue some command to rollback or commit the transaction. The key
> problem is the owner information is not available.
>
Locks do not survive the session that creates them for more than a
relatively short time. Only if the engine threads processing a query
for a session are in critical sections will they continue once the
client exited or has otherwise disconnected. Once the thread exits from
the critical code section it will discover that the client session is
gone an rollback any work releasing locks.
So, you should be able to see the lock in onstat -k and in the
sysmaster:syslocks table and relate the lock back to a session via the
'address' holding the lock. Once you have the session id you can use
onmode -z to kill the session which will release all of its resources.The client application would receive a -213 error at that point. If
it's local using a shared memory connection it may received a SIGSEGV or
SIGILL if it tries to access the connection memory in low level Informix
library code. Either way it's either going to crash or process an
irrecoverable error and have to exit.
Art S. Kagel
Oninit
> Denny
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art S. Kagel (Oninit)
> Sent: Wednesday, April 02, 2008 12:19 PM
> To: ids@iiug.org
> Subject: Re: table level lock [11768]
>
> Guo, Denny wrote:
>
>> Hi,
>>
>>
>
> You are not hitting a table level lock (or at least not necessarily),
> what you are hitting is some momentary lock held by another session.
> The locks are likely short lived. You need to have this ODBC session
> run: SET LOCK MODE TO WAIT <nseconds>; with nseconds replaced by a small
>
> but reasonable value like 5, 10, or 30 seconds. Then the session will
> wait that long for momentary locks to be released (and will likely
> continue on fine) before returning an error. If locks are indeed being
> held for long periods by poorly behaved applications, you will get a
> different SQL error indicating a Lock timeout with the appropriate ISAM
> error indicating what the lock was.
>
> Art S. Kagel
> Oninit
>
>
>> We recently have some issue with the table level lock.
>> We have application connect to database using ODBC to update the some
>> tables.
>> Sometimes the application will report such error.
>>
>> 244 : Could not do a physical-order read to fetch next row.
>> 107 : ISAM error: record is locked.
>>
>>
>>
>>> onstat -k>>>
>>>
>> IBM Informix Dynamic Server Version 7.31.UD10 -- On-Line -- Up
>> 00:04:41 -- 19>> 05888 Kbytes
>>
>> Locks
>> address wtlist owner lklist type
>> tblsnum rowid key#/bsiz
>> 3543e688 0 0 0 HDR+IX
>> 400052 0 0
>> 3543e6c0 0 0 3543e688 HDR+X
>> 400052 101 0
>> 368455c8 0 90064468 0 HDR+S
>> 100002 203 0
>> 39053448 0 90064e70 0 S
>> 100002 203 0
>> 4 active, 3000000 total, 262144 hash buckets
>>
>> Lock check shows there is exclusive table level lock placed on that
>> particular table. Since the owner information only shows "0". I am not
>>
>
>
>> able to find corresponds session. How can I release the lock? "unlock
>> table" would not work.
>>
>> Thanks,
>> Denny
>>
>>
>>
>>
> ************************************************************************
> *******
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>> See you at the IIUG Informix 2008 Conference
>> The Power Conference for Informix Professionals
>> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
>> http://www.iiug.org/conf
>> Registration Now Open!!
>>
>>
>>
>>
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
Make sure you are using row level locking, and not page level.
Aside from rhat, it is best to put a small wait time in the applications,
since locks are
usually held for a brief period, unless you have long running transactions.
"Guo, Denny" <DGuo@livingstonintl.com> wrote:
Hi,
We recently have some issue with the table level lock.
We have application connect to database using ODBC to update the some
tables.
Sometimes the application will report such error.
244 : Could not do a physical-order read to fetch next row.
107 : ISAM error: record is locked.
> onstat -k
IBM Informix Dynamic Server Version 7.31.UD10 -- On-Line -- Up
00:04:41 -- 1905888 Kbytes
Locks
address wtlist owner lklist type
tblsnum rowid key#/bsiz
3543e688 0 0 0 HDR+IX
400052 0 0
3543e6c0 0 0 3543e688 HDR+X
400052 101 0
368455c8 0 90064468 0 HDR+S
100002 203 0
39053448 0 90064e70 0 S
100002 203 0
4 active, 3000000 total, 262144 hash buckets
Lock check shows there is exclusive table level lock placed on that
particular table. Since the owner information only shows "0". I am not
able to find corresponds session. How can I release the lock? "unlock
table" would not work.
Thanks,
Denny
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
---------------------------------
You rock. That's why Blockbuster's offering you one month of Blockbuster Total
Access, No Cost.
In addition to Row Level Locking,
make sure your update statement uses proper index from the table.
Check WHERE clause of the update statement. To check whether it's using
index or not, run "set explain on" before your SQL statement.
if it's not using any of available index on table, it will go for the
sequential scan
and end up with the mentioned error if another process is updating another
record
in the table......
> To: ids@iiug.org> From: walterlowich@yahoo.com> Subject: Re: table level
lock [11773]> Date: Wed, 2 Apr 2008 22:49:36 -0400> > Make sure you are using
row level locking, and not page level. > Aside from rhat, it is best to put a
small wait time in the applications, > since locks are > usually held for a
brief period, unless you have long running transactions. > > "Guo, Denny"
<DGuo@livingstonintl.com> wrote: > Hi, > > We recently have some issue with
the table level lock. > We have application connect to database using ODBC to
update the some > tables. > Sometimes the application will report such error.
> > 244 : Could not do a physical-order read to fetch next row. > 107 : ISAM
error: record is locked. > > > onstat -k > > IBM Informix Dynamic Server
Version 7.31.UD10 -- On-Line -- Up > 00:04:41 -- 19 > 05888 Kbytes > > Locks >
address wtlist owner lklist type > tblsnum rowid key#/bsiz > 3543e688 0 0 0
HDR+IX > 400052 0 0 > 3543e6c0 0 0 3543e688 HDR+X > 400052 101 0 > 368455c8 0
90064468 0 HDR+S > 100002 203 0 > 39053448 0 90064e70 0 S > 100002 203 0 > 4
active, 3000000 total, 262144 hash buckets > > Lock check shows there is
exclusive table level lock placed on that > particular table. Since the owner
information only shows "0". I am not > able to find corresponds session. How
can I release the lock? "unlock > table" would not work. > > Thanks, > Denny >
> >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. > > See
you at the IIUG Informix 2008 Conference > The Power Conference for Informix
Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City),
Kansas > http://www.iiug.org/conf > Registration Now Open!! > >
--------------------------------- > You rock. That's why Blockbuster's
offering you one month of Blockbuster Total > Access, No Cost. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. > > See
you at the IIUG Informix 2008 Conference> The Power Conference for Informix
Professionals> April 27 - 30, 2008 Marriott Overland Park (Kansas City),
Kansas> http://www.iiug.org/conf>; Registration Now Open!!
_________________________________________________________________
More immediate than e-mail? Get instant access with Windows Live Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_ins
tantaccess_042008
Thanks all for the reply.
This table only has one entry. So we do not create index for this table.
The best solution is to have application to wait lock for few seconds.
Denny
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
dharmendra sharma
Sent: Wednesday, April 02, 2008 11:19 PM
To: ids@iiug.org
Subject: RE: table level lock [11775]
In addition to Row Level Locking,
make sure your update statement uses proper index from the table.
Check WHERE clause of the update statement. To check whether it's using
index or not, run "set explain on" before your SQL statement.
if it's not using any of available index on table, it will go for the
sequential scan
and end up with the mentioned error if another process is updating
another
record
in the table......
> To: ids@iiug.org> From: walterlowich@yahoo.com> Subject: Re: table
level
lock [11773]> Date: Wed, 2 Apr 2008 22:49:36 -0400> > Make sure you are
using
row level locking, and not page level. > Aside from rhat, it is best to
put a
small wait time in the applications, > since locks are > usually held
for a
brief period, unless you have long running transactions. > > "Guo,
Denny"
<DGuo@livingstonintl.com> wrote: > Hi, > > We recently have some issue
with
the table level lock. > We have application connect to database using
ODBC to
update the some > tables. > Sometimes the application will report such
error.
> > 244 : Could not do a physical-order read to fetch next row. > 107 :
ISAM
error: record is locked. > > > onstat -k > > IBM Informix Dynamic Server
Version 7.31.UD10 -- On-Line -- Up > 00:04:41 -- 19 > 05888 Kbytes > >
Locks >
address wtlist owner lklist type > tblsnum rowid key#/bsiz > 3543e688 0
0 0
HDR+IX > 400052 0 0 > 3543e6c0 0 0 3543e688 HDR+X > 400052 101 0 >
368455c8 0
90064468 0 HDR+S > 100002 203 0 > 39053448 0 90064e70 0 S > 100002 203 0
> 4
active, 3000000 total, 262144 hash buckets > > Lock check shows there is
exclusive table level lock placed on that > particular table. Since the
owner
information only shows "0". I am not > able to find corresponds session.
How
can I release the lock? "unlock > table" would not work. > > Thanks, >
Denny >
> >
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum. >
> See
you at the IIUG Informix 2008 Conference > The Power Conference for
Informix
Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas
City),
Kansas > http://www.iiug.org/conf > Registration Now Open!! > >
--------------------------------- > You rock. That's why Blockbuster's
offering you one month of Blockbuster Total > Access, No Cost. > > >
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum. >
> See
you at the IIUG Informix 2008 Conference> The Power Conference for
Informix
Professionals> April 27 - 30, 2008 Marriott Overland Park (Kansas City),
Kansas> http://www.iiug.org/conf>; Registration Now Open!!
_________________________________________________________________
More immediate than e-mail? Get instant access with Windows Live
Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Ref
resh_instantaccess_042008
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
Related threads
- record locked
- who locks a record?
- Regarding Non-Default Page Sizes
- Don't Understand Table's Space Requirement