With the unintended impression of becoming live blogging, we now follow up on Solving the non-atomic table swap, Take II and Solving the Facebook-OSC non-atomic table swap problem with a safe, blocking, atomic solution
Why yet another iteration?
The solution presented in Solving the non-atomic table swap, Take II was good, in that it was safe. No data corruption. Optimistic: if no connection is killed throughout the process, then completely blocking.
Two outstanding issues remained:
- If something did go wrong, the solution reverted to a table-outage
- On replicas, the table swap is non atomic, non blocking. There’s table-outage scenario on replica.
As it turns out, there’s a simpler solution which overcomes both the above. As with math and physics, the simpler solution is often the preferred one. But it took those previous iterations to gather a few ideas together. So, anyway:
Safe, locking, atomic, asynchronous table swap
Do read the aforementioned previous posts; the quick-quick recap is: we want to be able to LOCK a table tbl, then do some stuff, then swap it out and put some ghost table in its place. MySQL does not allow us to rename tbl to tbl_old, ghost to tbl if we have locks on tbl in that session.
The solution we offer is now based on two connections only (as opposed to three, in the optimistic approach). “Our” connections will be C10, C20. The “normal” app connections are C1..C9, C11..C19, C21..C29.
- Connections C1..C9 operate on tbl with normal DML: INSERT, UPDATE, DELETE
- Connection C10: CREATE TABLE tbl_old (id int primary key) COMMENT=’magic-be-here’
- Connection C10: LOCK TABLES tbl WRITE, tbl_old WRITE
- Connections C11..C19, newly incoming, issue queries on tbl but are blocked due to the LOCK
- Connection C20: RENAME TABLE tbl TO tbl_old, ghost TO tbl
This is blocked due to the LOCK, but gets prioritized on top connections C11..C19 and on top C1..C9 or any other connection that attempts DML on tbl - Connections C21..C29, newly incoming, issue queries on tbl but are blocked due to the LOCK and due to the RENAME, waiting in queue
- Connection C10: checks that C20’s RENAME is applied (looks for the blocked RENAME in processlist)
- Connection 10: DROP TABLE tbl_old
Nothing happens yet; tbl is still locked. All other connections still blocked. - Connection 10: UNLOCK TABLES
BAM! The RENAME is first to execute, ghost table is swapped in place of tbl, then C1..C9, C11..C19, C21..C29 all get to operate on the new and shiny tbl
Some notes Continue reading » “Solving the non-atomic table swap, Take III: making it atomic”