insert in locked table

We used similar script as following to replace a table with new table(existing table was approaching to max pages limit). Both tables are locked and an insert statement with maximum serial column value from existing table is inserted in new table to keep the serial in accordance with existing table. Still we get 7 rows with serial number 1 to 7 before our inserted value. We got inserts in that table almost every second but how inserts was successful by a non dba user while table was locked? Script ------ create table tab_new(sr serial , val varchar(20) ); begin work; lock table tab in exclusive mode; lock table tab_new in exclusive mode; rename table tab to tab_old; rename table tab_new to tab; insert into tab select * from tab_old where sr = ( select max(i.sr) from tab_old i );commit; if max value was 123456 then we found following sequence in newly created table now "tab" after rename 1, 2, 3, 4, 5, 6, 7, 123456, 123457 ... IDS 12.1FC7 on SunOS

I'm having issues understanding the example and this may be important to understand the issue. The query you show: insert into tab select * from tab_old where sr = ( select max(i.sr) fromtab_old i ); would select from tab_old only the records where "sr" matches the max value... is that really what you're doing? Apart from that: 1- Is the new table name the same as the application uses? In other words, are you doing something like: 1) rename existing_table to existing_table_old; 2) create table existing_table (...); 3) begin work; 4) lock table... If yes, between 2 and 4 you may get INSERTs You should create the new table as "existing_table_new" do the process and at the end do "rename existing_table_new to existing_table" Regards. ... On Fri, Oct 13, 2017 at 1:18 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > We used similar script as following to replace a table with new > table(existing > table was approaching to max pages limit). Both tables are locked and an > insert statement with maximum serial column value from existing table is > inserted in new table to keep the serial in accordance with existing table. > Still we get 7 rows with serial number 1 to 7 before our inserted value. We > got inserts in that table almost every second but how inserts was > successful > by a non dba user while table was locked? > > Script > ------ > create table tab_new(> sr serial > , val varchar(20) > ); > begin work; > lock table tab in exclusive mode; > lock table tab_new in exclusive mode; > rename table tab to tab_old; > rename table tab_new to tab; > insert into tab select * from tab_old > where sr = ( select max(i.sr) from tab_old i );> commit; > > if max value was 123456 then we found following sequence in newly created > table now "tab" after rename > 1, 2, 3, 4, 5, 6, 7, 123456, 123457 ... > > IDS 12.1FC7 on SunOS > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Hi... I made some wrong assumptions and I now understand what you're doing... (thanks to a quick chat with Luis Marques). The problem is that when you have a lock on a table... all the pending INSERTs will get the "sr" from 1. So when you commit, they complete with the initial obtained value... This behavior could be discussed, but the easiest way to solve this is to exchange the order of the operations: Do the RENAME of the new table AFTER the INSERT This should be able to avoid the issue. Regards. On Fri, Oct 13, 2017 at 2:14 PM, Fernando Nunes <domusonline@gmail.com> wrote: > I'm having issues understanding the example and this may be important to > understand the issue. The query you show: > > insert into tab select * from tab_old where sr = ( select max(i.sr) from> tab_old i ); > > would select from tab_old only the records where "sr" matches the max > value... is that really what you're doing? > > Apart from that: > 1- Is the new table name the same as the application uses? In other words, > are you doing something like: > > 1) rename existing_table to existing_table_old; > 2) create table existing_table (...); > 3) begin work; > 4) lock table... > > If yes, between 2 and 4 you may get INSERTs > You should create the new table as "existing_table_new" do the process and > at the end do "rename existing_table_new to existing_table" > > Regards. > .... > > On Fri, Oct 13, 2017 at 1:18 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > > > We used similar script as following to replace a table with new > > table(existing > > table was approaching to max pages limit). Both tables are locked and an > > insert statement with maximum serial column value from existing table is > > inserted in new table to keep the serial in accordance with existing > table. > > Still we get 7 rows with serial number 1 to 7 before our inserted value. > We > > got inserts in that table almost every second but how inserts was > > successful > > by a non dba user while table was locked? > > > > Script > > ------ > > create table tab_new(> > sr serial > > , val varchar(20) > > ); > > begin work; > > lock table tab in exclusive mode; > > lock table tab_new in exclusive mode; > > rename table tab to tab_old; > > rename table tab_new to tab; > > insert into tab select * from tab_old > > where sr = ( select max(i.sr) from tab_old i );> > commit; > > > > if max value was 123456 then we found following sequence in newly created > > table now "tab" after rename > > 1, 2, 3, 4, 5, 6, 7, 123456, 123457 ... > > > > IDS 12.1FC7 on SunOS > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Fernando If he changes the order the new rows are inserted in the old table and the serial number he starts with in the new table is no longer max of the old table. If the sequence doesn't matter he could copy these rows to the new table after switching. Regards Reinhard. -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von Fernando Nunes Gesendet: Freitag, 13. Oktober 2017 15:46 An: ids@iiug.org Betreff: Re: insert in locked table [40069] Hi... I made some wrong assumptions and I now understand what you're doing... (thanks to a quick chat with Luis Marques). The problem is that when you have a lock on a table... all the pending INSERTs will get the "sr" from 1. So when you commit, they complete with the initial obtained value... This behavior could be discussed, but the easiest way to solve this is to exchange the order of the operations: Do the RENAME of the new table AFTER the INSERT This should be able to avoid the issue. Regards. On Fri, Oct 13, 2017 at 2:14 PM, Fernando Nunes <domusonline@gmail.com> wrote: > I'm having issues understanding the example and this may be important > to understand the issue. The query you show: > > insert into tab select * from tab_old where sr = ( select max(i.sr) > from tab_old i );> > would select from tab_old only the records where "sr" matches the max > value... is that really what you're doing? > > Apart from that: > 1- Is the new table name the same as the application uses? In other > words, are you doing something like: > > 1) rename existing_table to existing_table_old; > 2) create table existing_table (...); > 3) begin work; > 4) lock table... > > If yes, between 2 and 4 you may get INSERTs You should create the new > table as "existing_table_new" do the process and at the end do "rename > existing_table_new to existing_table" > > Regards. > .... > > On Fri, Oct 13, 2017 at 1:18 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > > > We used similar script as following to replace a table with new > > table(existing table was approaching to max pages limit). Both > > tables are locked and an insert statement with maximum serial column > > value from existing table is inserted in new table to keep the > > serial in accordance with existing > table. > > Still we get 7 rows with serial number 1 to 7 before our inserted value. > We > > got inserts in that table almost every second but how inserts was > > successful by a non dba user while table was locked? > > > > Script > > ------ > > create table tab_new(> > sr serial > > , val varchar(20) > > ); > > begin work; > > lock table tab in exclusive mode; > > lock table tab_new in exclusive mode; rename table tab to tab_old; > > rename table tab_new to tab; insert into tab select * from tab_old > > where sr = ( select max(i.sr) from tab_old i ); commit; > > > > if max value was 123456 then we found following sequence in newly > > created table now "tab" after rename 1, 2, 3, 4, 5, 6, 7, 123456, > > 123457 ... > > > > IDS 12.1FC7 on SunOS > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

