During several days I was trying to solve an issue with rowlock on Oracle and MariaDB (MySQL). There were concurrent requests entering into @Transactional* method which changed data in the DB.
HOW TO DEBUG
When solving such issues, it might be hard to reproduce the situation because of the concurrency. One solution is to use logging or simple output. It’ll give understanding at least how many streams have passed a particular point in the code. Using static variables can also help. Iterating them might give an estimate of the number of times something happens.
It’s possible to send two concurrent threads using, for example, sleep() for a thread. Placing it before the problematic area will provide enough time to send as many threads as needed. However, you might need to use static counter in order to let every second thread without falling asleep.
HOW TO FIX
Lock
In the case of reading-writing the data, the safest fix is to put a trigger, which tells if the area contains consistent information or it’s better to wait till the previous thread finishes changing the resource. To find out more about this mechanism, read about Semaphores and, particularly, about one of its implementations, Mutex. What was especially strange with this approach in my case was that in MariaDB all worked fine but not in Oracle. The problem was that with the find request I used the following annotation: @Lock(PESSIMISTIC_WRITE). Its parameter is LockModeType enum value. Somehow it seems not to work proper with Oracle 12 or Hibernate 5.0.2.Final. What helped was to use entityManager.lock(Entity, LockModeType) method. You can also make a lock on the session: entityManager.unwrap(Session.class).lock(Entity, LockMode). Here you can get more general info about locking. For more information why the annotation didn't work and how to fix it check the following StackOverflow question of mine. One of the Hibernate authors was very kind to reply.
Exchange Uncommitted Info (Scarry)
There are different ways to fix the problem, but they heavily depend on the code. For example, you can enable information exchange between transactions-sessions. As soon as the data are changed in terms of one session, the other one will become aware of it and will read the already updated data. This feature can be enabled using READ_UNCOMMITED in the DB. Note that it’s a possible isolation level in MariaDB (MySql) which you can happily use when debugging but it’s not applicable in serious Oracle.
Problems may occur, however. If the transaction that changed the data and gave it to the next parallel transaction rollbacks, the data, with which the second transaction operates, might become erroneous. Besides, it’s always better to have guarantees that 2 parallel transactions won’t read the data being changed, which they use to write new information. The first transaction still could want to continue updating the data, when the second one had already read it to change.
Try Until You Make it or Fast fix
Badly written locks can produce deadlocks. Two threads will constantly attempt to access the same resource. Each will wait until the other one releases the DB. If this happens very rarely, you might be interested in using Ostrich algorithm. The better solution might be to get rid of possible deadlocks and wait till the exception of writing to the DB happens. When it does, just try again and hope that this time, no other thread will want to disturb the same data. This solution might be applied when the problem happens really rarely, and when putting a lock will cause serious downshift in performance. For example, I knew that the erroneous insert to the DB was always followed by update, and I couldn’t have 2 bad consecutive inserts. So the just described fix worked for my problem.
I wish you good luck! Have fun with your problem. Hope the fix comes soon. :)
* This article is inspired by experienced problems in Java + Spring + Hibernate + SpringData + DB (Oracle and MariaDB)
