XADataSourceLoader attribute: isolation level - configurable

[joe543]

Hi,

Tomcat3.2.3+Jboss2.4.1 on SuSE Linux 7.2, jdk1.3.1_01 against PostgreSQL 7.0

A quote from Sun's blueprints:
Enterprise beans using container-managed persistence use the default isolation level of the underlying database; therefore, the isolation level cannot modified.

A quote from the lastest (flashline) JBoss2.4.x base docs:
Page 192 of 444 TransactionIsolation: Sets the Transaction isolation level on the SQL Connection. Valid values are TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED , TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE. The choice of the level allows one to choose a trade-off between performance and transaction isolation. The values listed are from poor to full isolation; but from good to poor performance. Refer to java.sql.Connection for more information. The default is to use the JDBC driver default.

My question:
If the postgresql jdbc driver default is READ_COMMITTED, is my setting of the XADataSourceLoader attribute, TransactionIsolation (in jboss.jcml) to REPEATABLE_READ meaningless? (as the Sun quote seems to indicate). i.e. does 'cannot (be) modified' mean that it cannot be overridden (at jboss startup) either?

I have tried setting it and according to my logging, it appears not to have taken effect. Can anyone confirm the situation?

Cheers
Joe

[joe543]

Hi,

Well, it appears that the TransactionIsolation attribute (jboss.jcml) does actually have an effect. I changed the setting to TRANSACTION_SERIALIZABLE and ran a client against it.
What I got as soon as there was a concurrent request was an SQLException - during an attempted store. This to me is unexpected because if I choose serializable - the safest option in terms of data integrity, it should at worst result in a super-slow response - not an exception. Maybe its expecting a 'wait' from the server or something. I've had a look and there doesn't seem to be a wait policy of any kind for concurrent requests (that I can see, anyway).

The following is a snippet from the output:
[snip][pre][Default] ejbStore(), id = 28!
[EntitySynchronizationInterceptor] Store failed
java.rmi.ServerException: Store failed; nested exception is:
java.sql.SQLException: ERROR: Can't serialize access due to concurrent update

java.sql.SQLException: ERROR: Can't serialize access due to concurrent update

at org.postgresql.Connection.ExecSQL(Connection.java:393)
at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
at org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:126)
at org.jboss.pool.jdbc.PreparedStatementInPool.executeUpdate(PreparedStatementInPool.java:82)
at org.jboss.ejb.plugins.jaws.jdbc.JDBCUpdateCommand.executeStatementAndHandleResult(JDBCUpdateCommand.java:49)
at org.jboss.ejb.plugins.jaws.jdbc.JDBCCommand.jdbcExecute(JDBCCommand.java:160)
at org.jboss.ejb.plugins.jaws.jdbc.JDBCStoreEntityCommand.execute(JDBCStoreEntityCommand.java:97)
at org.jboss.ejb.plugins.jaws.JAWSPersistenceManager.storeEntity(JAWSPersistenceManager.java:168)
at org.jboss.ejb.plugins.CMPPersistenceManager.storeEntity(CMPPersistenceManager.java:397)
[/pre][snip]

Another puzzling thing is although I get the above exception only when I set transaction_serializable, the postgres logs say NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED.

The way I understand synchronization by the container to work is that it provides concurrent processing for a single db row (i.e. multiple entity bean instances in memory for a single db row) and then looks to the isolation level for the level of transaction isolation. If there is a request for a 'currently-accessed-within-trx' db record, the 2nd request should wait.

I'm still struggling to understand this so any help much appreciated.:-)

Joe

[davidjencks]

Don't believe everything you read in suns blueprints- in this case they may be talking about their ri.

JBoss definitely uses the transaction isolation setting, why else would we have put it in?

Are you sure of the semantics of postgres serializable? I agree with you about what it "should" mean but this may not be what they implemented. They might have read committed + exceptions when you attempt to update records updated in another transaction. This does I believe result in all successful transactions being serialized... just too many failed transactions.

[joe543]

Hi,

Yes, I thought I'd try a different approach for such a high read-to-update ratio so I'm caching data in the servlet engine and flushing it to the persistent store using a background thread(servlet genned).

Thanks for the feedback!

[willekiel]

You know, you might want to add FOR UPDATE at the end of some of your finder clauses. This seems like a better solution if your write to read ratio is high, or if rolling back the transaction is too wasteful. The cost is that you need to know whether you're going to update the row upfront, or else incur and additional DB hit to lock the row once you do know, plus another hit to check the data did not get modified after you read and before you locked. To do this you might have 2 sets of finders: locking and non-locking.

Keep in mind that regardless of what you specify at the bean level, the DB is the only one in a position to enforce locking (e.g. across a cluster). If two separate sessions try to update the same a row, the second session must block until the first one commits or rolls back. If the first one commits, the result depends on the isolation level of the second session. If it's READ COMMITTED, its update will go through at that point. If it's SERIALIZABLE, it will get a "can't serialize access" type of error (the infamous ORA-08177 under Oracle).

Also, note that some databases (e.g. Postgres and Oracle) don't support all isolation modes in the ANSI/ISO SQL Standard.