SELECT ... FOR UPDATE in 'dbaccess'
Joerg wanted a single dbaccess script to UNLOAD all rows flagged processed='N' and then mark them 'Y', without catching rows inserted by concurrent processes meanwhile; he proposed using COMMITTED READ RETAIN UPDATE LOCKS plus SELECT ... FOR UPDATE. Art Kagel warned the update locks (including on index nodes) would block concurrent inserts/queries and suggested writing a proper extract-and-update program instead. Everett Mills offered the accepted-looking alternative: use a three-state flag — first UPDATE 'N' to 'X', unload the 'X' rows, then set them to 'Y' — avoiding FOR UPDATE and leaving new rows for the next run. Joerg planned to test; no final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi there,
Following "problem":
I would like to unload some data out of a table and afterwards flag exactly
this unloaded data. Thing is that during unload and update the table will be
filled with other data continuously (with the processed = 'N' flag). I would
like to use a single dbaccess call.
Will this code work correctly and not accidently update data that came into
the table during the unload (or the update)?
First on the shell: setenv DBACCNOIGN 1
Then with 'dbaccess':
BEGIN;
SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS;
UNLOAD TO $UNLOADFILE SELECT * FROM $TABLE WHERE processed = 'N' FOR UPDATE;UPDATE $TABLE SET processed = 'Y' WHERE processed = 'N';
COMMIT;
Thanks in advance for you help.
Joerg.
Most likely it will block out many of the other inserts and updates that are
being attempted.
Why not just write your own extract-and-update program in ESQL/C, C-CLI,
Java-JDBC, Perl-DBD/DBI, 4GL, etc.?
If you don't have the expertise there's lots of talent for hire out there
(including myself ;-) and it would not be an expensive project. Fairly
small actually.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Thu, Jan 7, 2010 at 7:56 AM, JOERG REDEMANN <joerg.redemann@sabre.com>wrote:
> Hi there,
>
> Following "problem":
>
> I would like to unload some data out of a table and afterwards flag exactly
> this unloaded data. Thing is that during unload and update the table will
> be
> filled with other data continuously (with the processed = 'N' flag). I
> would
> like to use a single dbaccess call.
>
> Will this code work correctly and not accidently update data that came into
> the table during the unload (or the update)?
>
> First on the shell: setenv DBACCNOIGN 1
>
> Then with 'dbaccess':
>
> BEGIN;
> SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS;
> UNLOAD TO $UNLOADFILE SELECT * FROM $TABLE WHERE processed = 'N' FOR> UPDATE;
> UPDATE $TABLE SET processed = 'Y' WHERE processed = 'N';
> COMMIT;
>
> Thanks in advance for you help.
>
> Joerg.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517447f0c7fb6af047c94c6f1
Thanks for your thoughts Art, >> Most likely it will block out many of the other inserts and updates that are being attempted. Hmmm... why? If table lock level is row there should nothing be blocked I think. This unload will run as single task every hour. During this time ONLY NEW inserts will take place. >>Why not just write your own extract-and-update.... :-)) Yeah. I will catch a develop colleague if testing will not lead to (my) expected result. But first I will create a realistic scenario (didn't find the time so far...) and test. Thanks!
The problem will be the locks in the indexes. Any query that needs to scan an index node may encounter a locked node or leaf. Also any query that's performing a table scan will surely encounter locked rows. Update locks are exclusive, no reading except DIRTY READ and COMMITTED READ with READ COMMITTED. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Thu, Jan 7, 2010 at 11:13 AM, JOERG REDEMANN <joerg.redemann@sabre.com>wrote: > Thanks for your thoughts Art, > > >> Most likely it will block out many of the other inserts and updates that > are > being attempted. > > Hmmm... why? If table lock level is row there should nothing be blocked I > think. This unload will run as single task every hour. During this time > ONLY > NEW inserts will take place. > > >>Why not just write your own extract-and-update.... > > :-)) Yeah. I will catch a develop colleague if testing will not lead to > (my) > expected result. > > But first I will create a realistic scenario (didn't find the time so > far...) > and test. > > Thanks! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174c3c1c929123047c956de4
Ah - the indexes. Got it. In this case should not be a problem (indexes or data) - the continous running inserts are queued by a java proceess detached from application. Users will not notice anything - hopefully. It's only these 2 processes which are concurrent from time to time for ~1 minute. See what the tests reveal... Thanks again.
Wouldn't it be cleaner if you used a three state flag? That way you could mark
the ones you want to unload, unload them and then mark them complete, leaving
any rows inserted during your unload for the next iteration. It also relieves
you of needing the for update clause. We use three state flags in almost every
situation where inserts are coming into a table we need to work on throughout
the day.
--EEM
Example:
UPDATE $TABLE SET processed = 'X' WHERE processed = 'N'
UNLOAD TO $UNLOADFILE SELECT * FROM $TABLE WHERE processed = 'X'
UPDATE $TABLE SET processed = 'Y' WHERE processed = 'X'
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Thursday, January 07, 2010 10:19 AM
> To: ids@iiug.org
> Subject: Re: SELECT ... FOR UPDATE in 'dbaccess' [18614]
>
> The problem will be the locks in the indexes. Any query that needs to
> scan
> an index node may encounter a locked node or leaf. Also any query
> that's
> performing a table scan will surely encounter locked rows. Update locks
> are
> exclusive, no reading except DIRTY READ and COMMITTED READ with READ
> COMMITTED.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> 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 Thu, Jan 7, 2010 at 11:13 AM, JOERG REDEMANN
> <joerg.redemann@sabre.com>wrote:
>
> > Thanks for your thoughts Art,
> >
> > >> Most likely it will block out many of the other inserts and
> updates that
> > are
> > being attempted.
> >
> > Hmmm... why? If table lock level is row there should nothing be
> blocked I
> > think. This unload will run as single task every hour. During this
> time
> > ONLY
> > NEW inserts will take place.
> >
> > >>Why not just write your own extract-and-update....
> >
> > :-)) Yeah. I will catch a develop colleague if testing will not lead
> to
> > (my)
> > expected result.
> >
> > But first I will create a realistic scenario (didn't find the time so
> > far...)
> > and test.
> >
> > Thanks!
> >
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0015174c3c1c929123047c956de4
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.