mdsol/the_policy_machine — pull request #23 by smyers (2014-05-27 14:23:51+00:00)
Title: [MCC-110306] Fix MySQL Deadlocks

This PR addresses the deadlocks seen in validation environment, and reproduced consistently in performance environment (by the role assignment create test). Essentially the problem is the cross join locks a large number of rows, and deadlocks the transaction when two concurrent requests are made to `add_to_transitive_closure` with the same parent_id.

My investigation (and experimentation - thanks to @csavage-mdsol great repro test!) found that although the transaction is in "Repeatable Read", normally allowing reads to occur w/o locking, InnoDB will take shared locks on reads (as if it was "Read Committed") in certain scenarios with "insert - select" statements.

Breaking up the transaction so that `add_to_transitive_closure` is its own transaction will also work, however that will remove the atomic update of the assignment.
Simply breaking up the select from the insert seems the best approach. I could not reproduce the deadlock after this change (and before the change it would occur 100 times in the same test consistently).

Other minor changes:
- added "distinct" to select clause (at least in performance, there was dramatic duplication of entries)
- changed `select *` to `select NULL` (more idiomatic sql, and skips a second read on the clustered index, if assignment table ever changed).

@HonoreDB @csavage-mdsol @yzhangmedidata please review.
@mszenher fyi.

Finished in 15.57 seconds
564 examples, 0 failures
Coverage report generated for RSpec to /Users/smyers/mdsol/the_policy_machine/coverage. 628 / 706 LOC (88.95%) covered.