1 Reply Latest reply on Jun 2, 2006 2:31 PM by lauri

    Single UPDATE instead of SELECT, then UPDATE

    lauri
    lauri Jun 1, 2006 4:14 AM

      Good afternoon.

      Common entty bean contain a number of set and get methods. Here we have a Customer bean with field named credits.
      Also we have a Session bean named Cashier with deposit method. In general this method looks like:

      
public void deposit(Integer customerId, int value) throws RemoteException {
 try {
 CustomerLocal customer = this.customerHome.findByPrimaryKey(customerId);
 int credits = customer.getCredits()+value;
 if(credits < 0) {
 throw new OutOfCreditsBuisenessException("....");
 }
 customer.setCredits(credits);
 } catch .... {
 ///.... bla bla. catch some common exceptions
 }
}



      Invocation of this method results in two queries
      1) SELECT credits FROM customer WHERE id=?
      2) UPDATE customer SET credits=? WHERE id=?

      That is good enoght for those who are using SERIALIZABLE tx isolation level. We can not afford it with mysql here, the level leads to locks on customer table (i.e. customer spends credits and deposits it a same time) and we have to use REPETABLE_READ instead.

      Is that possible somehow to merge two queries to one like:
      UPDATE customer SET credits=credits+? WHERE id=?
      I know that it is possible by using direct connection with db. But this will mean new transaction per each deposit. Is that avoidable?

      Could it be that SERIALIZABLE tx isolation level is a weak point of mysql and we should consider migration to another db?

      Thanks for attention.

      P.s. Don't know if this forum best place for such a question. Hits where shold-I-place-the-question are welcome too :).

      • 4375 Views
      • Tags:
        • 1. Re: Single UPDATE instead of SELECT, then UPDATE
          lauri
          lauri Jun 2, 2006 2:31 PM (in response to lauri)

          The problem has been solved.
          I'd like to share the story.

          The question was going from obivious lack of J2EE and JBoss experience and understanding.
          I was absolutely sure that dataSource.getConnection() returns free connection and this free connection means new transaction.

          I've had tried to use same datasource for cmp beans and session beans. Lets say it is registered as java:jdbc/cmp in global evinonent (possibly other depoyments use java:DefaultDS or java:MySqlDS).
          I've placed a ref link to it for my Session bean Cashier. In xdoclet attributes it looks like:

          @ejb.resource-ref res-ref-name="dataSource" ref-type="javax.sql.DataSource" jndi-name="java:jdbc/cmp" res-auth="Container"
@jboss.resource-ref res-ref-name="dataSource" jndi-name="java:jdbc/cmp"

          Cashier session bean has a private field of javax.sql.DataSource type dataSource. It is being initialized in ejbCreate() 
          public void ejbCreate() throws CreateException {
 try {
 InitialContext ctx = new InitialContext();
 this.dataSource = (DataSource) ctx.lookup("java:comp/env/dataSource");
 //....
 } catch (NamingException nEx) {
 throw new RuntimeException(nEx);
 }
}

          Now dataSource field is usable.
          So deposit() method from example above has changed to 
          public void deposit(Integer customerId, int value) throws RemoteException {
 Connection conn = null;
 try {
 conn = this.dataSource.getConnection();
 PreparedStatement stmt = conn.prepareStatement("UPDATE customer SET credits=credits+? WHERE id =?");
 stmt.setInt(1, value);
 stmt.setObject(2, customerId);
 stmt.executeUpdate();
 } catch .... {
 ///.... bla bla. catch some common exceptions
 } finally {
 if(conn != null) {
 try {
 conn.close();
 } catch (SQLException sqlEx) {
 // log error
 }
 }
 }
}

          That is really simple and was hard for me to believe.
          Queries generated inside new deposit method come inside transaction which is managed by container! (I've verified it from mysql query logs). It is forbidden to use conn.commit(), conn.rollback() and conn.setAutoComint(...) methods, an exception will be thrown and whole transaction will be rolled back.

          Similar solution has been applied to many methods for local edition of the Cashier bean.

          Total application has been stress tested with REPETABLE_READ isolation level for mysql. Works perfectly.

          At least one drawback indeed exist, now sql code moved to java code (or deployment descriptors). It is not very beautiful to my humble opinion.

          P.S. Next question of curiosity. Will other containers behave in the same way? I hope it will never come to find an answer myself :).

            • Actions