Right now I am preventing several users from several instances to access the same row of my database table with a PESSIMISTIC_WRITE lock. The transaction which locks the row has the default isolation level set which is REPEATABLE_READ.
Under the circumstance that the table is empty it gets refilled with new entries and here it gets strange. The insert is an async bulk insert that runs in a seperate transaction. So when this data is commited a users reads a row for update which should lock the row. Somehow this does not work anymore and several users are able to get the value from the same row.
While trying to figure this out I changed the isolation level to READ_COMMITED and the issue did not occure anymore.
Can somebody enlighten me how this is possible? What am I missing?
Here is some psyeudo code for better understanding:
Async refilling
Transaction
get values to insert
bulk insert
Getting values
Transaction
read a row for update (Locks it)
do stuff
delete the row
For locking we are using this annotation from spring data:
@Lock(LockModeType.PESSIMISTIC_WRITE)
Ok so the answer is, that aurora prier to aurora 2.x was using MySQL 5.6.10. Aurora 2.x is now using MySQL 5.7.12 and the issue was a bug that is now fixed.
P.S. I am still asking myself why aurora uses a 2 year old MySQL version, but that’s a different story.