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
- added "for update of"
- Check for 'isoracle' consistent with getNextId check
- removed check for system seq, always using "for update"