locking problems when using multiple Inner Joins (ASA9 database)

Hello Experts,

When the database reads records (even in explicit read only mode) I get locks.
eg: SELECT article_id from article where article_id IN( SELECT distinct article.article_id from article Inner Join article_settings as settings0 ON (article.article_id = settings0.article_id) Inner Join article_settings as settings1 ON (settings0.article_id = settings1.article_id) where ( settings0.setting like 'mainsite' and settings0.setting_value like '8' ) AND ( settings1.setting like 'site' and settings1.setting_value like '001' ) AND status in (0) AND type in (309) ) ORDER BY article_name asc FOR READ ONLY
I notice that a lock stays for more than approximately 30 seconds. This is not possible. Records can not be locked that long since most of the saves are very short.

This used to work, but now it doesn't anymore.
Also one year ago I had locking problems with multiple Inner Joins.
SELECT Persoon.input_date,Persoon.modified_date,Persoon.naam,Persoon.vnaam,Bedrijf.id as bedrijf_id,Bedrijf.naam as bedrijfsnaam,Persoon.adres,Persoon.postcode,Persoon.gemeente,Toegang.id as toegang_id, Toegang.userid as email,Toegang.paswoord,Persoon.status,StartersDagSessie.aantal as sessieAantal,StartersDagAdviseur.aantal as adviseurAantal,persoonStarters.gestart,persoonStarters.sectordetail,persoonStarters.leeftijdscategorie,ISNULL(NieuwsBrief.unsubscribed,1) as unsubscribed,NieuwsBrief.id as nieuwsbrief_id FROM Persoon Inner Join Bedrijf on (Persoon.bedrijf_id=Bedrijf.id) Inner Join Toegang on (Persoon.id = Toegang.persoon_id) Left outer join StartersDagSessie on (Persoon.id=StartersDagSessie.persoon_id) Left outer join StartersDagAdviseur on (Persoon.id=StartersDagAdviseur.persoon_id) Left outer join persoonStarters on (Persoon.id=persoonStarters.persoon_id) Left outer join Nieuwsbrief on (Persoon.id=Nieuwsbrief.persoon_id) WHERE Persoon.id =? and (Nieuwsbrief.doelgroep = 1 OR NieuwsBrief.doelgroep = NULL)
I solved it using multiple select statements and combining the result.

Important note. There is only a problem on the production server which is a Linux Red Hat Enterprise 3 AS. There are less locking problems on the developmentserver which is a Windows2003 (for the same workload).

Can some one please help me?


Database: Adaptiver Server Anywhere Network Server Version 9.02.3219
JDBC-connection --> JConnect5.5
      <resource-ref>
            <res-ref-name>jdbc/xxxx</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <init-param driver-name="com.sybase.jdbc2.jdbc.SybDriver"/>
            <init-param url="jdbc:sybase:Tds:localhost:2638/xxxx"/>
            <init-param user="xxxx"/>
            <init-param password="xxxx"/>
            <init-param max-connections="50"/>
            <init-param max-idle-time="30s"/>
      </resource-ref>

Table structure
CREATE TABLE "DBA"."article"
(
      "article_id"                integer NOT NULL DEFAULT autoincrement ,
      "article_name"              varchar(255) NULL ,
      "status"                    integer NOT NULL DEFAULT 1 ,
      "input_date"                datetime NOT NULL DEFAULT current timestamp ,
      "modified_date"             datetime NOT NULL DEFAULT timestamp ,
      "input_by"                  integer NOT NULL DEFAULT 0 ,
      "modified_by"               integer NOT NULL DEFAULT 0 ,
      "start_date"                datetime NOT NULL DEFAULT current timestamp ,
      "stop_date"                 datetime NULL ,
      "type"                    integer NOT NULL DEFAULT 0 ,
      "scope"                   integer NOT NULL DEFAULT 0 ,
       PRIMARY KEY ("article_id"),
      
)
Number of rows: 175.000 (approximate)
CREATE TABLE "DBA"."article_settings"
(
      "article_id"                integer NOT NULL ,
      "setting"                   varchar(250) NOT NULL ,
      "setting_value"             long varchar NOT NULL ,
       PRIMARY KEY ("article_id", "setting"),
      
)
Number of rows: 1.500.000 (approximate)

Select example:
      public Vector getResult(){
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            Vector result = new Vector();
            boolean first = true;
            try{
                  conn = ConnectionHandler.getConnection(); // non-transactional connection
                  StringBuffer select =  new StringBuffer("SELECT article_id .......");
                  .....  //make the select string
                  stmt = conn.createStatement();
                  rs = stmt.executeQuery(select.toString());
                  while (rs.next()){
                        result.add(new Integer(rs.getInt(1))); //always article_id
                  }
                  return result;
            } catch (SQLException e) {
            throw new ConnectionException(e);
        } finally {
            ConnectionHandler.close(conn);
        }
      }

Insert or Update example:
    public boolean save() {
        String select = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = ConnectionHandler.getConnection(true); //get a transactional connection
            if (articleId == 0) {
                // generate new
                select = "INSERT INTO Article ( article_name,      status,      start_date,      stop_date,input_by,      type,      scope) Values (?,?,?,?,?,?,? )";
                stmt = conn.prepareStatement(select);
                stmt.clearParameters();
                stmt.setString(1, articleName);
                .....
                stmt.setInt(7, scope);
                int result = stmt.executeUpdate();
                Statement autoinc = conn.createStatement();
                rs = autoinc.executeQuery("select @@identity");
                if (rs.next()) articleId = rs.getInt(1);
            } else {
                select = "UPDATE article set article_name=?,      status=?,      start_date=?,      stop_date=?,      modified_by=?, modified_date=?,      type=?,      scope=?  WHERE article_id = ?";
                stmt = conn.prepareStatement(select);
                stmt.clearParameters();
                stmt.setString(1, articleName);
                ....
                stmt.setInt(9, articleId);
                int result = stmt.executeUpdate();
            }
            stmt = conn.prepareStatement("delete from article_settings where article_id = ? and setting = ?");
            Enumeration enum = deleted.elements(); // only deleted
            while (enum.hasMoreElements()) {
                String setting = (String) enum.nextElement();
                stmt.clearParameters();
                stmt.setInt(1, articleId);
                stmt.setString(2, setting);
                stmt.execute();
            }
            CallableStatement cstmt = conn.prepareCall("{call sp_set_article_setting(?,?,?)}");
            enum = settings.propertyNames(); // only changed
            while (enum.hasMoreElements()) {
                String setting = (String) enum.nextElement();
                String value = settings.getProperty(setting);
                cstmt.clearParameters();
                cstmt.setInt(1, articleId);
                cstmt.setString(2, setting);
                cstmt.setString(3, value);
                cstmt.execute();
            }
            conn.commit(); //commit the transaction
            return true;
        } catch (SQLException e) {
            throw new ConnectionException(e); //an explicit conn.rollback() is not required here because we close the connection
        } finally {
            ConnectionHandler.close(conn);
        }
    }