Hi,
I have a question about container managed transactions and how they might cause a problem I'm seeing compared to straight JDBC transactions.
We have a piece of functionality in our application that might mean that a single row in a DB table is updated by many threads at the same time. This seems to work fine up until there are more than 16 threads all requiring a lock on the same row, then a deadlock occurs. Having seen this happen with the actual functionality I created a test using a servlet to queue the required number of messages onto a JMS queue, that has an unrestricted pool of MDBs listening at the end of it. The MDB is marked as bean managed transactions but I haven't added any specific transaction code so the operation of receiving the message is not wrapped in a transaction of any kind. The called session bean then reads a value using a locking read, manipulating it (in this case just adding 1 to the existing value), then updating the row in the DB. All of this is done in a transaction, and the read operation is using a row level lock provided by the mysql select...for update statement. The test doesn't do anything else so it's a very basic test, and only makes use of a single datasource which is non-XA.
I have a param on my test that allows me to switch from using JDBC transactions over to container managed transactions and have tried both scenarios. Using JDBC transactions the test never fails, no mater how many threads I request, and the value at the end of the test is always right. However using container managed transactions if I queue anywhere up to 16 messages onto the JMS queue all the updates get processed and the value at the end of the test is correct. If 17 or more threads are used then all the processes enter a lock situation whereby I can see in the innodb status that all transactions are trying to acquire a lock on the record concerned but non of them are being granted.
My test system is a SUN T1000 server running the latest version of glassfish v2 (although I have tried this with previous versions and the same occurs), the latest version of mysql 5.1 (has also been tried with the latest 5.0 and 5.4 releases and showed the same problem), and the relevant mysql JDBC drivers.
It seems to be that 16 is the magic number, where over this value something stuffs up in the container. I'm grateful for any ideas of how to solve this problem, or if anyone can help explain why this occurs that would be equally valuable. I'd really like to continue using container managed transactions but at the moment I'm having to re-evaluate them due to this problem, so if this is generally something that container managed transactions don't handle too well then I will have to steer clear of them.
Many thanks in advance,
Darren
[Message sent by forum member 'xerces' (xerces)]
http://forums.java.net/jive/thread.jspa?messageID=361141