Discussions

EJB design: Are container managed transactions as good as they should be?

  1. Well folks,

    A little strikeback from container managed transactions (CMT) antagonist.

    Let's consider the following typical situation. I need to perform the following set of simple operations in one transaction:

    1. Get balance from the account.
    2. Substract an amount from the balance.
    3. Check if balance is non-zero.
    4. If not zero, substract the amount from the balance, otherwise rollback.

    I have the following meta-SQL to deal with:

    1. BEGIN ; --- start transaction here
    2. SELECT balance FROM accounts WHERE [blah-blah-blah]
    3. value=balance-amount
    4. if (value!=0) UPDATE balance SET balance=value ELSE ROLLBACK
    5. COMMIT

    Notice, that in 4), we I intend on using "SET balance=value" construction instead of more obvious "SET balance=balance-amount"! Otherwise, the whole sense of this topic would belost!

    You can easily notice, that between statement 2) and 4) another transaction that updates the balance could be easily commited and, in this case, the whole balance would be screwd up.

    If I am dealing with appserver's container managed transactions logic, the only remedy in this situation will be a SERIALIZABLE transaction isolation level (if we deal with non-Oracle database, only this TI level will guarantee the anticipated results, this is the only TI that guarantees transaction-scope transaction set consistency) - in this case we will be working on a data image during the whole transaction duration. However, in case of Oracle this kind of isolation level has a lot of drawbacks - when we update the data that were already changed by another transaction - we will get "can't serialize to this instance" error and we will be made to repeat the transaction. We should also consider that SERIALIZABLE level will cause a huge performance drawback in all the databases, so most of the databases have other ways to sort out this kind of situation.

    For example, for Oracle, you can use the following statement as the remedy:

    LOCK TABLE accounts IN ROW EXCLUSIVE MODE

    However, when you stuck in CMT, I am not sure that your appserver will create this statement for you (I am not even thinking about the hell you will get with CMP).

    So, why go with CMT and CMP, if in a very simple cases you're deep in trouble?

    Correct me if I am wrong anywhere.

    Alex

    Threaded Messages (5)

  2. A little correction[ Go to top ]

    Forgot,

    There is also another way to sort out this problem in Oracle: you can use SELECT FOR UPDATE instead of SELECT statement. However, if I stick with other databases or with CMP, will this issue still be actual?

    Alex
  3. A little correction[ Go to top ]

    The other way you could do this is with optimistic locking

    rather than saying

    1. BEGIN ; --- start transaction here
    2. SELECT balance FROM accounts WHERE [blah-blah-blah]
    3. value=balance-amount
    4. if (value!=0) UPDATE balance SET balance=value ELSE ROLLBACK
    5. COMMIT

    you could say

    1. BEGIN ; --- start transaction here
    2. SELECT balance FROM accounts WHERE [blah-blah-blah]
    2a) remembered_balance = balance;
    3. value=remembered_balance-amount
    4. if (value!=0) numberofUpdates=UPDATE balance SET balance=value where balance=remembered_balance
    5. if (numberOfUpdates==0) ROLLBACK ELSE COMMIT
  4. What leads you to the conclusion that transactions with SERIALIZABLE isolation are more expensive than exclusive locks? As far as I know, they aren't. If they were, they would be implemented using exclusive locks. The reason Oracle uses a version numbering, optimistic concurrency control algorithm is that it is usually more efficient than exclusive locking. However, if you do want an exclusive lock, most CMP implementations allows you to either switch it on, or edit the generated SQL if you want to add a "FOR UPDATE" clause or something like that.

    Gal
  5. Reply[ Go to top ]

    Gal,

    That's exclusive ROW LOCKING. It will be much less expensive, than full exclusive mode. And another point - you don't get "can't serialize this instance" errors and restart your transactions when you use exclusive locking - less problems for a programmer.

    I am not sure which CMP implementations will allow you to switch on or off exclusive locks and how they deal with different databases. If you have examples or links, they are welcome.

    Thanks,
    Alex
  6. Reply[ Go to top ]

    Why does a Serializable transaction require more than an exclusive row lock? I still think Oracle's optimistic algorithm is pretty efficient. About rerunning the transaction, that doesn't allways makes sense. For instance, if the balance changed, shouldn't the user be given a chance to change his mind before making another withdrawal? I generally think that stopping a user when he/she is interacting with the system is a bad idea. Let the user work smoothly without being blocked by other users, and when a conflict arises let the user know.

    I believe iAS does support exclusive locks on Oracle, or at least it did last time I checked. Other containers allow you to edit the generated SQL yourself.

    Gal