Topic
12 replies Latest Post - ‏2013-12-15T17:03:57Z by TuukkaIlomäki
TuukkaIlomäki
TuukkaIlomäki
67 Posts
ACCEPTED ANSWER

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
    969 Posts
    ACCEPTED ANSWER

    Re: EGL, keys and concurrent inserts

    ‏2013-12-03T19:13:18Z  in response to TuukkaIlomäki

    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
      67 Posts
      ACCEPTED ANSWER

      Re: EGL, keys and concurrent inserts

      ‏2013-12-04T06:46:39Z  in response to dan_darnell

      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
        329 Posts
        ACCEPTED ANSWER

        Re: EGL, keys and concurrent inserts

        ‏2013-12-05T14:14:07Z  in response to TuukkaIlomäki

        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
          969 Posts
          ACCEPTED ANSWER

          Re: EGL, keys and concurrent inserts

          ‏2013-12-05T17:24:51Z  in response to canutri

          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
            329 Posts
            ACCEPTED ANSWER

            Re: EGL, keys and concurrent inserts

            ‏2013-12-05T19:45:12Z  in response to dan_darnell

            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
              969 Posts
              ACCEPTED ANSWER

              Re: EGL, keys and concurrent inserts

              ‏2013-12-05T20:27:48Z  in response to canutri

              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
              67 Posts
              ACCEPTED ANSWER

              Re: EGL, keys and concurrent inserts

              ‏2013-12-09T07:13:16Z  in response to canutri

              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
                329 Posts
                ACCEPTED ANSWER

                Re: EGL, keys and concurrent inserts

                ‏2013-12-10T15:49:02Z  in response to TuukkaIlomäki

                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
                  67 Posts
                  ACCEPTED ANSWER

                  Re: EGL, keys and concurrent inserts

                  ‏2013-12-11T11:07:40Z  in response to canutri

                  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.

                  • canutri
                    canutri
                    329 Posts
                    ACCEPTED ANSWER

                    Re: EGL, keys and concurrent inserts

                    ‏2013-12-11T20:13:55Z  in response to TuukkaIlomäki

                    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
  • Hsieh
    Hsieh
    554 Posts
    ACCEPTED ANSWER

    Re: EGL, keys and concurrent inserts

    ‏2013-12-11T13:34:17Z  in response to TuukkaIlomäki

    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

    • TuukkaIlomäki
      TuukkaIlomäki
      67 Posts
      ACCEPTED ANSWER

      Re: EGL, keys and concurrent inserts

      ‏2013-12-15T17:03:57Z  in response to 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