To:
informix-list@iiug.org (
informix-list@iiug.org)
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.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)