2 Replies Latest reply on Jan 13, 2005 10:02 AM by Simon Godden

    Entity beans broken because isolation level specified on the

    Simon Godden Newbie

      I am looking for people to shoot me down here :-)

      With this post, bear in mind the following:
      * I need to be able to deploy my code into different app servers (JBoss and Websphere at least).
      * We have bid for projects with up to 30,000 users in one time zone ( we may have relatively high concurrency).

      With entity beans, I can only specify the locking policy at the entity level. There is no runtime 'Session API' where I can say 'now I wish to exclusively lock that entity' at a certain point in a particular transaction.

      This leads me to believe that entity beans are broken as a general persistence solution due to the following scenario (which obviously may not occur for everyone, rendering entity beans a viable solution for them):

      (This scenario does not happen often, but when it does it must succeed and leave the database in a consistent state)

      TX1 wishes to read the 5 order lines for a particular order, and verify that the line totals equal the header totals.

      TX2 wishes to update the line total on lines 1 and 3:

      If I run at a READ_COMMITTED isolation level, then the following could occur:

      * TX1 reads row 1, with no lock
      * TX1 reads row 2, with no lock
      * TX2 commits changes to rows 1 and 3
      * TX1 reads row 3, with no lock

      TX1 is now working with an inconsistent set of data. It requires REPEATABLE_READ isolation level or better, in which case the following would occur:

      * TX1 reads row 1, and obtains at least a shared read lock
      * TX1 reads row 2, and obtains at least a shared read lock
      * TX2 attempts to upgrade its read lock to a write lock on row 1 and has to wait
      * TX1 finishes its work
      * TX2 promotes to a write lock and finishes its work

      Both transactions have now finished and worked on consistent data.

      But now come(s) my problem(s):

      1) if I specify my isolation level at the entity level, I am forcing at least REPEATABLE_READ on all transactions whether they need it or not. This will cause lock waits when they are not necessary, and reduce the throughput of my application.

      2) Worse, Oracle does not support read locks(!). Therefore an update lock would be required, the equivalent of forcing TRANSACTION_SERIALIZABLE on all transactions, even though very few need it. This is not viable.

      This leads me to conclude that we need to look at using Hibernate instead of entity beans, where we can explicitly set the lock mode at any point in a transaction.

      Specification of locking policy at entity level is insufficient for a general persistence solution (although it will be sufficient for many specific scenarios).

      What do people think?

      Shoot me down.