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

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

Details

Reviewers: None
Maniphest Tasks: T13596: "INSERT INTO ... SELECT" queries require more database locks than "SELECT" queries
Commits: rP6af8756e798d: (stable) Execute project membership materialization as "SELECT" + "INSERT", not…
rP9cbbbe2a8712: Execute project membership materialization as "SELECT" + "INSERT", not "INSERT .

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.