To: informix-list@iiug.org (informix-list@iiug.org)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)