Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-7124

Make SQL directories with auto-incremented id field robust in multi-threaded scenarios

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.4
    • Fix Version/s: 5.5
    • Component/s: Core VCS, Directory
    • Tags:
    • Impact type:
      Content model Change
    • Upgrade notes:
      Hide

      For a table "mytable" with id column "myid", the following upgrade steps must be taken for all tables specified as <autoincrementIdField>true</autoincrementIdField>:

      First, note the next sequence value "thestart", which will be needed to initialize the sequences created:
      SELECT COALESCE(MAX(myid) + 1, 0) FROM mytable;

      Then convert the table to use an auto-incremented column:

      PostgreSQL:
      CREATE SEQUENCE mytable_myid_seq START WITH thestart;
      ALTER TABLE mytable ALTER COLUMN myid SET DEFAULT NEXTVAL('mytable_myid_seq');
      ALTER SEQUENCE mytable_myid_seq OWNED BY mytable.myid;

      MySQL:
      SET INSERT_ID = thestart;
      ALTER TABLE mytable MODIFY COLUMN myid BIGINT AUTO_INCREMENT PRIMARY KEY;

      SQL Server:
      ALTER TABLE mytable DROP CONSTRAINT mytable_myid_pk;
      ALTER TABLE mytable ADD myid2 BIGINT IDENTITY PRIMARY KEY;
      UPDATE mytable SET myid2 = myid;
      ALTER TABLE mytable DROP myid;
      EXEC sp_rename 'mytable.myid2', 'myid', 'column'
      DBCC CHECKIDENT (mytable, RESEED, thestart-1);

      Oracle:
      CREATE SEQUENCE mytable_IDSEQ START WITH thestart;
      /
      CREATE OR REPLACE TRIGGER mytable_IDTRIG
      BEFORE INSERT ON mytable
      FOR EACH ROW WHEN (NEW.myid IS NULL)
      BEGIN
      SELECT mytable_IDSEQ.NEXTVAL INTO :NEW.myid FROM DUAL;
      END;
      /

      Show
      For a table "mytable" with id column "myid", the following upgrade steps must be taken for all tables specified as <autoincrementIdField>true</autoincrementIdField>: First, note the next sequence value "thestart", which will be needed to initialize the sequences created: SELECT COALESCE(MAX(myid) + 1, 0) FROM mytable; Then convert the table to use an auto-incremented column: PostgreSQL: CREATE SEQUENCE mytable_myid_seq START WITH thestart; ALTER TABLE mytable ALTER COLUMN myid SET DEFAULT NEXTVAL('mytable_myid_seq'); ALTER SEQUENCE mytable_myid_seq OWNED BY mytable.myid; MySQL: SET INSERT_ID = thestart; ALTER TABLE mytable MODIFY COLUMN myid BIGINT AUTO_INCREMENT PRIMARY KEY; SQL Server: ALTER TABLE mytable DROP CONSTRAINT mytable_myid_pk; ALTER TABLE mytable ADD myid2 BIGINT IDENTITY PRIMARY KEY; UPDATE mytable SET myid2 = myid; ALTER TABLE mytable DROP myid; EXEC sp_rename 'mytable.myid2', 'myid', 'column' DBCC CHECKIDENT (mytable, RESEED, thestart-1); Oracle: CREATE SEQUENCE mytable_IDSEQ START WITH thestart; / CREATE OR REPLACE TRIGGER mytable_IDTRIG BEFORE INSERT ON mytable FOR EACH ROW WHEN (NEW.myid IS NULL) BEGIN SELECT mytable_IDSEQ.NEXTVAL INTO :NEW.myid FROM DUAL; END; /

      Description

      The current autoincrementIdField generation does a SELECT MAX(id) FROM thetable and then inserts its value + 1.
      This is not robust in cluster mode or in multi-threaded mode, as two different parallel transaction will compute the same max.

      This must be replaced by a proper use of database-backed autoincrement or serial columns.

      H2:

      CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...);

      PostgreSQL:

      CREATE TABLE mytable(myid SERIAL, ...);

      MySQL:

      CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...);

      SQL Server:

      CREATE TABLE mytable (myid BIGINT IDENTITY PRIMARY KEY, ...);

      Oracle:

      CREATE TABLE mytable(myid INTEGER PRIMARY KEY, ...);
/
CREATE SEQUENCE mytable_IDSEQ; 
/
CREATE TRIGGER mytable_IDTRIG
BEFORE INSERT ON mytable
FOR EACH ROW WHEN (NEW.myid IS NULL)
BEGIN
SELECT mytable_IDSEQ.NEXTVAL INTO :NEW.myid FROM DUAL;
END;
/

        Attachments

          Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. NXP-7092 Fix autoincrement in directory for cluster mode

          • Major - Major loss of function.
          • Resolved
          is required by

          Bug - A problem which impairs or prevents the functions of the product. NXP-11030 PostgreSQL directory auto-incremented ids may fail to return

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Resolved

            Activity

              People

              • Assignee:
                fguillaume Florent Guillaume
                Reporter:
                fguillaume Florent Guillaume
                Participants:
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: