details: https://code.openbravo.com/erp/devel/pi/rev/3ff8ce5616bb
changeset: 35180:3ff8ce5616bb
user: Asier Lostalé <asier.lostale <at> openbravo.com>
date: Fri Nov 30 11:55:27 2018 +0100
summary: fixed bug 39717: unnecesary contention to create new documents in UI
AD_Sequence_DocType acquired a "FOR UPDATE" lock whenever it was invoked
regardless
whether it updated the sequence number or only returned a number proposal. The
latter is the the case of proposing document numbers while creating from UI,
which was forced to be sequentially executed due to this lock.
AD_Sequence_DocType behavior has been changed to:
1. If AD_Sequence_DocType is not going to finally update AD_Sequence, no lock
is acquired.
2. Acquire the lock only if AD_Sequence is updated. Even this lock has been
relaxed in PostgreSQL from FOR UPDATE to FOR NO KEY UPDATE, by doing the
update (which aquires the lock) before doing the select, which now needs
to know in which case we are in order to properly calculate the actual
number.
The same applies to AD_Sequence_Doc.
diffstat:
src-db/database/model/functions/AD_SEQUENCE_DOC.xml | 14 +++++++-------
src-db/database/model/functions/AD_SEQUENCE_DOCTYPE.xml | 14 +++++++-------
2 files changed, 14 insertions(+), 14 deletions(-)
diffs (77 lines):
diff -r d45f2b8d352e -r 3ff8ce5616bb
src-db/database/model/functions/AD_SEQUENCE_DOC.xml
--- a/src-db/database/model/functions/AD_SEQUENCE_DOC.xml Fri Nov 30
15:14:27 2018 +0100
+++ b/src-db/database/model/functions/AD_SEQUENCE_DOC.xml Fri Nov 30
11:55:27 2018 +0100
@@ -28,7 +28,7 @@
* parts created by ComPiere are Copyright (C) ComPiere, Inc.;
* All Rights Reserved.
* Contributor(s): Openbravo SLU
- * Contributions are Copyright (C) 2001-2012 Openbravo, S.L.U.
+ * Contributions are Copyright (C) 2001-2018 Openbravo, S.L.U.
*
* Specifically, this derivative work is based upon the following Compiere
* file and version.
@@ -44,19 +44,19 @@
v_Prefix VARCHAR2(30) ;
v_Suffix VARCHAR2(30) ;
BEGIN
- SELECT CurrentNext, Prefix, Suffix
- INTO v_NextNo, v_Prefix, v_Suffix
- FROM AD_Sequence
- WHERE Name=p_SequenceName AND IsActive='Y' AND IsTableID='N' AND
IsAutoSequence='Y' AND AD_Client_ID=p_AD_Client_ID FOR UPDATE OF CurrentNext;
-
IF p_Update_Next='Y' THEN
UPDATE AD_Sequence
SET CurrentNext=CurrentNext + IncrementNo, Updated=now()
WHERE Name=p_SequenceName
AND ad_client_id=p_ad_client_id;
END IF;
+
+ SELECT CurrentNext - (CASE WHEN p_Update_Next='Y' THEN IncrementNo ELSE 0
END), Prefix, Suffix
+ INTO v_NextNo, v_Prefix, v_Suffix
+ FROM AD_Sequence
+ WHERE Name=p_SequenceName AND IsActive='Y' AND IsTableID='N' AND
IsAutoSequence='Y' AND AD_Client_ID=p_AD_Client_ID;
+
p_DocumentNo:=COALESCE(v_Prefix, '') || v_NextNo || COALESCE(v_Suffix, '') ;
-
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, '@DocumentSequenceNotFound@' ||
p_SequenceName) ;
diff -r d45f2b8d352e -r 3ff8ce5616bb
src-db/database/model/functions/AD_SEQUENCE_DOCTYPE.xml
--- a/src-db/database/model/functions/AD_SEQUENCE_DOCTYPE.xml Fri Nov 30
15:14:27 2018 +0100
+++ b/src-db/database/model/functions/AD_SEQUENCE_DOCTYPE.xml Fri Nov 30
11:55:27 2018 +0100
@@ -28,7 +28,7 @@
* parts created by ComPiere are Copyright (C) ComPiere, Inc.;
* All Rights Reserved.
* Contributor(s): Openbravo SLU
- * Contributions are Copyright (C) 2001-2009 Openbravo, S.L.U.
+ * Contributions are Copyright (C) 2001-2018 Openbravo, S.L.U.
*
* Specifically, this derivative work is based upon the following Compiere
* file and version.
@@ -64,20 +64,18 @@
DBMS_OUTPUT.PUT_LINE('[AD_Sequence_DocType: not found - C_DocType_ID=' ||
p_DocType_ID || ']') ;
RETURN;
END IF;
--- Get the numbers
-SELECT s.CurrentNext, s.Prefix, s.Suffix
-INTO v_NextNo, v_Prefix, v_Suffix
-FROM AD_Sequence s
-WHERE s.AD_Sequence_ID = v_Sequence_ID AND s.IsActive='Y' AND
s.IsTableID='N' AND s.IsAutoSequence='Y' FOR UPDATE OF CurrentNext;
-
IF p_Update_Next='Y' THEN
UPDATE AD_Sequence
SET CurrentNext=CurrentNext + IncrementNo
WHERE AD_Sequence_ID=v_Sequence_ID;
END IF;
+
+ SELECT s.CurrentNext - (CASE WHEN p_Update_Next='Y' THEN IncrementNo ELSE 0
END), s.Prefix, s.Suffix
+ INTO v_NextNo, v_Prefix, v_Suffix
+ FROM AD_Sequence s
+ WHERE s.AD_Sequence_ID = v_Sequence_ID AND s.IsActive='Y' AND
s.IsTableID='N' AND s.IsAutoSequence='Y';
+
p_DocumentNo:=COALESCE(v_Prefix, '') || v_NextNo || COALESCE(v_Suffix, '') ;
-
--- DBMS_OUTPUT.PUT_LINE(p_DocumentNo);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, '@DocumentTypeSequenceNotFound@') ;
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
