- MySQL docs are wrong
- Transactions act like a try-catch block. They don’t provide protection against concurrency. If something fails in a transaction, it can be rolled back to undo pending operations that were not committed.
- To handle concurrency you need to create locks.
- SELECT … LOCK IN SHARE MODE is a Reader-Writer lock that allows multiple threads to read and only 1 to write. https://en.wikipedia.org/wiki/Readers%E2%80%93writer_lock
- SELECT … FOR UPDATE is a normal lock aka Mutex – mutual exclusion. https://en.wikipedia.org/wiki/Lock_(computer_science)
Beginning a transaction causes any pending transaction to be committed is extremely misleading because what it fails to mention is that The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a
COMMITbefore executing the statement. What this means is that if in our code you have a START TRANSACTION and you forget to explicitly COMMIT it, then later on whenever another START TRANSACTION is encountered, it will cause the running transaction to be committed before a new one can begin. This is likely not what you thought it does when you read Beginning a transaction causes any pending transaction to be committed.
|Readers–writer lock – Wikipedia
In computer science, a readers–writer (single-writer lock, a multi-reader lock, a push lock, or an MRSW lock) is a synchronization primitive that solves one of the readers–writers problems.An RW lock allows concurrent access for read-only operations, while write operations require exclusive access. This means that multiple threads can read the data in parallel but an exclusive lock is …