In this blog series, we would like to introduce you gently to the topic on which we were working over last 2 years. Improving how InnoDB locks data (tables and rows) in order to provide illusion to clients that their queries are executed one after another, while in reality there is a lot of concurrency. By Kuba Łopuszański.
Do you remember Excel? You know, columns, and rows, perhaps in multiple tabs. And then you might have multiple files. You probably do not appreciate it consciously, but it is a nice feature, that whatever you put inside a cell stays there if you save. Perhaps sometimes you need to add a new row, or remove it expecting that to simply work, even if there were some formulas, like SUM(A1:A100) involving these rows, right? And have you ever had to collaborate on a single spreadsheet with another person? How did you go about not overwriting each others work?
In this post author will introduce following concepts:
- Serializability of transactions (ability to explain states observed over time with a convincing story about relative order of parallel operations)
- Amdahl’s law (limits of how much parallelism can help in presence of tasks which must be done one at a time)
- Timeouts (for misbehaving lock owners, and to resolve deadlocks)
- Reader-writer lock (shared/exclusive access rights)
- Starvation (permanent inflow of readers starving a writer waiting for its turn) queueing (FIFO, or priority)
- Read views (read-only snapshots which allow stale reads concurrent to new writes)
- Wall clock time out of band communication and their possible inconsistency with serialization order
- Deadlocks caused by escalation
- Granularity of a lock (access right to everything vs. only the resources needed)
… and more. This is klong read with explaining the concepts rea;ted to locking on the Excel spreadsheet example. Enjoyable!
[Read More]