details: https://code.openbravo.com/erp/devel/pi/rev/708c5c4d51a0 changeset: 35182:708c5c4d51a0 user: Sandra Huguet <sandra.huguet <at> openbravo.com> date: Tue Dec 04 15:36:12 2018 +0100 summary: Fixed issue 39672 reduce lock and calculate the current balance at the end of the process
details: https://code.openbravo.com/erp/devel/pi/rev/18fd96916ab5 changeset: 35183:18fd96916ab5 user: Sandra Huguet <sandra.huguet <at> openbravo.com> date: Tue Dec 04 15:42:37 2018 +0100 summary: Fixed issue 39755 added in OBDAL lockForNoKeyUpdate new lockForNoKeyUpdate method to allow the use "FOR NO KEY UPDATE", because "FOR UPDATE" in PostgreSQL is too aggressive. details: https://code.openbravo.com/erp/devel/pi/rev/26214ce6e789 changeset: 35184:26214ce6e789 user: Sandra Huguet <sandra.huguet <at> openbravo.com> date: Tue Dec 04 15:47:59 2018 +0100 summary: related to issue 39672 use lockForNoKeyUpdate instead of lockFinAccount Use lockForNoKeyUpdate instead of use lockFinAccount in which a "FOR UPDATE" is done and is too agressive. diffstat: modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/FIN_TransactionProcess.java | 50 ++++------ src/org/openbravo/dal/service/OBDal.java | 47 +++++++++ 2 files changed, 67 insertions(+), 30 deletions(-) diffs (179 lines): diff -r a0e0c8e52bea -r 26214ce6e789 modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/FIN_TransactionProcess.java --- a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/FIN_TransactionProcess.java Wed Nov 28 10:20:49 2018 +0530 +++ b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/FIN_TransactionProcess.java Tue Dec 04 15:47:59 2018 +0100 @@ -22,10 +22,9 @@ import java.util.List; import org.apache.commons.lang.StringUtils; -import org.hibernate.LockOptions; -import org.hibernate.Session; +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; import org.hibernate.criterion.Restrictions; -import org.hibernate.query.Query; import org.openbravo.advpaymentmngt.APRM_FinaccTransactionV; import org.openbravo.advpaymentmngt.dao.AdvPaymentMngtDao; import org.openbravo.advpaymentmngt.utility.FIN_Utility; @@ -49,8 +48,6 @@ import org.openbravo.model.financialmgmt.payment.FIN_PaymentScheduleDetail; import org.openbravo.model.financialmgmt.payment.FinAccPaymentMethod; import org.openbravo.scheduling.ProcessBundle; -import org.apache.logging.log4j.Logger; -import org.apache.logging.log4j.LogManager; public class FIN_TransactionProcess implements org.openbravo.scheduling.Process { /** Transaction type - Financial Account */ @@ -115,10 +112,6 @@ msg = OBMessageUtils.messageBD("PeriodNotAvailable"); throw new OBException(msg); } - - final FIN_FinancialAccount financialAccount = lockFinAccount(transaction.getAccount()); - financialAccount.setCurrentBalance(financialAccount.getCurrentBalance().add( - transaction.getDepositAmount().subtract(transaction.getPaymentAmount()))); transaction.setProcessed(true); FIN_Payment payment = transaction.getFinPayment(); if (payment != null) { @@ -127,8 +120,8 @@ } AdvPaymentMngtDao dao = new AdvPaymentMngtDao(); if (StringUtils.equals(payment.getStatus(), dao.PAYMENT_STATUS_AWAITING_EXECUTION) - && dao.isAutomatedExecutionPayment(financialAccount, payment.getPaymentMethod(), - payment.isReceipt())) { + && dao.isAutomatedExecutionPayment(transaction.getAccount(), + payment.getPaymentMethod(), payment.isReceipt())) { msg = OBMessageUtils.messageBD("APRM_AutomaticExecutionProcess"); throw new OBException(msg); } @@ -176,8 +169,15 @@ && getConversionRateDocument(transaction).size() == 0) { insertConversionRateDocument(transaction); } + + String financialAccountId = OBDal.getInstance().lockForNoKeyUpdate( + transaction.getAccount().getEntity(), transaction.getAccount().getId()); + FIN_FinancialAccount financialAccount = OBDal.getInstance().get(FIN_FinancialAccount.class, + financialAccountId); + + financialAccount.setCurrentBalance(financialAccount.getCurrentBalance().add( + transaction.getDepositAmount().subtract(transaction.getPaymentAmount()))); transaction.setAprmProcessed("R"); - OBDal.getInstance().save(financialAccount); OBDal.getInstance().save(transaction); } else if (strAction.equals("R") && transaction.isProcessed()) { @@ -221,10 +221,7 @@ OBContext.restorePreviousMode(); } transaction.setProcessed(false); - final FIN_FinancialAccount financialAccount = lockFinAccount(transaction.getAccount()); - financialAccount.setCurrentBalance(financialAccount.getCurrentBalance() - .subtract(transaction.getDepositAmount()).add(transaction.getPaymentAmount())); - OBDal.getInstance().save(financialAccount); + OBDal.getInstance().save(transaction); if (payment != null) { Boolean invoicePaidold = false; @@ -247,6 +244,13 @@ transaction.setStatus(transaction.getDepositAmount().compareTo( transaction.getPaymentAmount()) > 0 ? "RPR" : "PPM"); } + String financialAccountId = OBDal.getInstance().lockForNoKeyUpdate( + transaction.getAccount().getEntity(), transaction.getAccount().getId()); + FIN_FinancialAccount financialAccount = OBDal.getInstance().get(FIN_FinancialAccount.class, + financialAccountId); + + financialAccount.setCurrentBalance(financialAccount.getCurrentBalance() + .subtract(transaction.getDepositAmount()).add(transaction.getPaymentAmount())); transaction.setAprmProcessed("P"); OBDal.getInstance().save(transaction); } @@ -368,18 +372,4 @@ } return confirmation; } - - private static FIN_FinancialAccount lockFinAccount(FIN_FinancialAccount account) { - StringBuilder queryStr = new StringBuilder( - "select a from FIN_Financial_Account a where id = :id"); - final Session session = OBDal.getInstance().getSession(); - final Query<FIN_FinancialAccount> query = session.createQuery(queryStr.toString(), - FIN_FinancialAccount.class); - query.setParameter("id", account.getId()); - query.setMaxResults(1); - query.setLockOptions(LockOptions.UPGRADE); - OBDal.getInstance().getSession().evict(account); - return query.uniqueResult(); - } - } diff -r a0e0c8e52bea -r 26214ce6e789 src/org/openbravo/dal/service/OBDal.java --- a/src/org/openbravo/dal/service/OBDal.java Wed Nov 28 10:20:49 2018 +0530 +++ b/src/org/openbravo/dal/service/OBDal.java Tue Dec 04 15:47:59 2018 +0100 @@ -21,6 +21,8 @@ import java.io.Serializable; import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; @@ -51,6 +53,7 @@ import org.openbravo.database.SessionInfo; import org.openbravo.model.ad.system.Client; import org.openbravo.model.common.enterprise.Organization; +import org.openbravo.service.db.DalConnectionProvider; /** * The OBDal class offers the main external access to the Data Access Layer. The variety of data @@ -726,4 +729,48 @@ final Entity e = ModelProvider.getInstance().getEntity(entityName); OBContext.getOBContext().getEntityAccessChecker().checkReadable(e); } + + /** + * Creates a lock for no key update + * + * @param entity + * the type to create the query for + * @param id + * identifier of the record + * @return id + */ + public String lockForNoKeyUpdate(Entity entity, String id) { + final String RDBMS = new DalConnectionProvider(false).getRDBMS(); + String strSQL; + if ("ORACLE".equals(RDBMS)) { + strSQL = "SELECT * FROM " + entity.getTableName() + " WHERE " + entity.getTableName() + + "_id =? FOR UPDATE"; + } else { + strSQL = "SELECT * FROM " + entity.getTableName() + " WHERE " + entity.getTableName() + + "_id =? FOR NO KEY UPDATE"; + } + PreparedStatement sqlQuery = null; + ResultSet rs = null; + try { + sqlQuery = new DalConnectionProvider(false).getPreparedStatement(strSQL); + sqlQuery.setString(1, id); + sqlQuery.execute(); + sqlQuery.setMaxRows(1); + rs = sqlQuery.getResultSet(); + while (rs.next()) { + return rs.getString(1); + } + } catch (Exception e) { + log.error("Error when executing query", e); + } finally { + try { + if (sqlQuery != null) { + sqlQuery.close(); + } + } catch (Exception e) { + log.error("Error when closing statement", e); + } + } + return null; + } } \ No newline at end of file _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
