Execute project membership materialization as "SELECT" + "INSERT", not "INSERT ... SELECT"
Summary:
Ref T13596. See that task for discussion. Executing "INSERT ... SELECT" at default isolation levels requires more locking than executing "SELECT" + "INSERT" separately.
Decompose this "INSERT ... SELECT" into "SELECT + INSERT", and reformat it to execute a minimal set of changes instead of wiping everything out and then writing all of it back. In most cases, this means we write 1 row instead of O(number of project members) rows.
Test Plan:
- Created a project. Added and removed members, looked at database and saw a consistent membership/materialization list.
- Created a subproject. Added and removed members, looked at database and saw a consistent membership/materialization list.
I wasn't successful in reproducing the LOCK WAIT issue locally by trying various concurrent SELECT / INSERT / INSERT ... SELECT strategies. It may depend on the "DELETE + INSERT ... SELECT" structure used here, or versions/config/etc, so we'll have to see how that fares in production.
Maniphest Tasks: T13596
Differential Revision: https://secure.phabricator.com/D21527