0025286: Deadlocks on the AD_SEQUENCE table

Description

ON applications with a high and an concurrent usage of processes that call to AD_SEQUENCE_DOC() and AD_SEQUENCE_DOCTYPE() procedures, deadlocks happen on the AD_SEQUENCE table.

The procedures that supply documentno values from the AD_SEQUENCE table, AD_SEQUENCE_DOC() and AD_SEQUENCE_DOCTYPE(), are
susceptible to causing deadlocks if multiple threads have long transactions that are creating multiple objects of the same type. There is also a
remote possibility of dirty reads in this procedure causing a return of non-unique values if certain race conditions hold.

Steps To Reproduce

-Several users creating documents.

Proposed Solution

1.Re-write the procedures to incorporate UPDATE cursors ( locking the record to promote uniqueness )
2.Make the procedures autonomous transactions so that the updates to the AD_SEQUENCE record are not dependent on the calling transaction

Tags

Performance

Attached Files

DocNoTest.java [^] (8,209 bytes) 2014-02-18 08:18

Relationships [ Relation Graph ] [ Dependency Graph ]

related to	defect	0025285	3.0PR14Q3	closed	AugustoMauch	Deadlocks on the C_ORDERTAX table
related to	defect	0025732	3.0PR14Q2	closed	AugustoMauch	AttributeSetInstanceValueData's selectNextSerNo and updateSerNoSequence methods perform poorly with large transaction volume

Notes
(0064309) alostale (manager) 2014-02-18 08:21	No deadlocks detected. There is contention in ad_sequence (see attached test). This behavior is correct, if there are 2 threads in parallel: -Thread 1 gets a doc no and does a long process within the same transaction -Before thread 1 finishes, thread 2 tries to get a doc no for the same document type. Thread 2 waits till thread 1 finishes before getting the doc no, this is correct and prevents empty sequences in case thread 1 rolls back.

Issue History
Date Modified	Username	Field	Change
2013-12-06 13:26	egoitz	New Issue
2013-12-06 13:26	egoitz	Assigned To	=> AugustoMauch
2013-12-06 13:26	egoitz	Modules	=> Core
2013-12-06 13:26	egoitz	Resolution time	=> 1388790000
2013-12-06 13:26	egoitz	Triggers an Emergency Pack	=> No
2013-12-06 13:26	egoitz	Issue generated from	0025285
2013-12-06 13:26	egoitz	Relationship added	related to 0025285
2013-12-17 18:40	egoitz	Tag Attached: Performance
2014-01-27 09:57	egoitz	Fixed in Version	=> 3.0MP32
2014-01-27 16:12	alostale	Fixed in Version	3.0MP32 =>
2014-01-27 16:12	alostale	Target Version	=> 3.0MP33
2014-02-14 09:39	alostale	Relationship added	related to 0025732
2014-02-18 08:18	alostale	File Added: DocNoTest.java
2014-02-18 08:21	alostale	Note Added: 0064309
2014-02-18 08:21	alostale	Status	new => closed
2014-02-18 08:21	alostale	Resolution	open => no change required