Page MenuHomePhabricator

Execute project membership materialization as "SELECT" + "INSERT", not "INSERT ... SELECT"
ClosedPublic

Authored by epriestley on Jan 27 2021, 11:20 PM.

Details

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.

Diff Detail

Repository
rP Phabricator
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.