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.