[
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}
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}
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:
{{CREATE TABLE foo(id BIGINT AUTO_INCREMENT, ...);}}
PostgreSQL:
{{CREATE TABLE foo(id SERIAL, ...);}}
MySQL:
{{CREATE TABLE foo(id BIGINT AUTO_INCREMENT UNIQUE, ...);}}
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 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;
/
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 UNIQUE;
> 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}
> 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}
--
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
