Msequence Table Locked in oracle

2010-01-18
2013-05-02
  • Asif Iqbal Syed

    Asif Iqbal Syed - 2010-01-18

    Hello,
    Our compiere version is 2.6.1 and database is oracle standard 10g 1. We have been getting Msequence table locked quite often. System is running in production now. Client is really unhappy for this since the whole system is locked up.

    Any help witll be very much appreciable.

     
  • Stephan Keller

    Stephan Keller - 2010-01-19

    Try this:

    In Msequence.getdocumentNo (two of them) setup trxName = null at the beginning.

     
  • Asif Iqbal Syed

    Asif Iqbal Syed - 2010-01-21

    Thanks a lot for your response. We have some modifcation in MSequence.java file. Although this table lock happens periodically like every after 10 to 15 days.

    I would like to share with you about my development. Our client want prefix + system date time as the document no.
    Therefore I modified the both getdocumentno function

    public static synchronized String getDocumentNo (int AD_Client_ID, String TableName, String trxName)
        {
            if (TableName == null || TableName.length() == 0)
                throw new IllegalArgumentException("TableName missing");
    
            //  Check CompiereSys
            boolean compiereSys = Ini.isPropertyBool(Ini.P_COMPIERESYS);
            if (compiereSys && AD_Client_ID > 11)
                compiereSys = false;
            //
            if (CLogMgt.isLevel(LOGLEVEL))
                s_log.log(LOGLEVEL, TableName + " - CompiereSys=" + compiereSys  + " [" + trxName + "]");
            // Requirement No : R2
            String selectSQL = "SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, AD_Sequence_ID, SequenceDigit "
                + "FROM AD_Sequence "
                + "WHERE Name=?"
                //jz fix duplicated nextID  + " AND AD_Client_ID IN (0,?)"
                + " AND AD_Client_ID = ?"
                + " AND IsActive='Y' AND IsTableID='N' AND IsAutoSequence='Y' ";
            if (DB.isOracle())
                selectSQL += " ORDER BY AD_Client_ID DESC ";
            selectSQL +=  "FOR UPDATE";
            Connection conn = null;
            PreparedStatement pstmt = null;
            trxName = null;
            Trx trx = trxName == null ? null : Trx.get(trxName, true);
    
            //
            int AD_Sequence_ID = 0;
            int incrementNo = 0;
            int next = -1;
            int digit = 0; // Requiement No : R2
            String prefix = "";
            String suffix = "";
            try
            {
                if (trx != null)
                    conn = trx.getConnection();
                else
                    conn = DB.getConnectionID();
                //  Error
                if (conn == null)
                    return null;
                //
                pstmt = conn.prepareStatement(selectSQL,
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
                pstmt.setString(1, PREFIX_DOCSEQ + TableName);
                pstmt.setInt(2, AD_Client_ID);
                //
                ResultSet rs = pstmt.executeQuery();
            //  s_log.fine("AC=" + conn.getAutoCommit() + " -Iso=" + conn.getTransactionIsolation() 
            //      + " - Type=" + pstmt.getResultSetType() + " - Concur=" + pstmt.getResultSetConcurrency());
                if (rs.next())
                {
                    AD_Sequence_ID = rs.getInt(6);
                    [b]prefix = replaceDateVariable_s(rs.getString(4)); // Requirement No : R1
                    suffix = replaceDateVariable_s(rs.getString(5)); // Requirement No : R1[/b]
                    incrementNo = rs.getInt(3);
                    digit = rs.getInt(7);
                    if (USE_PROCEDURE)
                    {
                        next = nextID(conn, AD_Sequence_ID, compiereSys);
                    }
                    else
                    {
                        if (compiereSys)
                        {
                            next = rs.getInt(2);
                            rs.updateInt(2, next + incrementNo);
                        }
                        else
                        {
                            next = rs.getInt(1);
                            rs.updateInt(1, next + incrementNo);
                        }
                        rs.updateRow();
                    }
                }
                else
                {
                    s_log.warning ("(Table) - no record found - " + TableName);
                    MSequence seq = new MSequence (Env.getCtx(), AD_Client_ID, TableName, null);
                    next = seq.getNextID();
                    seq.save();
                }
                rs.close();
                pstmt.close();
                pstmt = null;
                //  Commit
                if (trx == null)
                {
                    conn.commit();
                //  conn.close();           keep open
                }
                conn = null;
            }
            catch (Exception e)
            {
                s_log.log(Level.SEVERE, "(Table) [" + trxName + "]", e);
                next = -2;
            }
            //  Finish
            try
            {
                if (pstmt != null)
                    pstmt.close();
                pstmt = null;
            //  if (conn != null && trx == null)    keep open
            //      conn.close();
                conn = null;
            }
            catch (Exception e)
            {
                s_log.log(Level.SEVERE, "(Table) - finish", e);
                pstmt = null;
            }
            //  Error
            if (next < 0)
                return null;
    
            //  create DocumentNo
            StringBuffer doc = new StringBuffer();
            if (prefix != null && prefix.length() > 0)
                doc.append(prefix);
    
            /* Requirement No : R2 */ 
            if(digit > 0){
                StringBuffer fmt = new StringBuffer();
                for (int i=0;i<digit;i++){
                    fmt.append("0");
                }
                java.text.DecimalFormat dmfmt = new java.text.DecimalFormat(fmt.toString()); 
                doc.append(dmfmt.format(next));
            }
            else{
                doc.append(next);
            }
            /* End */
            if (suffix != null && suffix.length() > 0)
                doc.append(suffix);
            String documentNo = doc.toString();
            s_log.finer (documentNo + " (" + incrementNo + ")" 
                    + " - Table=" + TableName + " [" + trx + "]");
            return documentNo;
        }   //  getDocumentNo
    

    and every document related file I added code inside beoresave function. Following is the example from MInOut.java file.

    if(getDocumentNo() == null)
                setDocumentNo(MSequence.getDocumentNo(getC_DocType_ID(), null));
    
            // Check Duplicate
            String sql = "SELECT 1 FROM M_InOut WHERE lower(DocumentNo) = lower('"+getDocumentNo()+"') AND AD_Client_ID =? AND DocStatus IN ('CO','IP','DR') ";
            if(!newRecord)
                sql = sql +"AND M_InOut_ID <> "+getM_InOut_ID();
    
            int ret = DB.getSQLValue(get_TrxName(), sql, getAD_Client_ID());
            if(ret > 0){
                m_processMsg = "Document No : "+getDocumentNo()+" Is Already In The System";
                log.saveError("Cannot Save", m_processMsg);
                return false;
            }
    

    I have no idea yet what can be the root cause for ad_sequence table lock.

    I would like to deploy your code  trxName=null  on your next deployment. Just to make sure, this code change may affect the production or not.

    Thanks again. Look forward to hearing you soon.

     
  • Asif Iqbal Syed

    Asif Iqbal Syed - 2010-01-21

    /
    * Requirement No : R1
    * Replace "${java_date_format}" in prefix and suffix setup by current date
    * @param text String
    * @return String

      ex. String text= replaceDateVariable_s("PR$yy");

                 text ==> PR51 (if current date is 2008/05/22) 
    */
    public static String replaceDateVariable_s(String text){
    String ret ="";
    if(text !=null){
    try{
    int i = text.indexOf("$");
    String format= text.substring(i+1);
    ret = text.substring(0,i);
    if(i!=-1){
    DateFormat formatter ;
    Date date = new Date() ;   
    formatter = new SimpleDateFormat(format,new Locale("th", "TH"));
    String s = formatter.format(date);
    ret = ret+s;
    }
    else{
    return text;
    }
    }catch(Exception ex){
    s_log.finer ("Exception In MSequence.replaceDateVariable_s Text :"+text);
    return text;
    }
    }
    return ret;
    }

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks