My JMS Receiver or MDB cannot see a DB update made in the same transaction as a send?

Question

I have a transaction that uses XA to update a database and send a message. The receiver of the message does not see the changed data?

Answer

This is an an example of a race condition due to the isolation level of the database.

To understand what is happening here are the steps

• Thread1 is the sending thread that does the database update

• Thread2 is the receiving thred that queries the database

Thread1: send a message in a JTA transaction (it is not sent yet)
Thread1: update the database (the data is not visible to other transactions yet)
Thread1: commit -> requires two phase commit
Thread1: prepare phase JMS votes ok
Thread1: prepare phase DB votes ok

At this point the commit is guaranteed to work, but there is no guarantee about timings,

i.e. the order of the individual local branch commits or when they happen.

Thread1: commit JMS branch - the message is now available to be received
Thread2: JMS pushes the message to the receiver
Thread2: tries to query the data, but it hasn't changed yet
Thread1: commit jdbc branch - the change is visible to other transactions now

The problem is the isolation level of the connection. You need TRANSACTION_REPEATABLE_READ

if it were a database write you need to avoid a phantom read you need TRANSACTION_SERIALIZABLE.

Performance Note

These isolations have considerable impact on performance. Depending upon your database

implementation they either lead to more locking in the database or an increased likelyhood

of optimisitic rollbacks. Addtionally, these isolation levels cannot be used with JBoss's entity bean pessimistic lock implementation.