Duplicate serial in Informix 7.2.3

Hi, I've developed an webservice application which accesses an informix database. When the service is invoked, the application executes some business logic, opens a transaction inserts values in 3 tables (let's call them A, B and C), and commits it. Table A's primary key column is of type serial and my application sets no value to it, so the number is automatically generated. This is also a foreign key to table B. My problem is that sometimes a duplicate serial is generated, when two consecutive transactions insert values in table A. I assume that this happens when the second transaction makes the insert before the first one is commited, but I'm not really sure about this. I've tried several isolation levels (repeatable read, read commited and serializable) and observed the same behaviour. We are using Informix 7.2.3 running under solaris 2.6. Anyone can help me? Thanks in advance. Pedro

On Jul 25, 2:18 pm, pedro <pedro.e.sa...@gmail.com> wrote: > Hi, > > I've developed an webservice application which accesses an informix > database. > When the service is invoked, the application executes some business > logic, opens a transaction inserts values in 3 tables (let's call them > A, B and C), and commits it. Table A's primary key column is of type > serial and my application sets no value to it, so the number is > automatically generated. This is also a foreign key to table B. > > My problem is that sometimes a duplicate serial is generated, when two > consecutive transactions insert values in table A. I assume that this > happens when the second transaction makes the insert before the first > one is commited, but I'm not really sure about this. I've tried > several isolation levels (repeatable read, read commited and > serializable) and observed the same behaviour. We are using Informix > 7.2.3 running under solaris 2.6. > > Anyone can help me? > > Thanks in advance. > Pedro That should not ever happen! The serial number is assigned and the table's next serial value updated under a mutex latch. Two sessions should NEVER get the same value no matter what! Indeed if you rollback an insert to a table with a serial number the number assigned to that rolled back row is not reissued but is skipped. IDS 7.23 is a VERY old release, it's possible that there was such a bug in that version. I would contact IBM Tech Support and see. At any rate you should upgrade, not the least reason than because there were significant speed increases in releases 7.24, 7.30, 7.31, 9.30, 9.40, 10.10, & now 11.00 and you're missing out. It's likely that if you port your web service to IDS 10.10 or even to the latest 11.00 (Cheetah) release it may run twice as fast and support many more users on the same hardware. Art S. Kagel

>From: "Art S. Kagel" <art.kagel@gmail.com> >On Jul 25, 2:18 pm, pedro <pedro.e.sa...@gmail.com> wrote: > > Hi, > > > > I've developed an webservice application which accesses an informix > > database. > > When the service is invoked, the application executes some business > > logic, opens a transaction inserts values in 3 tables (let's call them > > A, B and C), and commits it. Table A's primary key column is of type > > serial and my application sets no value to it, so the number is > > automatically generated. This is also a foreign key to table B. > > > > My problem is that sometimes a duplicate serial is generated, when two > > consecutive transactions insert values in table A. I assume that this > > happens when the second transaction makes the insert before the first > > one is commited, but I'm not really sure about this. I've tried > > several isolation levels (repeatable read, read commited and > > serializable) and observed the same behaviour. We are using Informix > > 7.2.3 running under solaris 2.6. > > > > Anyone can help me? > > > > Thanks in advance. > > Pedro > >That should not ever happen! The serial number is assigned and the >table's next serial value updated under a mutex latch. Two sessions >should NEVER get the same value no matter what! Indeed if you >rollback an insert to a table with a serial number the number assigned >to that rolled back row is not reissued but is skipped. > >IDS 7.23 is a VERY old release, it's possible that there was such a >bug in that version. > >I would contact IBM Tech Support and see. At any rate you should >upgrade, not the least reason than because there were significant >speed increases in releases 7.24, 7.30, 7.31, 9.30, 9.40, 10.10, & now >11.00 and you're missing out. It's likely that if you port your web >service to IDS 10.10 or even to the latest 11.00 (Cheetah) release it >may run twice as fast and support many more users on the same >hardware. > >Art S. Kagel Its not a bug. (The serial data type had been perfected back in the days of SE) I don't believe that 7.23 is still being supported so I suspect there isn't a support contract in place. As you already point out that you *cant* have duplicates in a serial column. It will *never* happen. (You have the sequence generator and then the identity index.) Note: Since you can still insert external rows, if there is a duplicate, that insert will be rejected. (no duh!) However, if that external row's identity number is > than the serial sequence number, the sequence generator will consider that the last value inserted such that the next value in the sequence generator would be n+1. Example: You use the serial type and insert rows 1,2,3,4,5. The last value in the sequence generator is 5. Now you insert a row in to the table with a value of 10 in the serial column. The next value used by the sequence generator is 11. What I suspect is that the OP is seeing a row duplicated on his web page and not the database. But what do I know? ;-) _________________________________________________________________ http://liveearth.msn.com

