While the answer above is true in that a SELECT … FOR UPDATE will prevent concurrent sessions / transactions from inserting the same record, that is not the full truth. I am currently fighting with the same problem and have come to the conclusion that the SELECT … FOR UPDATE is nearly useless in that … Read more
This is a great question. InnoDB is a row level locking engine, but it has to set additional locks to ensure safety with the binary log (used for replication; point in time recovery). To start explaining it, consider the following (naive) example: session1> START TRANSACTION; session1> DELETE FROM users WHERE is_deleted = 1; # 1 … Read more
You can use the ROWLOCK hint, but AFAIK SQL may decide to escalate it if it runs low on resources From the doco: ROWLOCK Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK … Read more