RE: Clean Reads/Database timing out
Larry found that a SELECT under COMMITTED READ isolation blocks and times out waiting on row locks held by a concurrent uncommitted INSERT, instead of returning just the committed rows immediately. John Miller (IBM) explained that the behaviour depends on ANSI vs non-ANSI logging databases, and that from IDS 11 the fix is COMMITTED READ LAST COMMITTED (settable in the app, sysdbopen, or onconfig), which reads through locks. Larry confirmed it worked on IDS 11; for IDS 10 Miller said there is no equivalent — only wait-forever lock mode, custom retry logic, or upgrading.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All,
I have the following situation for concurrent processes/transactions:
>> Tab1 (LOCK MODE ROW) has the following data:
>> ID name
>> 1 nam1
>> 2 nam2
>> 3 nam3
>> 4 nam4
>>
>> Transaction A:
>> SET LOCK MODE TO WAIT 120;
>> SET ISOLATION TO COMMITTED READ;>>
>> insert into tab1 values (5, 'name5');>> ....
>> ....
>>
>>
>> Transaction B:
>> SET LOCK MODE TO WAIT 12;
>> SET ISOLATION TO COMMITTED READ;>>
>> select * from tab1;>>
>> (this select retrieves the following and waits:
>> ID name
>> 1 nam1
>> 2 nam2
>> 3 nam3
>> 4 nam4
>>
What is happening is that Transaction B is timing out waiting for transaction
A locks during the insert. What we want it to do is to retrieve the
committed/clean data, ignore anything that transaction A is doing, and return
immediately. Is this not the way it is supposed to work? All of the
documentation that I have read says that transaction B should return
immediately with all of the clean data not locked.
Larry
Larry::
You failed to mention the type of database, ANSI or non-ansi logging
database
will make a big difference. It will change the default isolation level and
how lock
are handled.
If you are in version 11 or higher you should set the isolation level to
committed read last committed. There are several way to accomplish this,
in the application, in a global procedure (sysdbopen), or in the onconfig
file.
committed read last committed will always return the committed version of
the
data and will not wait on locks of other users who are deleting ,updating,
inserting data
but rather read through the lock to return the current committed version of
the data.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 05/12/2009 12:56:36 PM:
> [image removed]
>
> RE: Clean Reads/Database timing out [15718]
>
> LARRY SORENSEN
>
> to:
>
> ids
>
> 05/12/2009 12:57 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> All,
>
> I have the following situation for concurrent processes/transactions:
>
> >> Tab1 (LOCK MODE ROW) has the following data:
> >> ID name
> >> 1 nam1
> >> 2 nam2
> >> 3 nam3
> >> 4 nam4
> >>
> >> Transaction A:
> >> SET LOCK MODE TO WAIT 120;
> >> SET ISOLATION TO COMMITTED READ;> >>
> >> insert into tab1 values (5, 'name5');> >> ....
> >> ....
> >>
> >>
> >> Transaction B:
> >> SET LOCK MODE TO WAIT 12;
> >> SET ISOLATION TO COMMITTED READ;> >>
> >> select * from tab1;> >>
> >> (this select retrieves the following and waits:
> >> ID name
> >> 1 nam1
> >> 2 nam2
> >> 3 nam3
> >> 4 nam4
> >>
> What is happening is that Transaction B is timing out waiting for
transaction
> A locks during the insert. What we want it to do is to retrieve the
> committed/clean data, ignore anything that transaction A is doing, and
return
> immediately. Is this not the way it is supposed to work? All of the
> documentation that I have read says that transaction B should return
> immediately with all of the clean data not locked.
>
> Larry
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Is this just new to IDS 11?
> To: ids@iiug.org
> From: miller3@us.ibm.com
> Subject: RE: Clean Reads/Database timing out [15720]
> Date: Tue, 12 May 2009 16:43:59 -0400
>
> Larry::
>
> You failed to mention the type of database, ANSI or non-ansi logging
> database
> will make a big difference. It will change the default isolation level and
> how lock
> are handled.
>
> If you are in version 11 or higher you should set the isolation level to
> committed read last committed. There are several way to accomplish this,
> in the application, in a global procedure (sysdbopen), or in the onconfig
> file.
>
> committed read last committed will always return the committed version of
> the
> data and will not wait on locks of other users who are deleting ,updating,
> inserting data
> but rather read through the lock to return the current committed version of
> the data.
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 05/12/2009 12:56:36 PM:
>
> > [image removed]
> >
> > RE: Clean Reads/Database timing out [15718]
> >
> > LARRY SORENSEN
> >
> > to:
> >
> > ids
> >
> > 05/12/2009 12:57 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > All,
> >
> > I have the following situation for concurrent processes/transactions:
> >
> > >> Tab1 (LOCK MODE ROW) has the following data:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > >> Transaction A:
> > >> SET LOCK MODE TO WAIT 120;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> insert into tab1 values (5, 'name5');> > >> ....
> > >> ....
> > >>
> > >>
> > >> Transaction B:
> > >> SET LOCK MODE TO WAIT 12;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> select * from tab1;> > >>
> > >> (this select retrieves the following and waits:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > What is happening is that Transaction B is timing out waiting for
> transaction
> > A locks during the insert. What we want it to do is to retrieve the
> > committed/clean data, ignore anything that transaction A is doing, and
> return
> > immediately. Is this not the way it is supposed to work? All of the
> > documentation that I have read says that transaction B should return
> > immediately with all of the clean data not locked.
> >
> > Larry
> >
> >
> >
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The "last committed" option to committed read isolation level is new to=
version 11.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
=
From: "LARRY SORENSEN" <lsorensen25@msn.com> =
=
To: ids@iiug.org =
=
Date: 05/12/2009 02:05 PM =
=
Subject: RE: Clean Reads/Database timing out [15721] =
=
Sent by: ids-bounces@iiug.org =
=
Is this just new to IDS 11?
> To: ids@iiug.org
> From: miller3@us.ibm.com
> Subject: RE: Clean Reads/Database timing out [15720]
> Date: Tue, 12 May 2009 16:43:59 -0400
>
> Larry::
>
> You failed to mention the type of database, ANSI or non-ansi logging
> database
> will make a big difference. It will change the default isolation leve=
l
and
> how lock
> are handled.
>
> If you are in version 11 or higher you should set the isolation level=
to
> committed read last committed. There are several way to accomplish th=
is,
> in the application, in a global procedure (sysdbopen), or in the onco=
nfig
> file.
>
> committed read last committed will always return the committed versio=
n of
> the
> data and will not wait on locks of other users who are
deleting ,updating,
> inserting data
> but rather read through the lock to return the current committed vers=
ion
of
> the data.
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 05/12/2009 12:56:36 PM:
>
> > [image removed]
> >
> > RE: Clean Reads/Database timing out [15718]
> >
> > LARRY SORENSEN
> >
> > to:
> >
> > ids
> >
> > 05/12/2009 12:57 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > All,
> >
> > I have the following situation for concurrent processes/transaction=
s:
> >
> > >> Tab1 (LOCK MODE ROW) has the following data:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > >> Transaction A:
> > >> SET LOCK MODE TO WAIT 120;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> insert into tab1 values (5, 'name5');> > >> ....
> > >> ....
> > >>
> > >>
> > >> Transaction B:
> > >> SET LOCK MODE TO WAIT 12;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> select * from tab1;> > >>
> > >> (this select retrieves the following and waits:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > What is happening is that Transaction B is timing out waiting for
> transaction
> > A locks during the insert. What we want it to do is to retrieve the=
> > committed/clean data, ignore anything that transaction A is doing, =
and
> return
> > immediately. Is this not the way it is supposed to work? All of the=
> > documentation that I have read says that transaction B should retur=
n
> > immediately with all of the clean data not locked.
> >
> > Larry
> >
> >
> >
>
>
***********************************************************************=
********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
John,
The committed read last committed seems to be working for our IDS 11
databases. Can you suggestion anything to get around this for our non-ANSI IDS
10 databases without using DIRTY READ?
Larry
> To: ids@iiug.org
> From: miller3@us.ibm.com
> Subject: RE: Clean Reads/Database timing out [15720]
> Date: Tue, 12 May 2009 16:43:59 -0400
>
> Larry::
>
> You failed to mention the type of database, ANSI or non-ansi logging
> database
> will make a big difference. It will change the default isolation level and
> how lock
> are handled.
>
> If you are in version 11 or higher you should set the isolation level to
> committed read last committed. There are several way to accomplish this,
> in the application, in a global procedure (sysdbopen), or in the onconfig
> file.
>
> committed read last committed will always return the committed version of
> the
> data and will not wait on locks of other users who are deleting ,updating,
> inserting data
> but rather read through the lock to return the current committed version of
> the data.
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 05/12/2009 12:56:36 PM:
>
> > [image removed]
> >
> > RE: Clean Reads/Database timing out [15718]
> >
> > LARRY SORENSEN
> >
> > to:
> >
> > ids
> >
> > 05/12/2009 12:57 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > All,
> >
> > I have the following situation for concurrent processes/transactions:
> >
> > >> Tab1 (LOCK MODE ROW) has the following data:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > >> Transaction A:
> > >> SET LOCK MODE TO WAIT 120;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> insert into tab1 values (5, 'name5');> > >> ....
> > >> ....
> > >>
> > >>
> > >> Transaction B:
> > >> SET LOCK MODE TO WAIT 12;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> select * from tab1;> > >>
> > >> (this select retrieves the following and waits:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > What is happening is that Transaction B is timing out waiting for
> transaction
> > A locks during the insert. What we want it to do is to retrieve the
> > committed/clean data, ignore anything that transaction A is doing, and
> return
> > immediately. Is this not the way it is supposed to work? All of the
> > documentation that I have read says that transaction B should return
> > immediately with all of the clean data not locked.
> >
> > Larry
> >
> >
> >
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
John,
The COMMITTED READ LAST COMMITTED seems to be working for our IDS 11
databases. Can you suggestion anything to get around this for our non-ANSI IDS
10 databases without using DIRTY READ?
Larry
> To: ids@iiug.org
> From: miller3@us.ibm.com
> Subject: RE: Clean Reads/Database timing out [15720]
> Date: Tue, 12 May 2009 16:43:59 -0400
>
> Larry::
>
> You failed to mention the type of database, ANSI or non-ansi logging
> database
> will make a big difference. It will change the default isolation level and
> how lock
> are handled.
>
> If you are in version 11 or higher you should set the isolation level to
> committed read last committed. There are several way to accomplish this,
> in the application, in a global procedure (sysdbopen), or in the onconfig
> file.
>
> committed read last committed will always return the committed version of
> the
> data and will not wait on locks of other users who are deleting ,updating,
> inserting data
> but rather read through the lock to return the current committed version of
> the data.
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 05/12/2009 12:56:36 PM:
>
> > [image removed]
> >
> > RE: Clean Reads/Database timing out [15718]
> >
> > LARRY SORENSEN
> >
> > to:
> >
> > ids
> >
> > 05/12/2009 12:57 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > All,
> >
> > I have the following situation for concurrent processes/transactions:
> >
> > >> Tab1 (LOCK MODE ROW) has the following data:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > >> Transaction A:
> > >> SET LOCK MODE TO WAIT 120;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> insert into tab1 values (5, 'name5');> > >> ....
> > >> ....
> > >>
> > >>
> > >> Transaction B:
> > >> SET LOCK MODE TO WAIT 12;
> > >> SET ISOLATION TO COMMITTED READ;> > >>
> > >> select * from tab1;> > >>
> > >> (this select retrieves the following and waits:
> > >> ID name
> > >> 1 nam1
> > >> 2 nam2
> > >> 3 nam3
> > >> 4 nam4
> > >>
> > What is happening is that Transaction B is timing out waiting for
> transaction
> > A locks during the insert. What we want it to do is to retrieve the
> > committed/clean data, ignore anything that transaction A is doing, and
> return
> > immediately. Is this not the way it is supposed to work? All of the
> > documentation that I have read says that transaction B should return
> > immediately with all of the clean data not locked.
> >
> > Larry
> >
> >
> >
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Larry:
I am sorry I can not offer much of a solution. You can set lock mode to
wait forever, build custom retry logic or upgrade to version 11.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
> FW: Clean Reads/Database timing out [15758]
>
> LARRY SORENSEN
>
>
> John,
>
> The COMMITTED READ LAST COMMITTED seems to be working for our IDS 11
> databases. Can you suggestion anything to get around this for our
> non-ANSI IDS
> 10 databases without using DIRTY READ?
>
> Larry
>
> > To: ids@iiug.org
> > From: miller3@us.ibm.com
> > Subject: RE: Clean Reads/Database timing out [15720]
> > Date: Tue, 12 May 2009 16:43:59 -0400
> >
> > Larry::
> >
> > You failed to mention the type of database, ANSI or non-ansi logging
> > database
> > will make a big difference. It will change the default isolation level
and
> > how lock
> > are handled.
> >
> > If you are in version 11 or higher you should set the isolation level
to
> > committed read last committed. There are several way to accomplish
this,
> > in the application, in a global procedure (sysdbopen), or in the
onconfig
> > file.
> >
> > committed read last committed will always return the committed version
of
> > the
> > data and will not wait on locks of other users who are
deleting ,updating,
> > inserting data
> > but rather read through the lock to return the current committed
version of
> > the data.
> >
> > John F. Miller III
> > STSM, Support Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)
> >
> > ids-bounces@iiug.org wrote on 05/12/2009 12:56:36 PM:
> >
> > > [image removed]
> > >
> > > RE: Clean Reads/Database timing out [15718]
> > >
> > > LARRY SORENSEN
> > >
> > > to:
> > >
> > > ids
> > >
> > > 05/12/2009 12:57 PM
> > >
> > > Sent by:
> > >
> > > ids-bounces@iiug.org
> > >
> > > Please respond to ids
> > >
> > > All,
> > >
> > > I have the following situation for concurrent processes/transactions:
> > >
> > > >> Tab1 (LOCK MODE ROW) has the following data:
> > > >> ID name
> > > >> 1 nam1
> > > >> 2 nam2
> > > >> 3 nam3
> > > >> 4 nam4
> > > >>
> > > >> Transaction A:
> > > >> SET LOCK MODE TO WAIT 120;
> > > >> SET ISOLATION TO COMMITTED READ;> > > >>
> > > >> insert into tab1 values (5, 'name5');> > > >> ....
> > > >> ....
> > > >>
> > > >>
> > > >> Transaction B:
> > > >> SET LOCK MODE TO WAIT 12;
> > > >> SET ISOLATION TO COMMITTED READ;> > > >>
> > > >> select * from tab1;> > > >>
> > > >> (this select retrieves the following and waits:
> > > >> ID name
> > > >> 1 nam1
> > > >> 2 nam2
> > > >> 3 nam3
> > > >> 4 nam4
> > > >>
> > > What is happening is that Transaction B is timing out waiting for
> > transaction
> > > A locks during the insert. What we want it to do is to retrieve the
> > > committed/clean data, ignore anything that transaction A is doing,
and
> > return
> > > immediately. Is this not the way it is supposed to work? All of the
> > > documentation that I have read says that transaction B should return
> > > immediately with all of the clean data not locked.
> > >
> > > Larry
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>