HQSL transaction isolation

[timfox]

JBoss Messaging requires tx isolation level of READ_COMMITTED, to ensure proper tx isolation, and anything stricter than READ_COMMITTED is likely to result in deadlocks.

Since HSQL only supports transaction isolation level of READ_UNCOMMITTED, we cannot guarantee any kind of data consistency when using HQSQL. (Without doing lots of nasty hacks to get isolation through some other means)

In practice we would really only see issues when paging messages to and from storage. (E.g. tx1 commits sending messages to storage, then they are partially loaded by another thread before the tx commit completes), although I am not 100% sure wouldn't see effects of this in normal "non paging" use. Perhaps we should disable paging functionality if an isolation level of looser than READ_COMMITTED is detected?

I know we don't recommend HSQLDB in production but I think we need to be clear about this to potential new users of JBossMessaging, and get people to use MySQL for example if they don't have access to a "big" database. (I guess mysql IS a big db these days.....)

On a related point, MySQL has a default tx isolaton of REPEATABLE_READ which is actually unnecessarily strict for us (and leads to deadlock) so any mysql datasource used needs to explicitly set the isolation level.

This is currently detected in the code.

Getting it to work with REPEATABLE_READ is probably possible, but could have a performance impact since ordering write locks (which I'm currently doing) would not be sufficient to prevent deadlock. I'd probably need to detect and retry the tx.

[adrian.brock]

You should be doing the isolation yourself.

For < READ_COMMITTED
You should know when you lazy load from a queue
which messages are in a committed state (from the JMS point of view).

I can see why > READ_COMMITTED would cause a problem
Thread 1: in tx1 insert Queue1 Message1
Thread 2: load Queue1
Thread 1: in tx1 insert Queue1 Message2

Depending on the quality of db implementation this will either:
1) Timeout
2) Cause a deadlock exception
3) Cause a optimistic lock failure

Which you would have to cater for. i.e. rerun the transactions
that fail.
You should also recommend not using > READ_COMMITTED
if the DB doesn't support deadlock detection or optimistic failures
for > REPEATABLE_COMMITTED isolation
and for performance reasons anyway,

[adrian.brock]

On a related issue, you should try
connection.setReadOnly(true) when you are only querying.

This is a hint to the DB to do extra optimizations, including
improving the commit time when there were only reads.

I keep meaning to test this for JBossMQ.

[timfox]

You should be doing the isolation yourself.

For < READ_COMMITTED
You should know when you lazy load from a queue
which messages are in a committed state (from the JMS point of view).

Not sure how I would know that. If I attempt to load and another tx is concurrently inserting a bunch of messages then I may only see some of that other transactions messages.

I can see why > READ_COMMITTED would cause a problem
Thread 1: in tx1 insert Queue1 Message1
Thread 2: load Queue1
Thread 1: in tx1 insert Queue1 Message2

Depending on the quality of db implementation this will either:
1) Timeout
2) Cause a deadlock exception
3) Cause a optimistic lock failure

Which you would have to cater for. i.e. rerun the transactions
that fail.
You should also recommend not using > READ_COMMITTED
if the DB doesn't support deadlock detection or optimistic failures
for > REPEATABLE_COMMITTED isolation
and for performance reasons anyway,

I can add transaction rerunning, I just wanted to avoid doing this if we can mandate READ_COMMITTED.

Probably laziness on my part :)

[adrian.brock]

"timfox" wrote:

You should be doing the isolation yourself.

For < READ_COMMITTED
You should know when you lazy load from a queue
which messages are in a committed state (from the JMS point of view).

Not sure how I would know that. If I attempt to load and another tx is concurrently inserting a bunch of messages then I may only see some of that other transactions messages.

Why would you care if you see only some? You want to "see" none
of them until the commit is confirmed.

This is even more important for 2PC where the prepare writes to disk
to confirm the commit will work, but it doesn't exist for anybody else
until you get the commit()

e.g. In JBossMQ JDBC2 this would be something like:
select * from jms_messages where destination="Queue1"
and txid not in (select txid from jms_transactions)

But even that wouldn't be enough, it would have to weed out
messages that are in the unacknowledged state using the internal
data structure. i.e. it should NOT reload messages from the database
that are currently being received by a client.

[timfox]

For 2PC we do something similar to JBossMQ, i.e. we insert messages with state "+", and on jms commit, we update them.

For 1PC we just insert them in "committed" state.

The entire commit (or prepare) happens in one jdbc tx

Even if we inserted them with state = "+" and then updated on commit, then with transaction isolation level of READ_UNCOMITTED another tx could see the results of the update when it's only partially complete, resulting in only some of the tx being visible.

For loading this should be ok as long the messages are updated in insertion order (i.e. i wouldn't see "holes" in the ordering if I saw the tx part way throught), but can I guarantee this?

[timfox]

On a related issue, you should try
connection.setReadOnly(true) when you are only querying.

Interesting. Is this ok to set when using a managed connection?

[adrian.brock]

Yes. The ManagedConnection will reset the read-only mode
as a part of the pooling.
It even does this "lazily" like the auto-commit.
http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCALazyAutoCommit

[timfox]

I get the following when I attempt this:

1) test_Simple_TP_LX(org.jboss.test.messaging.jms.stress.StressTopicSameConnecti
onTest)java.sql.SQLException: You cannot set read only during a managed transact
ion!
 at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.setJdbcR
eadOnly(BaseWrapperManagedConnection.java:519)
 at org.jboss.resource.adapter.jdbc.WrappedConnection.setReadOnly(Wrapped
Connection.java:88)
 at org.jboss.messaging.core.plugin.JDBCPersistenceManager.retrievePrepar
edTransactions(JDBCPersistenceManager.java:1787)
 at org.jboss.messaging.core.tx.TransactionRepository.loadPreparedTransac