Isolation level not working (Kinda urgent)

[viniciuscarvalho]

Hello there! I'm having serious problems with Isolation levels in a production application.
I don't know if it is my fault (99.999% of sure that is) or spring's + database configuration.

Here's what happening: For 0.0049% of our access, we are having cuncurrent requests to a method (probably caused by the user clicking twice in the submit).

The problem is a classic dirty_read problem:

public void registerBid(){
//we check to see if user already bid if not, we save the bid
}

now, what's happening is that Thread A starts, check if user has bid, (he does not), Thread B gets the context, check if user has the bid (not yet). Thread A takes back control, saves the bid, Thread B assumes the control, saves the Bid again.

Well, big problem for us.

Here's my transaction attributes:

<prop key="saveBid*">PROPAGATION_REQUIRED,ISOLATION_SERI ALIZABLE</prop>

I was expecting Thread B not being able to access my Service, until Thread A finishes it.

I could add a Serializable to the method, but the method access other services, and that could cause bottlenecks.

My database is Oracle 10.1.2 (I believe it supports Serializable)

Any ideas please?

[Alarmnummer]

The origin of the problem of the problem is Oracle, or to be more specific the concurrency control mechanism they use: Multi Version Concurrency Control.

Unless you lock (pessimistic) the record yourself, oracle uses an optimistic locking approach. If it used a pessimistic locking approach (like you are expecting) then you are correct that it would be impossible for the other transaction to change the record if the first record has read it.

Solutions:
1) or do a select for update (this pessimistic locks the record read)
2) or retry the transaction because you just have lost an optimistic lock.

PS:
MVCC allows for very scalable database systems.. so this is they price you have to pay for it.

PPS:
Oracle supports READ_COMMITTED AND SERIALIZED. There is no reason for them to support the other Isolation levels (READ_UNCOMMITTED, REPEATABLE_READ) because MVCC is able to 'revert' to a previous committed version of the record. So uncommited reads are not needed, because you always can revert to a committed one, and REPEATABLE_READ is not supported explicitly, because it can (for the duration of the transaction) use the 'same' view of data because it 'can travel back in time. This is something that is available at the SERIALIZED isolation level.

READ_UNCOMMITED is upgraded to READ_COMMITTED
REPEATABLE_READ is upgraded to SERIALIZED

[Marten Deinum]

If you call saveBid from within the registerBid then the the saveBid will participate in the already started transaction of registerBid.

Your transaction demarcation will only be used if the saveBid is called externally (on the proxy). Either define your transaction different (i.e. on registerBid) or make sure the saveBid gets called externally.

<prop key="registerBid*">PROPAGATION_REQUIRED,ISOLATION_SERIALIZABLE</prop>

[Alarmnummer]

....

Did not even see this one. Always make sure that transactions are atomic!

If you use seperate transactions for reading and updating, it could lead to isolaton problems.

Database systems 101 imho.

[viniciuscarvalho]

Thanks for the help folks, I'll check it, but the read and update are already atomic (aka they are inside the same transaction)
//Has the SERIALIZABLE attribute
public registerBid(){
readBid()
saveBid()
}

Best Regards

[Marten Deinum]

Thanks for the help folks, I'll check it, but the read and update are already atomic (aka they are inside the same transaction)
//Has the SERIALIZABLE attribute
public registerBid(){
readBid()
saveBid()
}

This means the participate in the transaction started by registerBid, the transaction demarcation as you mentioned (for saveBid) are happily ignored, because it is an internal call and not an external call. It is thus by passing the proxy and ignoring your SERIALIZABLE setting.

Edit:
Oops missed your notice that registerBid also has the SERIALIZABLE attribute.

[Alarmnummer]

I think something else must be going wrong.

If you are using a single transaction for the read and the update, and the transaction is serialized and you are using oracle (without explicit locking, so not using a select for update), only 2 things can happen:

1) the transaction completes successfully because no other transaction has intervened.
2) you get an 'ORA-08177: can’t serialize access for this transaction'

So unless you get the ORA-08177 exception from Oracle, something else must be going wrong.

[viniciuscarvalho]

Hello there! Thanks for all the support folks! Really appreciate this Nice to have so many great developers willing to help

Well, We've inspected the code inside the production server. Transaction Isolation was set to default, not reflecting our test environment (really big meeting in few minutes about that ...).

Also, I'm a bit afraid of using custom isolation levels, might be wrong, but I recall a few posts back ago (we are using 1.2.8) regarding Spring 1.2.8 + Hibernate 3.1.x + OpenSessionInView and connection release modes.... (Not sure but I guess it should be auto).

In my test, it really worked using serializable and custom connection release, but it was a unit test, outside web context, no OSVIF...

We have another solution (that I still can not believe was not implemented by the DBA), its a nice thing called unique_constraints . I did not mention at the time, but our system allows a user to bid only once per item, then it starts a new auction (its not an ebay like system, its government stuff )

One again thanks Alarmnummer and mdeinum for your time and advices.

Best regards

Vinicius Carvalho

[al0]

Some more comments -

1. usage of serializable isolation level in production environment is strictly discouradged for scalability reasons (or rather for complete lack of scalability
2. concerning the unique constraint - it may be not bad, all such things must be enforced on database level
3. in more general situation for insertion you shall synchronize on the "parent" record by select ... for update. Even this is more scalable then serializable transactions

[Alarmnummer]

Some more comments - [LIST=1][*]usage of serializable isolation level in production environment is strictly discouradged for scalability reasons (or rather for complete lack of scalability

It depends on the database. MVCC allows for much better scalability with the SERIALIZED isolation than 'classic' approaches where real locks (table locks often) are being used (these databases are subject to lock contention and that prevent scaling)

I have no statistics to back this up, but the fact that MVCC uses some sort of optimistic locking approach to prevent isolation problems, should make it more scalable because it is subject to a lot less lock contention.