serial fields

Posted in 2003

rkusenet — Sun, 25 May 2003 21:21:47 — source: IIUG Forums & Mailing Lists

IMHO Informix implementation of serial fields has a major flaw in salability. Informix serial field holds a lock on that value till it is either committed or rolled back. What benefit does it give to hold a lock. The only benefit it gives is that if a transaction is rolled back, then we won't have gaps in serial field. Any other benefit. consider this:- begin work ; insert into table1 (... (this table contains a serial field) insert into table2 ... (this table contains FKY to table1 using that serial field) insert into table3 ... (this table contains FKY to table1 using that serial field) insert into table4 ... (this table contains FKY to table1 using that serial field)commit work ; In this case, other sessions have to wait till this session commits, to proceed ahead with next serial value. I don't know about other databases except SQL Server,where the serial value (IDENTITY) is immediately released to other sessions (plus one value), regardless of commit or rollback in this session. I am about to write a new application where I have to insert into 4 tables, like the above example. So I guess, the only option is to create another table just with a serial field and get the next serial value, outside the transaction. Then use that value for the transaction mentioned above. I don't like this approach and I genuinely hope that I am making some mistake in what I have concluded above.

↪ replying to rkusenet

Kompella Prashant — Sun, 25 May 2003 23:56:47 — source: IIUG Forums & Mailing Lists

The value of SQLCA.SQLERRD[2] Will give you the serial value of the first insert, this can be used for subsequent FK inserts in the same transaction. Prashant -----Original Message----- From: rkusenet [mailto:rkusenet@sympatico.ca] Sent: Monday, 26 May 2003 7:22 AM To: ids@iiug.org Subject: serial fields [1215] IMHO Informix implementation of serial fields has a major flaw in salability. Informix serial field holds a lock on that value till it is either committed or rolled back. What benefit does it give to hold a lock. The only benefit it gives is that if a transaction is rolled back, then we won't have gaps in serial field. Any other benefit. consider this:- begin work ; insert into table1 (... (this table contains a serial field) insert into table2 ... (this table contains FKY to table1 using that serial field) insert into table3 ... (this table contains FKY to table1 using that serial field) insert into table4 ... (this table contains FKY to table1 using that serial field)commit work ; In this case, other sessions have to wait till this session commits, to proceed ahead with next serial value. I don't know about other databases except SQL Server,where the serial value (IDENTITY) is immediately released to other sessions (plus one value), regardless of commit or rollback in this session. I am about to write a new application where I have to insert into 4 tables, like the above example. So I guess, the only option is to create another table just with a serial field and get the next serial value, outside the transaction. Then use that value for the transaction mentioned above. I don't like this approach and I genuinely hope that I am making some mistake in what I have concluded above. *********************************************************** CAUTION: This Message may contain confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you received this message in error please notify Mail Administrators immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of Woolworths Ltd. ***********************************************************

↪ replying to rkusenet

rkusenet — Mon, 26 May 2003 01:49:15 — source: IIUG Forums & Mailing Lists

> The value of SQLCA.SQLERRD[2] Will give you the serial value of the first > insert, this can be used for subsequent FK inserts in the same transaction. I know this. But this is not what I asked. Please read my post again. Ravi

↪ replying to rkusenet

hotmail — Mon, 26 May 2003 05:28:23 — source: IIUG Forums & Mailing Lists

Try this: begin work; insert into countertable values(0);( this is RAW table with serial field only) insert into table1 ( pk_field, ... ) values ( dbinfo("sqlca.sqlerrd2"), ... ) ( this table contains PK integer field, not serial) insert into table2 (... ( this table contains FK integer field to PK on table1 )... etc. commit work; ATTENTION! Serial fields on RAW tables and ER environments ignores CDR_SERIAL directive. They behave like classical Serials. ----- Original Message ----- From: "rkusenet" <rkusenet@sympatico.ca> To: <ids@iiug.org> Sent: Sunday, May 25, 2003 11:21 PM Subject: serial fields [1215] > IMHO Informix implementation of serial fields has a major flaw > in salability. Informix serial field holds a lock on that value > till it is either committed or rolled back. What benefit does it > give to hold a lock. The only benefit it gives is that if a > transaction is rolled back, then we won't have gaps in serial field. > Any other benefit. > > consider this:- > > begin work ; > insert into table1 (... > (this table contains a serial field)> > insert into table2 ... > (this table contains FKY to table1 using that serial field)> > insert into table3 ... > (this table contains FKY to table1 using that serial field)> > insert into table4 ... > (this table contains FKY to table1 using that serial field)> commit work ; > > In this case, other sessions have to wait till this session > commits, to proceed ahead with next serial value. > > I don't know about other databases except SQL Server,where the serial > value (IDENTITY) is immediately released to other sessions > (plus one value), regardless of commit or rollback in this session. > > I am about to write a new application where I have to insert into 4 tables, > like the above example. So I guess, the only option is to create another > table just with a serial field and get the next serial value, outside the > transaction. Then use that value for the transaction mentioned above. > I don't like this approach and I genuinely hope that I am making > some mistake in what I have concluded above. > > > > > > > > > > >

↪ replying to rkusenet

rkusenet — Mon, 26 May 2003 09:28:37 — source: IIUG Forums & Mailing Lists

----- Original Message ----- From: "Michael Mueller" <michael.mueller@kay-mueller.de> To: "rkusenet" <rkusenet@sympatico.ca> Cc: <ids@iiug.org> Sent: May 26, 2003 04:15 Subject: Re: serial fields [1215] > One possibility is that your table1 has lockmode page. In this case a > second transaction that happens to hit the same page would be blocked. > But this has nothing to do with serial columns. Yes you are right. As I mentioned in my post, I was genuinely hoping that my diagnosis was incorrect and indeed I was way off the mark. I created a small test table and started two sessions to insert values into the serial field. But one session was made to wait for 10 seconds before committing. I noticed that the second session was also waiting. Bah!!!. I forgot lock mode row. Once I changed the lock mode to row, the second session did not wait for the first session to complete and when the first session rolledback, there was a gap in the serial number, as it should be. Sorry everyone for rather unnecessary thread. Thanks Michael. Ravi