Transactions and Locks in MySQL

Refer: https://stackoverflow.com/questions/61212438/why-do-we-need-to-lock-a-mysql-table-during-select-when-beginning-a-transaction/

TL;DR:

  1. MySQL docs are wrong
  2. 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.
  3. To handle concurrency you need to create locks.
  4. 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
  5. SELECT … FOR UPDATE is a normal lock aka Mutex – mutual exclusion. https://en.wikipedia.org/wiki/Lock_(computer_science)

The statement

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 COMMIT before 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 …
en.wikipedia.org
This entry was posted in Software and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s