stanford-oval / almond-cloud / 1916

62%

push

travis-ci-com

training/update-dataset: lower consistency level of update transaction (#585)

According to the MySQL documentation:
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks
at REPEATABLE READ level, UPDATE and DELETE statements use gap & next key locking,
and may lock rows or index records that are not matched by the query
(to avoid phantoms).
This is likely the cause of deadlocks we're seeing when the update-dataset
task is run concurrently to a Thingpedia edit operation or a mturk
task, both of which try to insert into the example_utterances database
(Thingpedia also tries to delete & edit).

At READ COMMITTED level, on the other hand, only the rows matched
by the query are locked. In principle, this should be sufficient
to solve the problem, as the update-dataset task only deletes synthetic
data. (It also updates paraphrase data to mark obsolete sentences, but
it's fine if it misses some paraphrase sentences that are inserted concurrently,
because those won't be immediately obsolete).

I say in principle, because we've been played with locking for a while
now, and this has been a long-standing concurrency problem. But this
is a quick fix.

2149 of 3840 branches covered (55.96%)

Branch coverage included in aggregate %.

6933 of 9742 relevant lines covered (71.17%)

2474.34 hits per line