0 Replies Latest reply on Mar 21, 2006 12:34 PM by Peter Bagrij

    Using aggregates (count(*), sum(), ..) in transactions to li

    Peter Bagrij Newbie

      Hello JBoss Users,

      Our problem is the following:
      There is a process which uses some kind of resource. If the process is invoked then
      we need to check if we have enough resource to fullfill the request.
      The process is represented with an EntityBean and in our nomenclature it is called "Session".
      This resource limit is a hard limit, this means all time we have to be under the limit.

      Our environment:
      - BMP is used
      - We have two Jboss nodes in the cluster
      - The Jboss version is 4.03RC1
      - Oracle 10g is used for Data Source engine
      - The transaction isolation level is read commited
      - "Instance Per Transaction BMP EntityBean" configuration is used in entity bean

      We planned to implement a count method within the Entity bean wchich would
      give back the number of the current records (=sessions).
      A statless session bean will be used to check the limit and create
      a new session within one step.

      What happens if this two creatSession() request are processed by the two nodes in the
      cluster simultanously. The limit is set to 20 and currently we have 19 sessions in the DB.


      Node1 Node2
      ==================================================================================
      begin transaction
      getCountSession() returns 19; begin transaction
       getCountSession() returns 19;
      if (19 < 20) createSession()
       if (19 < 20) createSession()
      Inserts a new entry in the Session
       inserts a new entry in the session
      commit;
       commit;


      Now we have 21 sessions, and that is not so good.

      What kind of sollutions can you imagine?

      Sollution 1)
      change the transaction isolation level to serialized into the DataSource file.
      This would cause a performance bottleneck.
      +theoreticaly OK,
      -performance problem can occur, if serialize all the transactions we have.

      Sollution 2)
      Before the commit a new getCountSession() should be executed. If the result is
      greater then limit the whole operation can be rolled back.
      +theoretically OK,
      -It can happen, that both transaction would roll back.

      Sollution 3)
      Track the number of the sessions using a helper entity bean. This bean would store
      the actual number of sessions. Because we have only one therefore DB lock would prevent
      the other node to access, till the first node finish.
      +Theoreticaly ok,
      -new tables are required
      -slower?

      sollution 4)
      Somehow change the transaction isolation level of the connection till the createSession() method runs.
      I am not sure that it can work at all.

      Do you have any idea?
      Best regards: pppeter