split update into multiple transactions
Poster wanted to split a big UPDATE into many small transactions, skipping rows locked by other sessions (no table lock, no LOCK MODE WAIT), ideally updating via a cursor with WHERE CURRENT OF. In SPL, an ON EXCEPTION for the -244 lock error exits the FOREACH instead of continuing; in 4GL, ESQL/C (including a modified tx_split) and Perl, the cursor stays stuck on the locked row and refetches it forever. Suggestions included dirty read (already in use), a helper flag table, and Art Kagel's approach of collecting rowids/keys first (separate session or WITH HOLD cursor) and updating in batches. The poster settled for rowids; no way was found to do it with WHERE CURRENT OF.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi folks.
I'm trying to split a simple update into multiple transactions, to
avoid holding arbitrarily many locks. Locking the table (even in
shared mode) is not an option.
I need to be able to skip rows which are currently locked, they'll be
updated by another run later. Using "lock mode wait" also isn't an
option.
Additionally, it would be good if I could do the update "where current
of" a cursor rather than relying on the table's key or rowid. This is
where I run into trouble.
I first tried to do this using SPL. In order to use "where current of"
you have to use a foreach loop. When I use "on exception" to deal with
the locked rows I can't get it to continue to the next iteration of the
foreach loop, instead it exits the foreach. See sample below.
I next tried to do this using 4GL. This fails because an error encountered
doing a fetch of the locked row keeps the cursor on that locked row -- the
next fetch tries to fetch the same row and fails again. I can't figure out
a way to get it to move past the locked row. A sample of this is below
too.
Thanks for any help.
Roderick
PS: Is there any way to post code samples so that they won't be
left-justified by the mailing list?
-------------------------------------------------------------------------------
These samples assume a database "db" with a table "tab" which has a column
"col int", and one of the rows is locked by a different session/process.
I tested this with IDS 11.70.FC8GE, 4GL 7.51.FC1XD, and sqlcmd 88.00 on
RHEL 5.11.
-- SPL sample -----------------------------------------------------------------
-- This fails because an error caught doing the foreach fetch (the
-- locked row) resumes after the foreach (exiting it) rather than with
-- the next iteration of the foreach.
set debug file to '/tmp/t.debug';
drop procedure if exists update_test;
create procedure update_test();
define i like tab.col;
begin work;
trace 'before block';
begin
on exception
trace 'caught exception outside foreach';
end exception with resume;
trace 'before foreach';
foreach update_c for
select col into i from tab
on exception
trace 'caught exception in foreach';
continue foreach;
end exception with resume;
update tab set col = 23 where current of update_c;
trace 'inside foreach';
end foreach;
trace 'after foreach';
end;
trace 'after block';
rollback work;
end procedure;
execute procedure update_test();
drop procedure update_test;
-- SPL results ----------------------------------------------------------------
If it's the 3rd row in the table which is locked the debug file ends up
like this:
trace expression :before block
trace expression :before foreach
trace expression :inside foreach
trace expression :inside foreach
trace expression :caught exception outside foreach
trace expression :after foreach
trace expression :after block
-- 4GL sample -----------------------------------------------------------------
database db
main
define s like tab.col
declare select_c cursor for
select col from tab for update
begin work
open select_c
while TRUE
whenever error continue
fetch select_c into s
whenever error stop
case
when sqlca.sqlcode = NOTFOUND exit while
when sqlca.sqlcode != 0 display sqlca.sqlcode
otherwise display "[", s, "]"
end case
end while
rollback work
end main
-- 4GL results ----------------------------------------------------------------
This outputs the rows before the lock then loops outputting "-244"
repeatedly. If you release the lock in the other window it continues
through the rest of the rows in the table.
-------------------------------------------------------------------------------
Can you change isolation level to dirty read inside the stored procedure?
Regards,
David.
> On 31 May 2017 at 19:02 Roderick Schertler <roderick@argon.org> wrote:
>
>
> Hi folks.
>
> I'm trying to split a simple update into multiple transactions, to
> avoid holding arbitrarily many locks. Locking the table (even in
> shared mode) is not an option.
>
> I need to be able to skip rows which are currently locked, they'll be
> updated by another run later. Using "lock mode wait" also isn't an
> option.
>
> Additionally, it would be good if I could do the update "where current
> of" a cursor rather than relying on the table's key or rowid. This is
> where I run into trouble.
>
> I first tried to do this using SPL. In order to use "where current of"
> you have to use a foreach loop. When I use "on exception" to deal with
> the locked rows I can't get it to continue to the next iteration of the
> foreach loop, instead it exits the foreach. See sample below.
>
> I next tried to do this using 4GL. This fails because an error encountered
> doing a fetch of the locked row keeps the cursor on that locked row -- the
> next fetch tries to fetch the same row and fails again. I can't figure out
> a way to get it to move past the locked row. A sample of this is below
> too.
>
> Thanks for any help.
>
> Roderick
>
> PS: Is there any way to post code samples so that they won't be
> left-justified by the mailing list?
>
>
>
-------------------------------------------------------------------------------
>
> These samples assume a database "db" with a table "tab" which has a column
> "col int", and one of the rows is locked by a different session/process.
>
> I tested this with IDS 11.70.FC8GE, 4GL 7.51.FC1XD, and sqlcmd 88.00 on
> RHEL 5.11.
>
> -- SPL sample
> -----------------------------------------------------------------
>
> -- This fails because an error caught doing the foreach fetch (the
>
> -- locked row) resumes after the foreach (exiting it) rather than with
>
> -- the next iteration of the foreach.
>
> set debug file to '/tmp/t.debug';
>
> drop procedure if exists update_test;>
> create procedure update_test();>
> define i like tab.col;
>
> begin work;
>
> trace 'before block';
>
> begin
>
> on exception
>
> trace 'caught exception outside foreach';
>
> end exception with resume;
>
> trace 'before foreach';
>
> foreach update_c for
>
> select col into i from tab
>
> on exception
>
> trace 'caught exception in foreach';
>
> continue foreach;
>
> end exception with resume;
>
> update tab set col = 23 where current of update_c;>
> trace 'inside foreach';
>
> end foreach;
>
> trace 'after foreach';
>
> end;
>
> trace 'after block';
>
> rollback work;
>
> end procedure;
>
> execute procedure update_test();>
> drop procedure update_test;>
> -- SPL results
> ----------------------------------------------------------------
>
> If it's the 3rd row in the table which is locked the debug file ends up
> like this:
>
> trace expression :before block
>
> trace expression :before foreach
>
> trace expression :inside foreach
>
> trace expression :inside foreach
>
> trace expression :caught exception outside foreach
>
> trace expression :after foreach
>
> trace expression :after block
>
> -- 4GL sample
> -----------------------------------------------------------------
>
> database db
>
> main
>
> define s like tab.col
>
> declare select_c cursor for
>
> select col from tab for update>
> begin work
>
> open select_c
>
> while TRUE
>
> whenever error continue
>
> fetch select_c into s
>
> whenever error stop
>
> case
>
> when sqlca.sqlcode = NOTFOUND exit while
>
> when sqlca.sqlcode != 0 display sqlca.sqlcode
>
> otherwise display "[", s, "]"
>
> end case
>
> end while
>
> rollback work
>
> end main
>
> -- 4GL results
> ----------------------------------------------------------------
>
> This outputs the rows before the lock then loops outputting "-244"
> repeatedly. If you release the lock in the other window it continues
> through the rest of the rows in the table.
>
>
>
-------------------------------------------------------------------------------
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
take a look at tx_split, it is in the iiug code repository.
http://members.iiug.org/software/archive/tx_split.html
ftp://ftp.iiug.org/pub/informix/pub/tx_split.tar.gz
You will need esql/c for this.
Marcus Haarmann
Von: "Roderick Schertler" <roderick@argon.org>
An: "ids" <ids@iiug.org>
Gesendet: Mittwoch, 31. Mai 2017 20:02:38
Betreff: split update into multiple transactions [39306]
Hi folks.
I'm trying to split a simple update into multiple transactions, to
avoid holding arbitrarily many locks. Locking the table (even in
shared mode) is not an option.
I need to be able to skip rows which are currently locked, they'll be
updated by another run later. Using "lock mode wait" also isn't an
option.
Additionally, it would be good if I could do the update "where current
of" a cursor rather than relying on the table's key or rowid. This is
where I run into trouble.
I first tried to do this using SPL. In order to use "where current of"
you have to use a foreach loop. When I use "on exception" to deal with
the locked rows I can't get it to continue to the next iteration of the
foreach loop, instead it exits the foreach. See sample below.
I next tried to do this using 4GL. This fails because an error encountered
doing a fetch of the locked row keeps the cursor on that locked row -- the
next fetch tries to fetch the same row and fails again. I can't figure out
a way to get it to move past the locked row. A sample of this is below
too.
Thanks for any help.
Roderick
PS: Is there any way to post code samples so that they won't be
left-justified by the mailing list?
-------------------------------------------------------------------------------
These samples assume a database "db" with a table "tab" which has a column
"col int", and one of the rows is locked by a different session/process.
I tested this with IDS 11.70.FC8GE, 4GL 7.51.FC1XD, and sqlcmd 88.00 on
RHEL 5.11.
-- SPL sample
-----------------------------------------------------------------
-- This fails because an error caught doing the foreach fetch (the
-- locked row) resumes after the foreach (exiting it) rather than with
-- the next iteration of the foreach.
set debug file to '/tmp/t.debug';
drop procedure if exists update_test;
create procedure update_test();
define i like tab.col;
begin work;
trace 'before block';
begin
on exception
trace 'caught exception outside foreach';
end exception with resume;
trace 'before foreach';
foreach update_c for
select col into i from tab
on exception
trace 'caught exception in foreach';
continue foreach;
end exception with resume;
update tab set col = 23 where current of update_c;
trace 'inside foreach';
end foreach;
trace 'after foreach';
end;
trace 'after block';
rollback work;
end procedure;
execute procedure update_test();
drop procedure update_test;
-- SPL results
----------------------------------------------------------------
If it's the 3rd row in the table which is locked the debug file ends up
like this:
trace expression :before block
trace expression :before foreach
trace expression :inside foreach
trace expression :inside foreach
trace expression :caught exception outside foreach
trace expression :after foreach
trace expression :after block
-- 4GL sample
-----------------------------------------------------------------
database db
main
define s like tab.col
declare select_c cursor for
select col from tab for update
begin work
open select_c
while TRUE
whenever error continue
fetch select_c into s
whenever error stop
case
when sqlca.sqlcode = NOTFOUND exit while
when sqlca.sqlcode != 0 display sqlca.sqlcode
otherwise display "[", s, "]"
end case
end while
rollback work
end main
-- 4GL results
----------------------------------------------------------------
This outputs the rows before the lock then loops outputting "-244"
repeatedly. If you release the lock in the other window it continues
through the rest of the rows in the table.
-------------------------------------------------------------------------------
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
The isolation is already dirty read, we set it in sysopen(). We migrated from SE some years ago, we needed this to avoid breaking stuff.
You haven't said what your table structure looks like, but here's a kind of
ugly idea about how you could make it work, assuming you have some spare disk
you could use for a table that just has the primary key from the table you're
updating and a flag to say it's done.
CREATE TABLE update_table
(
This_Key INT, (or whatever your key looks like)
Update_flag SMALLINT
)
UPDATE update_table SET update_flag = 0.
CREATE INDEX this_index ON update_table (This_Key, Update_flag)
Load the update table from the main table.
Loop through the update table not using a cursor. Use SELECT MIN(This_Key)
WHERE update_flag = 0 each time, so that you don't create locks or anything
that would bail out if the update to the main table fails. I know, this isn't
fast like a cursor.
Use a try catch to update the old table where the key matches the current key
and update the update_flag to 1 on the helper table.
If the try fails, update the update_flag to 2, instead of 1.
Once that finishes, you should have some straggler helper table records with
update flags set to 2. Update them to 0 and repeat until all are done.
You can then dispose of the helper table.
--EEM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of RODERICK
SCHERTLER
Sent: Thursday, June 1, 2017 7:57 AM
To: ids@iiug.org
Subject: Re: split update into multiple transactions [39312]
The isolation is already dirty read, we set it in sysopen(). We migrated from
SE some years ago, we needed this to avoid breaking stuff.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks, I looked at tx_split. The way it's written it uses a wait-mode lock, and if it times out it throws an error. I changed it to use not-wait and updated the code to loop if it gets a -244 error. With this in place the behavior was the same as in 4GL -- the cursor keeps trying to fetch the same (locked) row rather than moving on to the next one. I also tested the behavior in Perl and it was the same as in 4GL and ESQL/C. I've come to believe that what I'm trying to do just isn't possible using "where current of". Thanks for the replies. Roderick On Wed, May 31, 2017 at 4:35 PM, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > take a look at tx_split, it is in the iiug code repository. > http://members.iiug.org/software/archive/tx_split.html > ftp://ftp.iiug.org/pub/informix/pub/tx_split.tar.gz > > You will need esql/c for this. > > Marcus Haarmann > > Von: "Roderick Schertler" <roderick@argon.org> > An: "ids" <ids@iiug.org> > Gesendet: Mittwoch, 31. Mai 2017 20:02:38 > Betreff: split update into multiple transactions [39306] > > Hi folks. > > I'm trying to split a simple update into multiple transactions, to > avoid holding arbitrarily many locks. Locking the table (even in > shared mode) is not an option. > > I need to be able to skip rows which are currently locked, they'll be > updated by another run later. Using "lock mode wait" also isn't an > option. > > Additionally, it would be good if I could do the update "where current > of" a cursor rather than relying on the table's key or rowid. This is > where I run into trouble. > > I first tried to do this using SPL. In order to use "where current of" > you have to use a foreach loop. When I use "on exception" to deal with > the locked rows I can't get it to continue to the next iteration of the > foreach loop, instead it exits the foreach. See sample below. > > I next tried to do this using 4GL. This fails because an error encountered > doing a fetch of the locked row keeps the cursor on that locked row -- the > next fetch tries to fetch the same row and fails again. I can't figure out > a way to get it to move past the locked row. A sample of this is below > too. > > Thanks for any help. > > Roderick >
Roderick: The way to do this is to fetch the rowids that you need to update in a separate cursor, preferably in a separate database (use CONNECT TO and SET CONNECTION) session or cached into a local array, then in a separate session you can update a batch of rows by looping on the list of rowids fetched previously and commit periodically. If the rowids are fetched into a local array or from another session, the commits would interfere at all. If you do it looping on a cursor in the same session, you can declare that rowid cursor to be WITH HOLD so it will not close when you commit the updates. Look at the code for my dbdelete utility for other clues, but 99% of what you need is right here.. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Jun 8, 2017 at 3:14 PM, Roderick Schertler <roderick@argon.org> wrote: > Thanks, I looked at tx_split. The way it's written it uses a wait-mode > lock, and if it times out it throws an error. I changed it to use not-wait > and updated the code to loop if it gets a -244 error. With this in place > the behavior was the same as in 4GL -- the cursor keeps trying to fetch the > same (locked) row rather than moving on to the next one. > > I also tested the behavior in Perl and it was the same as in 4GL and > ESQL/C. > > I've come to believe that what I'm trying to do just isn't possible using > "where current of". > > Thanks for the replies. > > Roderick > > On Wed, May 31, 2017 at 4:35 PM, Marcus Haarmann < > marcus.haarmann@midoco.de> > wrote: > > > take a look at tx_split, it is in the iiug code repository. > > http://members.iiug.org/software/archive/tx_split.html > > ftp://ftp.iiug.org/pub/informix/pub/tx_split.tar.gz > > > > You will need esql/c for this. > > > > Marcus Haarmann > > > > Von: "Roderick Schertler" <roderick@argon.org> > > An: "ids" <ids@iiug.org> > > Gesendet: Mittwoch, 31. Mai 2017 20:02:38 > > Betreff: split update into multiple transactions [39306] > > > > Hi folks. > > > > I'm trying to split a simple update into multiple transactions, to > > avoid holding arbitrarily many locks. Locking the table (even in > > shared mode) is not an option. > > > > I need to be able to skip rows which are currently locked, they'll be > > updated by another run later. Using "lock mode wait" also isn't an > > option. > > > > Additionally, it would be good if I could do the update "where current > > of" a cursor rather than relying on the table's key or rowid. This is > > where I run into trouble. > > > > I first tried to do this using SPL. In order to use "where current of" > > you have to use a foreach loop. When I use "on exception" to deal with > > the locked rows I can't get it to continue to the next iteration of the > > foreach loop, instead it exits the foreach. See sample below. > > > > I next tried to do this using 4GL. This fails because an error > encountered > > doing a fetch of the locked row keeps the cursor on that locked row -- > the > > next fetch tries to fetch the same row and fails again. I can't figure > out > > a way to get it to move past the locked row. A sample of this is below > > too. > > > > Thanks for any help. > > > > Roderick > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Thanks, Art. Using rowids is what I had to settle for. The question was about doing this with a cursor using "where current of", that's what I've not been able to do. Roderick On Thu, Jun 8, 2017 at 3:29 PM, Art Kagel <art.kagel@gmail.com> wrote: > Roderick: > > The way to do this is to fetch the rowids that you need to update in a > separate cursor, preferably in a separate database (use CONNECT TO and SET > CONNECTION) session or cached into a local array, then in a separate > session you can update a batch of rows by looping on the list of rowids > fetched previously and commit periodically. If the rowids are fetched into > a local array or from another session, the commits would interfere at all. > If you do it looping on a cursor in the same session, you can declare that > rowid cursor to be WITH HOLD so it will not close when you commit the > updates. > > Look at the code for my dbdelete utility for other clues, but 99% of what > you need is right here.. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.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 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, Jun 8, 2017 at 3:14 PM, Roderick Schertler <roderick@argon.org> > wrote: > > > Thanks, I looked at tx_split. The way it's written it uses a wait-mode > > lock, and if it times out it throws an error. I changed it to use > not-wait > > and updated the code to loop if it gets a -244 error. With this in place > > the behavior was the same as in 4GL -- the cursor keeps trying to fetch > the > > same (locked) row rather than moving on to the next one. > > > > I also tested the behavior in Perl and it was the same as in 4GL and > > ESQL/C. > > > > I've come to believe that what I'm trying to do just isn't possible using > > "where current of". > > > > Thanks for the replies. > > > > Roderick > > > > On Wed, May 31, 2017 at 4:35 PM, Marcus Haarmann < > > marcus.haarmann@midoco.de> > > wrote: > > > > > take a look at tx_split, it is in the iiug code repository. > > > http://members.iiug.org/software/archive/tx_split.html > > > ftp://ftp.iiug.org/pub/informix/pub/tx_split.tar.gz > > > > > > You will need esql/c for this. > > > > > > Marcus Haarmann > > > > > > Von: "Roderick Schertler" <roderick@argon.org> > > > An: "ids" <ids@iiug.org> > > > Gesendet: Mittwoch, 31. Mai 2017 20:02:38 > > > Betreff: split update into multiple transactions [39306] > > > > > > Hi folks. > > > > > > I'm trying to split a simple update into multiple transactions, to > > > avoid holding arbitrarily many locks. Locking the table (even in > > > shared mode) is not an option. > > > > > > I need to be able to skip rows which are currently locked, they'll be > > > updated by another run later. Using "lock mode wait" also isn't an > > > option. > > > > > > Additionally, it would be good if I could do the update "where current > > > of" a cursor rather than relying on the table's key or rowid. This is > > > where I run into trouble. > > > > > > I first tried to do this using SPL. In order to use "where current of" > > > you have to use a foreach loop. When I use "on exception" to deal with > > > the locked rows I can't get it to continue to the next iteration of the > > > foreach loop, instead it exits the foreach. See sample below. > > > > > > I next tried to do this using 4GL. This fails because an error > > encountered > > > doing a fetch of the locked row keeps the cursor on that locked row -- > > the > > > next fetch tries to fetch the same row and fails again. I can't figure > > out > > > a way to get it to move past the locked row. A sample of this is below > > > too. > > > > > > Thanks for any help. > > > > > > Roderick > > > > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >