Potential duplicate key and documentno from MSequence

Description

MSequence can (and will, under concurrent load) potentially generate duplcate keys or generate a lock.
I have tracked the problem down to MSequence.getNextID: if the db is PostgreSQL a "for update" clause is appendend after the select, but is missing from oracle code.
Old Adempiere used a db procedure, wich was using the 'for update' clause. iDempiere doesnt use it anymore.
This result on duplicate key under concurrent load.

A similar issue affect MSequencce.getDocumentNoFromSeq, with an additional 'strange' check: if the sequence is for a system document no, no "for update" is appended (not even postgresql). I dont see any valid reason for that behaviour, but i may be missing something. The provided patch fixes that too.

To recap, the fix contains:
getNextID: added "for update of" CurrrentNext or CurrentNextSys for oracle db

getDocumentNoFromSeq:

  1. added "for update of"

  2. Check for 'isoracle' consistent with getNextId check

  3. removed check for system seq, always using "for update"

Regards,
Silvano, https://www.freepath.it

Environment

Oracle database

Status

Assignee

FreePath

Reporter

FreePath

Labels

Tested By

Nicolas Micoud

Components

Fix versions

Affects versions

Priority

Critical
Configure