In mysql, as per the official doc and other documentations,
When the IsolationLevel = ReadUncommitted or ReadCommitted or RepeatableRead, it uses Optimistic CC (with MVCC) which usually doesn't takes much locks. In case of Serializable, we will see all forms of locks like Read, Write, Range, Gap etc...
But I recently faced a problem in Hibernate when isolationLevel = RepeatableRead, it threw LockAquisitionException. After hours of debugging and analysing the behaviours, I found that we are inserting new rows which references the parent record (PrimaryKey) where INNODB locks the parent record (and) another transaction tried to update the parent record and threw LockAquisitionException (after some timeout).
After some googling, I came to know that, mysql takes locks in certain corner cases if the isolation level < Serializable which I couldn't find even after spending lot more time and documentations details about lot more variations and it's hard to spin all these around the head.
Can someone provide details on what locks, mysql takes automatically irrespective of isolation level in simple terms. This will help to identify the anamolies in the code and correct it.
In my case, no queries will be executed with "for update" or "lock in share mode". I'm concerned about only plain sql queries without any intention locks.
