Primary Key generated from a sequence is null during an INSERT operation

Posts   
 
    
Posts: 77
Joined: 05-May-2005
# Posted on: 31-Jul-2023 19:50:41   

LLBLGen Pro V5.7 (5.7.7) RTM .Net framework 4.6.2 Oracle 12 g LLBLGen Runtime version 5.5.7.0

Table COLLECTIONS_SESSION has PK column COLLECTIONS_SESSION_ID that is filled in from sequence COLLECTIONS_SESSION_ID. Occasionally, the insert for this row fails because the NEXTVAL statement for the sequence generates a null. Same code block every minute of the day and yet sometimes it fails to generate the insert statement properly.

In the log I see this for the failures: BEGIN SELECT COLLECTIONS_SESSION_ID.NEXTVAL INTO NULL FROM DUAL; INSERT INTO COLLECTIONS_SESSION (COLLECTIONS_SESSION_ID, SESSION_EMPLOYEE_ID, CONTAINER_TALLY_TOTAL, COIN_BAG_TALLY_TOTAL, FROM_TALLY_CLERK_ID, FROM_VEHICLE_ID, START_DTM, INSERTED_DTM, UPDATED_DTM) VALUES (NULL, 141307, 2, 0, 141542, 149, TO_DATE('20230720113726', 'YYYYMMDDHH24MISS'), TO_DATE('20230720114824', 'YYYYMMDDHH24MISS'), TO_DATE('20230720114824', 'YYYYMMDDHH24MISS')) ;END;

For the successful inserts, the insert statement looks like this: BEGIN SELECT COLLECTIONS_SESSION_ID.NEXTVAL INTO 31168 FROM DUAL; INSERT INTO COLLECTIONS_SESSION (COLLECTIONS_SESSION_ID, SESSION_EMPLOYEE_ID, SESSION_FACID, CONTAINER_EVENT_ID, SESSION_STATUS_ID, CONTAINER_TALLY_TOTAL, COIN_BAG_TALLY_TOTAL, CONTAINERS_RECEIVED_TOTAL, COIN_BAGS_RECEIVED_TOTAL, TALLY_TOTAL_CHANGED, FROM_TALLY_CLERK_ID, FROM_VEHICLE_ID, START_DTM, INSERTED_DTM, UPDATED_DTM) VALUES (31168, 141307, 2002, 906, 1, 32, 12, 20, 0, 0, 141504, 11, TO_DATE('20230720151031', 'YYYYMMDDHH24MISS'), TO_DATE('20230720151127', 'YYYYMMDDHH24MISS'), TO_DATE('20230720151127', 'YYYYMMDDHH24MISS')) ;END;

What could be causing this random behavior?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 01-Aug-2023 09:11:52   

How is this log generated btw? As it apparently specifies the value for a parameter and not the parameter name itself (NEXTVAL INTO <number> which is wrong)

Is there any multi-threading going in that code, or sharing of elements, like adapters, other elements that are used to generate the query?

Frans Bouma | Lead developer LLBLGen Pro
Posts: 77
Joined: 05-May-2005
# Posted on: 07-Aug-2023 16:48:46   

Otis wrote:

How is this log generated btw? As it apparently specifies the value for a parameter and not the parameter name itself (NEXTVAL INTO <number> which is wrong)

Extra code in the adapter to fill in the query parameter values before logging as it is easier to grab them out of the log and run them against the database if we need to.

Is there any multi-threading going in that code, or sharing of elements, like adapters, other elements that are used to generate the query?

No multi-threading or sharing of the adapter or elements in the entity. It works 98% of the time as is, and only occasionally does this weird thing.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 07-Aug-2023 16:59:13   

I honestly have no idea. Only thing I can think of is multi-threading/sharing of some element that's re-used across multiple queries/requests.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 77
Joined: 05-May-2005
# Posted on: 07-Aug-2023 17:42:08   

Otis wrote:

I honestly have no idea. Only thing I can think of is multi-threading/sharing of some element that's re-used across multiple queries/requests.

Thanks. I will go back through the code to see if I missed something regarding element sharing.