Please show us the schema of the table and indexes for the table that has the serial column. The serial column is not unique by default. You have to enforce this with a unique index or primary key constraint. If you create the table using dbaccess and the ring menu, this is done for you. Other wise you have to create the unique index. You will always get a unique value if you let the table assign it, but is someone comes along and forces an insert and you do not have a unique index, you could get duplicates. RTFM Roy On Jul 25, 1:18 pm, pedro <pedro.e.sa...@gmail.com> wrote: > Hi, > > I've developed an webservice application which accesses an informix > database. > When the service is invoked, the application executes some business > logic, opens a transaction inserts values in 3 tables (let's call them > A, B and C), and commits it. Table A's primary key column is of type > serial and my application sets no value to it, so the number is > automatically generated. This is also a foreign key to table B. > > My problem is that sometimes a duplicate serial is generated, when two > consecutive transactions insert values in table A. I assume that this > happens when the second transaction makes the insert before the first > one is commited, but I'm not really sure about this. I've tried > several isolation levels (repeatable read, read commited and > serializable) and observed the same behaviour. We are using Informix > 7.2.3 running under solaris 2.6. > > Anyone can help me? > > Thanks in advance. > Pedro

On Jul 25, 4:19 pm, Roy Mercer <roy.mer...@gmail.com> wrote: > Please show us the schema of the table and indexes for the table that > has the serial column. > > The serial column is not unique by default. You have to enforce this > with a unique index or primary key constraint. > > If you create the table using dbaccess and the ring menu, this is done > for you. Other wise you have to create the unique index. > > You will always get a unique value if you let the table assign it, but > is someone comes along and forces an insert and you do not have a > unique index, you could get duplicates. > > RTFM > Roy > > On Jul 25, 1:18 pm, pedro <pedro.e.sa...@gmail.com> wrote: > > > Hi, > > > I've developed an webservice application which accesses an informix > > database. > > When the service is invoked, the application executes some business > > logic, opens a transaction inserts values in 3 tables (let's call them > > A, B and C), and commits it. Table A's primary key column is of type > > serial and my application sets no value to it, so the number is > > automatically generated. This is also a foreign key to table B. > > > My problem is that sometimes a duplicate serial is generated, when two > > consecutive transactions insert values in table A. I assume that this > > happens when the second transaction makes the insert before the first > > one is commited, but I'm not really sure about this. I've tried > > several isolation levels (repeatable read, read commited and > > serializable) and observed the same behaviour. We are using Informix > > 7.2.3 running under solaris 2.6. > > > Anyone can help me? > > > Thanks in advance. > > Pedro I think Roy has nailed it. I did not consider that the table may have been created without a UNIQUE index or constraint. If it was a so created, then a user using another poorly written application that does "select max(serial_field) from ...; insert ... (selected_value, ...)" or an interactive tool like dbaccess or isql COULD manually insert a row with the same value that a properly written app was inserting using the "0" value properly. Art S. Kagel

On Jul 25, 5:10 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Jul 25, 4:19 pm, Roy Mercer <roy.mer...@gmail.com> wrote: > > > > > Please show us the schema of the table and indexes for the table that > > has the serial column. > > > The serial column is not unique by default. You have to enforce this > > with a unique index or primary key constraint. > > > If you create the table using dbaccess and the ring menu, this is done > > for you. Other wise you have to create the unique index. > > > You will always get a unique value if you let the table assign it, but > > is someone comes along and forces an insert and you do not have a > > unique index, you could get duplicates. > > > RTFM > > Roy > > > On Jul 25, 1:18 pm, pedro <pedro.e.sa...@gmail.com> wrote: > > > > Hi, > > > > I've developed an webservice application which accesses an informix > > > database. > > > When the service is invoked, the application executes some business > > > logic, opens a transaction inserts values in 3 tables (let's call them > > > A, B and C), and commits it. Table A's primary key column is of type > > > serial and my application sets no value to it, so the number is > > > automatically generated. This is also a foreign key to table B. > > > > My problem is that sometimes a duplicate serial is generated, when two > > > consecutive transactions insert values in table A. I assume that this > > > happens when the second transaction makes the insert before the first > > > one is commited, but I'm not really sure about this. I've tried > > > several isolation levels (repeatable read, read commited and > > > serializable) and observed the same behaviour. We are using Informix > > > 7.2.3 running under solaris 2.6. > > > > Anyone can help me? > > > > Thanks in advance. > > > Pedro > > I think Roy has nailed it. I did not consider that the table may have > been created without a UNIQUE index or constraint. If it was a so > created, then a user using another poorly written application that > does "select max(serial_field) from ...; insert ... > (selected_value, ...)" or an interactive tool like dbaccess or isql > COULD manually insert a row with the same value that a properly > written app was inserting using the "0" value properly. > > Art S. Kagel Uh guys....yes, Roy HAS nailed it. For G to reinforce that it will "*NEVER* ... (no duh!)" happen is absolutely untrue. Serial datatypes have NEVER been unique by default. Many, many clients (and apparently an IBMer or two) feel that they are...when traveling the US and world, I'd always ask this question openly to the crowd. The majority always felt that they were unique. I believe most of the time they didn't believe me, but....and, if you're not sure that I'm sure, just try it! It's easy enough to test. The serial value for a tablespace is stored in the partition page for that tablespace as the "Current value", which really means, the "next value to be used." If you wanna see some interesting stuff, play with seeding a negative value, then pass 0, etc...you might be surprised by what happens. Or maybe not. Try it! HTH - Mark Scranton Livin' the Farm Life (ok Clown, have at it...I'm sure this will generate some fodder from someone!)