I cautht an Exception about READ_COMMITTED and SERIALIZABLE :(

[kenees]

Spring : spring-framework-2.0-rc3
DB : Oracle 9i2
Hibernate : hibernate-3.2.0.ga
DataSource : c3p0-0.9.0.jar

I caught one Exception ;(

Caused by: java.sql.SQLException: 仅 READ_COMMITTED 和 SERIALIZABLE 是有效的事务处理级
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:162)

There is some thing in Chinese which means "only READ_COMMITTED and SERIALIZABLE is available transaction level"

When I use the following configuration to describe my NameMatchTransactionAttributeSource instance, it cause one exception, I don't know whether it is an Oracle error that catched by Spring or some thing else (maybe datasource?), if it was an Oracle error, can I any body tell me why this configuration cause this error, THANK YOU VERY MUCH.

	<bean id="transactionAttributeSource"
		class="org.springframework.transaction.interceptor.NameMatchTransactionAttributeSource">
		<property name="properties">
			<props>
				<prop key="*">
					PROPAGATION_REQUIRES_NEW,ISOLATION_REPEATABLE_READ				</prop>
			</props>
		</property>
	</bean>

With the following configuration every thing is OK, this is what puzzled me very much ;(

	<bean id="transactionAttributeSource"
		class="org.springframework.transaction.interceptor.NameMatchTransactionAttributeSource">
		<property name="properties">
			<props>
				<prop key="*">
					PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED				</prop>
			</props>
		</property>
	</bean>

[Andreas Senft]

There is some thing in Chinese which means "READ_COMMITTED and SERIALIZABLE is not available transaction level"

Afaik it is the other way around: Oracle does support _only_ READ_COMMITTED and SERIALIZABLE. That is why your second configuration works and your first does not.

Regards,
Andreas

[kenees]

Hi, Andreas. You mean that Spring’s transaction isolation levels 'ISOLATION_READ_COMMITTED' is the only one level that supported by ORACLE? Right?

But I find from the book 'Spring in action' that 'phantom and nonrepeatable
reads may still occur' in this level, so how do we solve this problem with it, THANK YOU.

[Andreas Senft]

SERIALIZABLE should work as well. That is the most restrictive level and eliminates the issues you mention.
However, in my experience READ_COMMITTED is enough for most cases.

Regards,
Andreas

[kenees]

Yes, I know READ_COMMITTED is enough for most cases, I just want to make it cleare, thank you again

[funks]

Either use READ_COMMITTED, or READ_SERIALIZABLE (if you need anything above READ_COMMITTED).. If you are using a DB that's MVCC (oracle, postgresql and etc).

Oracle is a Multi-Version Concurrency Control Database.

you might want to read the following - http://en.wikipedia.org/wiki/Isolati...puter_science), or other articles explaining MVCC.

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