How to SELECT FOR UPDATE with CMP (Oracle)
The most common database (Oracle) by default uses a scheme that does not fit into any of those isolation levels. A SELECT statement selects data at the start of the transactions, whereas a SELECT ... FOR UPDATE does something quite different. It is essential to do SELECT FOR UPDATEs before updating the row as SELECT does no lock. It's a hack that works well in practice.
1. Which isolation level is this?
2. More fundamentally, how an earth is it possible to use this scheme with CMP?! You would have to distinguish load() from loadForUpdate()! Is CMP inconsistent with Oracle?
This is a pretty big whole in the CMP spec!
[663 byte] By [
aberglas] at [2007-9-26 5:38:09]
No. thats no goes well.
Transaction serializable in Oracle uses a optimistic
concurrency system. And for update is a
pessimistic concurrency.
With optimistic: the system is faster but it can fail
With pessimistic: if doesnt fail (usually;)
You can solve the proble with many differents systems:
1. Edit the .xml descriptor files ans change the sql sentences.
And my prefer one.
2. Make a new jdbc driver that inherits from the original
oracledriver.
The new driver give u in "getConnection()" a new connection class that inherits from the original connection.
The executestatement and preparestatement adds the
string "for update" if the stattement was starting by select.
Configure your container to use the new driver.
Sad to say but entity beans do not work all the times. In the EBJ 1.1 specs they give some hint at how to use EB but it doesn't allow fine grained object (beause of the obvious performance problems) Instead they ask that we model coarse grained bean like an Order bean instead of the usual Order->OrderLine->OrderLineDetail... The way I understand it is that to update a single OrderLine with this model, you need to go through the Order bean (the coarse grained one) which might not be the fastest or cleanest approach. To this day I don't use EJB in any real-world project as it just does not work well.
I'm implementing EntityBeans BMP and I have no need to use updatable SELECTs. ejbLoad() uses a SELECT to retrieve values and ejbStore() uses an UPDATE to store them so, when takes place an updatable SELECT?
When you talk about load() and loadForUpdate, do you mean the ejbLoad() method?
Please, let me know this issue.
SELECT normally does not obtain any locks in Oracle. Worse, it actually tries to retrieve the value long ago at the start of the transaction. So any code that looks like:-
Select Value into :Value...
:value = :value + 1
Update ... set value=:value...
Is an insidious bug in Oracle.
The correct statement is
Select...for update.
