IDS Forum

Re: insert in locked table

Posted By: Fernando Nunes
Date: Friday, 13 October 2017, at 10:36 a.m.

In Response To: AW: insert in locked table (Habichtsberg, Reinhard)

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...

Messages In This Thread

• insert in locked table
  KAMRAN HAQ -- Friday, 13 October 2017, at 8:18 a.m.
  • Re: insert in locked table
    Fernando Nunes -- Friday, 13 October 2017, at 9:14 a.m.
    • Re: insert in locked table
      Fernando Nunes -- Friday, 13 October 2017, at 9:46 a.m.
      • AW: insert in locked table
        Habichtsberg, Reinhard -- Friday, 13 October 2017, at 9:59 a.m.
        • Re: insert in locked table
          Fernando Nunes -- Friday, 13 October 2017, at 10:36 a.m.
          • Re: insert in locked table
            KAMRAN HAQ -- Friday, 13 October 2017, at 1:45 p.m.
          • AW: insert in locked table
            Habichtsberg, Reinhard -- Monday, 16 October 2017, at 7:07 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.