Topic
  • 13 replies
  • Latest Post - ‏2015-07-02T18:38:53Z by TuukkaIlomäki
TuukkaIlomäki
TuukkaIlomäki
79 Posts

Pinned topic EGL, keys and concurrent inserts

‏2013-12-03T13:13:37Z |

Dear collective wisdom,

we use DB2 and EGL-programs on iSeries as the backbone of our web services. Our tables have ID column with unique index constraint. Typically option "generated always with identity" is not used. (Changing table design is not an option here.) Instead, we query current max key and then do the insert by adding 1 to the max key. Something like the following:

myRec SqlRec;
myRec.value = "porkkana";
myRec.ID = getCurrentMaxID() + 1;
try
add myRec;
onException(e SQlException)
handleError();
end

The problem is that if two users are concurrently doing the inserts, we may receive duplicate key error (SQL code -803). What would be a solid I am looking for a solid pattern for handling keys when inserting rows. As far as I know, EGL does not allow locking table between getCurrentMaxID() and insert. While we could write insert SQL manually and use select max(ID)+1 in the insert, then we would not know which ID was inserted.

The best idea so far is to try adding record, catch slqCode 803, call getCurrentMaxID() again and try insert again. Any better ideas?

 

  • dan_darnell
    dan_darnell
    973 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-03T19:13:18Z  

    You could put a trigger on the table and let the trigger determine the unique ID. It would be a "before insert" trigger so that it could modify the row data before it is written.

    Here is an example. This one uses a data area to keep up with the last ID used but maybe you could do something similar by getting the max ID -- but I guess you'll still need a data area or something to lock and unlock in order to ensure that the updates happen synchronously so that you don't get a duplicate key error.

    http://www2.systeminetwork.com/code/index.cfm?fuseaction=ShowCodeInFile&Year=2000&Month=07&Page=057&HostType=AS400&FileType=RPGLE&PCName=UPDTRG.RP4&HostName=UPDTRG

    If you could use a data area to hold the last ID, like the example, that would be better still. Anyway, before we had the ability to use generated identity columns the trigger-based approach was commonly used. This way, no matter what writes the record, it gets a correctly assigned unique ID.

    --Dan

     

     

  • TuukkaIlomäki
    TuukkaIlomäki
    79 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-04T06:46:39Z  

    You could put a trigger on the table and let the trigger determine the unique ID. It would be a "before insert" trigger so that it could modify the row data before it is written.

    Here is an example. This one uses a data area to keep up with the last ID used but maybe you could do something similar by getting the max ID -- but I guess you'll still need a data area or something to lock and unlock in order to ensure that the updates happen synchronously so that you don't get a duplicate key error.

    http://www2.systeminetwork.com/code/index.cfm?fuseaction=ShowCodeInFile&Year=2000&Month=07&Page=057&HostType=AS400&FileType=RPGLE&PCName=UPDTRG.RP4&HostName=UPDTRG

    If you could use a data area to hold the last ID, like the example, that would be better still. Anyway, before we had the ability to use generated identity columns the trigger-based approach was commonly used. This way, no matter what writes the record, it gets a correctly assigned unique ID.

    --Dan

     

     

    Hello Dan, thanks for the suggestion. I was hoping that I was missing some more obvious solution, but I presume I wasn't.

    I would prefer not to resort to RPG and data areas. A similar thing could be done in EGL using a separate RunningKeys table. Last used running key is stored in the table and table row is locked using forupdate statement. It would be better if we could use max() for the pertinent table instead of a separate table, since it would keep the data in a single location. Alas, that approach fails concurrent updates.

    In the current issue we have (patching an old program), checking for slqCode 803 might be the quickest solution, but I'll know better in the future.

  • canutri
    canutri
    361 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-05T14:14:07Z  

    Hello Dan, thanks for the suggestion. I was hoping that I was missing some more obvious solution, but I presume I wasn't.

    I would prefer not to resort to RPG and data areas. A similar thing could be done in EGL using a separate RunningKeys table. Last used running key is stored in the table and table row is locked using forupdate statement. It would be better if we could use max() for the pertinent table instead of a separate table, since it would keep the data in a single location. Alas, that approach fails concurrent updates.

    In the current issue we have (patching an old program), checking for slqCode 803 might be the quickest solution, but I'll know better in the future.

    Skip the RPG and use the SQL CREATE TRIGGER statement. 

    Daron

    Updated on 2013-12-05T19:45:26Z at 2013-12-05T19:45:26Z by canutri
  • dan_darnell
    dan_darnell
    973 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-05T17:24:51Z  
    • canutri
    • ‏2013-12-05T14:14:07Z

    Skip the RPG and use the SQL CREATE TRIGGER statement. 

    Daron

    Do you have an example that updates the data buffer on a before insert trigger without using RPG? That was the requirement here. If that can be done with SQL alone and not RPG I would love to have an example of it for future reference.

    Thanks.

    Dan

     

     

  • canutri
    canutri
    361 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-05T19:45:12Z  

    Do you have an example that updates the data buffer on a before insert trigger without using RPG? That was the requirement here. If that can be done with SQL alone and not RPG I would love to have an example of it for future reference.

    Thanks.

    Dan

     

     

    Dan,

    Thanks for putting me on the spot - I should have done better than a simple "drive-by" post like that.

    So here's what I think should work...

    CREATE TABLE MySchema/MyTable (
        Id INTEGER  CONSTRAINT MyTableId UNIQUE,
        CustNo INTEGER,
        CustomerName VARCHAR(50))
      ;

    CREATE OR REPLACE TRIGGER MySchema/MyTableUniqueId
      BEFORE INSERT ON MySchema/MyTable
      REFERENCING NEW AS Bef
      FOR EACH ROW
      MODE DB2ROW

    BEGIN ATOMIC
      SET Bef.Id = IFNULL((SELECT MAX(Id) + 1
                                FROM MySchema/MyTable), 1);
    END;

    --  Insert some data w/o Id

    INSERT
      INTO MySchema/MyTable (CustNo, CustomerName)
      VALUES(123, 'ACME Co.')
      ;

    --  Insert some data with Id (value of Id will be overridden by trigger event)

    INSERT
      INTO MySchema/MyTable
      VALUES(22, 234, 'Smith & Co.')
      ;

    This does not take of concurrent inserts.  I'm curious if the trigger option

    CONCURRENT ACCESS RESOLUTION WAIT FOR OUTCOME
    

    will be of value; although, I'm not really sure what it does.

    Disclaimer:  I don't profess to be a DB2 for i guru.  But, I did stay at a Holiday Inn Express last night

    I hope this helps.

    Daron

    Updated on 2013-12-11T20:25:28Z at 2013-12-11T20:25:28Z by canutri
  • dan_darnell
    dan_darnell
    973 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-05T20:27:48Z  
    • canutri
    • ‏2013-12-05T19:45:12Z

    Dan,

    Thanks for putting me on the spot - I should have done better than a simple "drive-by" post like that.

    So here's what I think should work...

    CREATE TABLE MySchema/MyTable (
        Id INTEGER  CONSTRAINT MyTableId UNIQUE,
        CustNo INTEGER,
        CustomerName VARCHAR(50))
      ;

    CREATE OR REPLACE TRIGGER MySchema/MyTableUniqueId
      BEFORE INSERT ON MySchema/MyTable
      REFERENCING NEW AS Bef
      FOR EACH ROW
      MODE DB2ROW

    BEGIN ATOMIC
      SET Bef.Id = IFNULL((SELECT MAX(Id) + 1
                                FROM MySchema/MyTable), 1);
    END;

    --  Insert some data w/o Id

    INSERT
      INTO MySchema/MyTable (CustNo, CustomerName)
      VALUES(123, 'ACME Co.')
      ;

    --  Insert some data with Id (value of Id will be overridden by trigger event)

    INSERT
      INTO MySchema/MyTable
      VALUES(22, 234, 'Smith & Co.')
      ;

    This does not take of concurrent inserts.  I'm curious if the trigger option

    <pre class="fig syntaxdiagram" dir="ltr">CONCURRENT ACCESS RESOLUTION WAIT FOR OUTCOME </pre>

    will be of value; although, I'm not really sure what it does.

    Disclaimer:  I don't profess to be a DB2 for i guru.  But, I did stay at a Holiday Inn Express last night

    I hope this helps.

    Daron

    Daron,

    That's great! I really appreciate the follow-up with the example. I didn't know how to do that.

    --Dan

     

  • TuukkaIlomäki
    TuukkaIlomäki
    79 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-09T07:13:16Z  
    • canutri
    • ‏2013-12-05T19:45:12Z

    Dan,

    Thanks for putting me on the spot - I should have done better than a simple "drive-by" post like that.

    So here's what I think should work...

    CREATE TABLE MySchema/MyTable (
        Id INTEGER  CONSTRAINT MyTableId UNIQUE,
        CustNo INTEGER,
        CustomerName VARCHAR(50))
      ;

    CREATE OR REPLACE TRIGGER MySchema/MyTableUniqueId
      BEFORE INSERT ON MySchema/MyTable
      REFERENCING NEW AS Bef
      FOR EACH ROW
      MODE DB2ROW

    BEGIN ATOMIC
      SET Bef.Id = IFNULL((SELECT MAX(Id) + 1
                                FROM MySchema/MyTable), 1);
    END;

    --  Insert some data w/o Id

    INSERT
      INTO MySchema/MyTable (CustNo, CustomerName)
      VALUES(123, 'ACME Co.')
      ;

    --  Insert some data with Id (value of Id will be overridden by trigger event)

    INSERT
      INTO MySchema/MyTable
      VALUES(22, 234, 'Smith & Co.')
      ;

    This does not take of concurrent inserts.  I'm curious if the trigger option

    <pre class="fig syntaxdiagram" dir="ltr">CONCURRENT ACCESS RESOLUTION WAIT FOR OUTCOME </pre>

    will be of value; although, I'm not really sure what it does.

    Disclaimer:  I don't profess to be a DB2 for i guru.  But, I did stay at a Holiday Inn Express last night

    I hope this helps.

    Daron

    Thank you for the suggestion and detailed description of the trigger. However, I am still unsure how to combine this with EGL. The rationale behind the original code was that we can return the ID field for further processing. If the ID field is updated by a trigger, I presume we do not get the updated value in the EGL record. We could obtain the same result by defining a field as GENERATED ALWAYS WITH IDENTITY.

    Hence, the question remains: how it insert rows safely concurrently and know which was the ID in the row added?

  • canutri
    canutri
    361 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-10T15:49:02Z  

    Thank you for the suggestion and detailed description of the trigger. However, I am still unsure how to combine this with EGL. The rationale behind the original code was that we can return the ID field for further processing. If the ID field is updated by a trigger, I presume we do not get the updated value in the EGL record. We could obtain the same result by defining a field as GENERATED ALWAYS WITH IDENTITY.

    Hence, the question remains: how it insert rows safely concurrently and know which was the ID in the row added?

    Yes, the same result would be produced as using GENERATED ALWAYS WITH IDENTITY.  I understand you're unable to change the table do do such, so I was offering a method using the exiting id column with UNIQUE constraint.

    Could you move the i/o logic from EGL into an SQL Stored Procedure?

    Using the LOCK TABLE statement should enable you to INSERT the record and then query for MAX(id).  From that, the stored procedure would return the value as a parameter.

    Daron

    Updated on 2013-12-10T15:51:47Z at 2013-12-10T15:51:47Z by canutri
  • TuukkaIlomäki
    TuukkaIlomäki
    79 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-11T11:07:40Z  
    • canutri
    • ‏2013-12-10T15:49:02Z

    Yes, the same result would be produced as using GENERATED ALWAYS WITH IDENTITY.  I understand you're unable to change the table do do such, so I was offering a method using the exiting id column with UNIQUE constraint.

    Could you move the i/o logic from EGL into an SQL Stored Procedure?

    Using the LOCK TABLE statement should enable you to INSERT the record and then query for MAX(id).  From that, the stored procedure would return the value as a parameter.

    Daron

    This sounds worth exploring. We have not used stored procedures, but I presume appropriate documentation is amply available. What remains to be tested is whether the key created in the stored procedure is available in EGL. The examples in the EGL manual give a host variable as parameter (but it is unclear whether the host variable can be updated) or return a result set. Getting a result set from an insert operation seems a little odd, but I'll need to explore the alternatives.

  • Hsieh
    Hsieh
    693 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-11T13:34:17Z  

    Did you try used sql option "WITH UR" (Uncommited Read) on getCurrentMaxID ?

    The problem is that there may be key gap , if one or more transaction are not commited.

    Hsieh

  • canutri
    canutri
    361 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-11T20:13:55Z  

    This sounds worth exploring. We have not used stored procedures, but I presume appropriate documentation is amply available. What remains to be tested is whether the key created in the stored procedure is available in EGL. The examples in the EGL manual give a host variable as parameter (but it is unclear whether the host variable can be updated) or return a result set. Getting a result set from an insert operation seems a little odd, but I'll need to explore the alternatives.

    I don't believe the key would be intrinsically available in EGL.  It could be passed back as out parameter from the stored procedure to EGL.  Using the SQL definitions above, the stored procedure would look something like this:

    CREATE OR REPLACE PROCEDURE MySchema/AddCustomer(
        IN id INT,
        IN custNo INT,
        IN customerName VARCHAR(50),
        OUT idAssigned INT)
      LANGUAGE SQL

    BEGIN

      LOCK TABLE MySchema/MyTable IN SHARE MODE;
      INSERT INTO MySchema/MyTable (CustNo, CustomerName)
        VALUES (custNo, customerName);
     
      SET idAssigned = (SELECT MAX(id) FROM MySchema/MyTable);

    END;

     

    EGL code to CALL the stored procedure (untested):

    package com.mycompany.mypackage;

    // service

    service MyService

        
        function addCustomer()
            id int = 0;  //  doesn't matter as ignored and assigned by db
            custNo int = 1234;
            customerName string = "Acme Co.";
            assignedId int;

            try
                // EGL statements
                
                execute
                        #sql{
                            CALL MySchema/AddCustomer(:id,
                                                      :custNo,
                                                      :customerName,
                                                      :assignedId)
                        };
                SysLib.writeStdout("Record added with id = " + assignedId);
            onException(ex SQLException)
                SysLib.writeStdout(ex.message);
            end
        end

    end

     

    Depending on the # of columns being added in the db record, you may want to pass the record in a structure as one parameter.  However, this can get involved with INTEGER type data.  I haven't done it in quite some time so the methods may have changed.  The topic should be found on this forum.

    Daron

    Updated on 2013-12-11T20:26:00Z at 2013-12-11T20:26:00Z by canutri
  • TuukkaIlomäki
    TuukkaIlomäki
    79 Posts

    Re: EGL, keys and concurrent inserts

    ‏2013-12-15T17:03:57Z  
    • Hsieh
    • ‏2013-12-11T13:34:17Z

    Did you try used sql option "WITH UR" (Uncommited Read) on getCurrentMaxID ?

    The problem is that there may be key gap , if one or more transaction are not commited.

    Hsieh

    Hello Hsieh, thank you for the suggestion. 
     
    I was not aware of "WITH UR". Key gap is not an issue, but it sounds like there might still be a problem with concurrency. This option solves the case when "the other process" performs getCurrentMaxID after insert but before commit. However, I presume it does not solve the case when "the other process" performs getCurrentMaxID before both insert and commit. Hence, if my reading of the solution is correct, it might reduce the probability of errors but not eliminate them altogether (and testing would be a tedious task). Hence, I would prefer clear old fashioned table locking.

    Tuukka

  • TuukkaIlomäki
    TuukkaIlomäki
    79 Posts

    Re: EGL, keys and concurrent inserts

    ‏2015-07-02T18:38:53Z  

    I am resurrecting this old topic as we came up with a simple solution. Namely, we can base keys on sequences: the number obtained from a sequence is unique even in concurrent processes and can be used as a key when inserting data. First, create a sequence like below.

    CREATE SEQUENCE mylib/porkkana
    

    We can then get obtain key value from the sequence, for example using the following EGL code 

    
    function 
    getSequence()
     
     seq 
    int
    ;
    
     execute 
    #
    sql
    {
     
       
    SELECT 
    NEXT 
    VALUE 
    FOR 
    porkkana 
    into 
    :seq 
    FROM 
    SYSIBM.SYSDUMMY1
     
     };
    
    end
    

    Concurrent processes seem get unique keys from the sequence just fine. If defined using the default values, the keys may not be consecutive but that is not an essential requirement for us. We have not yet used this approach in production code, but initial tests are positive.