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