No if he first renames the existing_table to existing_table_old The INSERTS will return an error... On Fri, Oct 13, 2017 at 2:59 PM, Habichtsberg, Reinhard < RHabichtsberg@arz-emmendingen.de> wrote: > Fernando > > If he changes the order the new rows are inserted in the old table and the > serial number he starts with in the new table is no longer max of the old > table. > > If the sequence doesn't matter he could copy these rows to the new table > after > switching. > > Regards > Reinhard. > > -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > Fernando Nunes > Gesendet: Freitag, 13. Oktober 2017 15:46 > An: ids@iiug.org > Betreff: Re: insert in locked table [40069] > > Hi... I made some wrong assumptions and I now understand what you're > doing... > (thanks to a quick chat with Luis Marques). > The problem is that when you have a lock on a table... all the pending > INSERTs > will get the "sr" from 1. So when you commit, they complete with the > initial > obtained value... This behavior could be discussed, but the easiest way to > solve this is to exchange the order of the operations: > > Do the RENAME of the new table AFTER the INSERT > > This should be able to avoid the issue. > Regards. > > On Fri, Oct 13, 2017 at 2:14 PM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > I'm having issues understanding the example and this may be important > > to understand the issue. The query you show: > > > > insert into tab select * from tab_old where sr = ( select max(i.sr) > > from tab_old i );> > > > would select from tab_old only the records where "sr" matches the max > > value... is that really what you're doing? > > > > Apart from that: > > 1- Is the new table name the same as the application uses? In other > > words, are you doing something like: > > > > 1) rename existing_table to existing_table_old; > > 2) create table existing_table (...); > > 3) begin work; > > 4) lock table... > > > > If yes, between 2 and 4 you may get INSERTs You should create the new > > table as "existing_table_new" do the process and at the end do "rename > > existing_table_new to existing_table" > > > > Regards. > > .... > > > > On Fri, Oct 13, 2017 at 1:18 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > > > > > We used similar script as following to replace a table with new > > > table(existing table was approaching to max pages limit). Both > > > tables are locked and an insert statement with maximum serial column > > > value from existing table is inserted in new table to keep the > > > serial in accordance with existing > > table. > > > Still we get 7 rows with serial number 1 to 7 before our inserted > value. > > We > > > got inserts in that table almost every second but how inserts was > > > successful by a non dba user while table was locked? > > > > > > Script > > > ------ > > > create table tab_new(> > > sr serial > > > , val varchar(20) > > > ); > > > begin work; > > > lock table tab in exclusive mode; > > > lock table tab_new in exclusive mode; rename table tab to tab_old; > > > rename table tab_new to tab; insert into tab select * from tab_old > > > where sr = ( select max(i.sr) from tab_old i ); commit; > > > > > > if max value was 123456 then we found following sequence in newly > > > created table now "tab" after rename 1, 2, 3, 4, 5, 6, 7, 123456, > > > 123457 ... > > > > > > IDS 12.1FC7 on SunOS > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Reversing the order solved the problem so it looks that the pending transactions get the sequence numbers (of serial column) while waiting the release of lock(s).

