[ https://jira.nuxeo.com/browse/NXP-7124?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Florent Guillaume updated NXP-7124: ----------------------------------- 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: {code} CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...); {code} PostgreSQL: {code} CREATE TABLE mytable(myid SERIAL, ...); {code} MySQL: {code} CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...); {code} SQL Server: {code} CREATE TABLE mytable (myid BIGINT IDENTITY PRIMARY KEY, ...); {code} Oracle: {code} CREATE TABLE mytable(myid INTEGER PRIMARY KEY, ...); / CREATE OR REPLACE TRIGGER mytable_seq_trig BEFORE INSERT ON mytable FOR EACH ROW WHEN(NEW.myid IS NULL) BEGIN SELECT mytable_seq.nextval INTO :NEW.myid FROM dual; END; / {code} was: 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: {code} CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...); {code} PostgreSQL: {code} CREATE TABLE mytable(myid SERIAL, ...); {code} MySQL: {code} CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...); {code} Oracle: {code} CREATE TABLE mytable(myid INTEGER PRIMARY KEY, ...); / CREATE OR REPLACE TRIGGER mytable_seq_trig BEFORE INSERT ON mytable REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT mytable_seq.nextval INTO :NEW.myid FROM dual; END; / {code} Upgrade notes: 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 MAX(myid) + 1 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'); 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_seq START WITH thestart; / CREATE OR REPLACE TRIGGER mytable_seq_trig BEFORE INSERT ON mytable FOR EACH ROW WHEN(NEW.myid IS NULL) BEGIN SELECT mytable_seq.nextval INTO :NEW.myid FROM dual; END; / was: 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 MAX(myid) + 1 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'); MySQL: SET INSERT_ID = thestart; ALTER TABLE mytable MODIFY myid BIGINT AUTO_INCREMENT PRIMARY KEY; Oracle: CREATE SEQUENCE mytable_seq START WITH thestart; / CREATE OR REPLACE TRIGGER mytable_seq_trig BEFORE INSERT ON mytable REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT mytable_seq.nextval INTO :NEW.myid FROM dual; END; / > Make SQL directories with auto-incremented id field robust in multi-threaded > scenarios > -------------------------------------------------------------------------------------- > > Key: NXP-7124 > URL: https://jira.nuxeo.com/browse/NXP-7124 > Project: Nuxeo Enterprise Platform > Issue Type: Bug > Components: Core SQL Storage, Directory > Affects Versions: 5.4 > Reporter: Florent Guillaume > Assignee: Florent Guillaume > Priority: Major > Fix For: 5.4.3 > > > 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: > {code} > CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...); > {code} > PostgreSQL: > {code} > CREATE TABLE mytable(myid SERIAL, ...); > {code} > MySQL: > {code} > CREATE TABLE mytable(myid BIGINT AUTO_INCREMENT PRIMARY KEY, ...); > {code} > SQL Server: > {code} > CREATE TABLE mytable (myid BIGINT IDENTITY PRIMARY KEY, ...); > {code} > Oracle: > {code} > CREATE TABLE mytable(myid INTEGER PRIMARY KEY, ...); > / > CREATE OR REPLACE TRIGGER mytable_seq_trig > BEFORE INSERT ON mytable > FOR EACH ROW > WHEN(NEW.myid IS NULL) > BEGIN > SELECT mytable_seq.nextval INTO :NEW.myid FROM dual; > END; > / > {code} -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira _______________________________________________ ECM-tickets mailing list ECM-tickets@lists.nuxeo.com http://lists.nuxeo.com/mailman/listinfo/ecm-tickets