Brion Vibber wrote:
Somewhat different but worth mentioning is the updating of link tables on save: we've tended to flip-flop between deleting and rewriting all the rows, and ending up locking things during the save which turned ugly. Should make sure those are maintained cleanly, swiftly, and without too much pain.
Currently we're not locking, it's patched live to do a non-locking read and then an incremental update. So the site keeps running, but inconsistencies will inevitably be introduced.
The problem with locking reads turns out to be a bug in InnoDB. Well, I'm calling it a bug anyway. The problem is that if you select all links from a particular article for update, every link table row linking to any of those pages is locked. So if one person is updating a stub, nobody else can update a stub. This is easily repeatable in a controlled situation with the following test. With two threads labelled T1 and T2:
T1> create table ltest (f int, t int, unique key (f,t), key t(t)) type=innodb; T1> insert into ltest values (1,10),(2,10),(3,10); T1> begin; T1> select * from ltest where f=1 for update;
T2> begin; T2> select * from ltest where f=2 for update; (T2 waits for T1)
Last time I reported a locking problem to the MySQL bug tracker, I was told that it wasn't a bug, and that I just needed to enable the reassuringly-named innodb_locks_unsafe_for_binlog option. I'm happy to concentrate on public ridicule if that's what they prefer.
The other problem was also related to link table updates. If you do locking reads, you get many unnecessary deadlocks.
-- Tim Starling