Fernando Sorry, yes you are perfectly right. I confused something with the order... Regards, Reinhard. -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von Fernando Nunes Gesendet: Freitag, 13. Oktober 2017 16:36 An: ids@iiug.org Betreff: Re: insert in locked table [40071] No if he first renames the existing_table to existing_table_old The INSERTS will return an error... On Fri, Oct 13, 2017 at 2:59 PM, Habichtsberg, Reinhard < RHabichtsberg@arz-emmendingen.de> wrote: > Fernando > > If he changes the order the new rows are inserted in the old table and > the serial number he starts with in the new table is no longer max of > the old table. > > If the sequence doesn't matter he could copy these rows to the new > table after switching. > > Regards > Reinhard. > > -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > Fernando Nunes > Gesendet: Freitag, 13. Oktober 2017 15:46 > An: ids@iiug.org > Betreff: Re: insert in locked table [40069] > > Hi... I made some wrong assumptions and I now understand what you're > doing... > (thanks to a quick chat with Luis Marques). > The problem is that when you have a lock on a table... all the pending > INSERTs will get the "sr" from 1. So when you commit, they complete > with the initial obtained value... This behavior could be discussed, > but the easiest way to solve this is to exchange the order of the > operations: > > Do the RENAME of the new table AFTER the INSERT > > This should be able to avoid the issue. > Regards. > > On Fri, Oct 13, 2017 at 2:14 PM, Fernando Nunes > <domusonline@gmail.com> > wrote: > > > I'm having issues understanding the example and this may be > > important to understand the issue. The query you show: > > > > insert into tab select * from tab_old where sr = ( select max(i.sr) > > from tab_old i );> > > > would select from tab_old only the records where "sr" matches the > > max value... is that really what you're doing? > > > > Apart from that: > > 1- Is the new table name the same as the application uses? In other > > words, are you doing something like: > > > > 1) rename existing_table to existing_table_old; > > 2) create table existing_table (...); > > 3) begin work; > > 4) lock table... > > > > If yes, between 2 and 4 you may get INSERTs You should create the > > new table as "existing_table_new" do the process and at the end do > > "rename existing_table_new to existing_table" > > > > Regards. > > .... > > > > On Fri, Oct 13, 2017 at 1:18 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > > > > > We used similar script as following to replace a table with new > > > table(existing table was approaching to max pages limit). Both > > > tables are locked and an insert statement with maximum serial > > > column value from existing table is inserted in new table to keep > > > the serial in accordance with existing > > table. > > > Still we get 7 rows with serial number 1 to 7 before our inserted > value. > > We > > > got inserts in that table almost every second but how inserts was > > > successful by a non dba user while table was locked? > > > > > > Script > > > ------ > > > create table tab_new(> > > sr serial > > > , val varchar(20) > > > ); > > > begin work; > > > lock table tab in exclusive mode; lock table tab_new in exclusive > > > mode; rename table tab to tab_old; rename table tab_new to tab; > > > insert into tab select * from tab_old where sr = ( select> > > max(i.sr) from tab_old i ); commit; > > > > > > if max value was 123456 then we found following sequence in newly > > > created table now "tab" after rename 1, 2, 3, 4, 5, 6, 7, 123456, > > > 123457 ... > > > > > > IDS 12.1FC7 on SunOS > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.