Lock the source while adding members
What does this MR do and why?
When two requests add the same user to the same group or project at the same time, both can miss the other's row, and both insert one. On a recent production clone there are about 100,000 duplicated user and source pairs. Almost all of them were created within a second of each other, and most come from AI service account flows.
This is the advisory lock step of Milestone 1 in the deduplication process for large tables. It's the first step towards a unique index on members, because new duplicates have to stop before the existing ones can be removed.
- A
prevent_concurrent_insertscallback locks the group or project for the rest of the transaction whenever a member is created or an invite is accepted. A concurrent add for the same source then waits, and fails the existing uniqueness validation instead of adding a duplicate. Members::CreatorServicetakes the same lock before it looks up existing members, so the second request sees the first request's row and updates it instead of failing.- The AI service account bulk add inserts rows directly, which skips callbacks, so it takes the lock itself. It locks projects in id order to avoid deadlocks, and its batch size drops from 1,000 to 100 so each transaction holds fewer locks.
- SCIM group sync changes members of several groups inside one transaction, so it now locks all of its groups up front, in id order, to avoid deadlocks.
- As a result, removing a user from a SCIM group no longer updates their membership in groups linked only to their other SCIM groups. Those groups' own sync jobs handle it, so the job never changes a group it didn't lock.
- The bulk permission rake tasks now add members one project at a time instead of in a single transaction, so they don't hold a lock on every project at once.
It's behind the lock_member_creation flag, so it can be rolled out and measured gradually.
Before the flag goes on by default
- SCIM group sync holds its group locks for the whole job. Check job durations, because a long job makes other member changes to those groups wait.
- Self-managed instances only get the lock once the flag is on by default or removed. The cleanup migration for existing duplicates has to wait for that.
Database
All queries run on the main database. Plans come from a Database Lab clone of production (EXPLAIN (ANALYZE, BUFFERS)). In the SQL below, "members".* and "namespaces".* stand in for the explicit column lists Rails generates.
1. Lock statement (Member.lock_source)
SELECT pg_advisory_xact_lock(hashtextextended('members:Project:<project_id>', 0))
-- or 'members:Namespace:<group_id>' for a groupA transaction-level advisory lock, released at commit or rollback. It reads no table, so there is no plan. All callers are behind the lock_member_creation flag, checked per source. The lock is taken by:
Member#prevent_concurrent_inserts(before_validation) when a member is created or a user is set on one (accepting an invite), inside the save transaction.Members::CreatorService#lock_and_find_existing_members, before the existing-member lookup (query 2).add_memberswith several sources sorts them by[polymorphic_name, id]first, so locks are always taken in the same order.Authn::SyncScimGroupMembersWorker#lock_linked_groups, once per linked group in id order, at the start of the job's transaction (query 3).Ai::ServiceAccountProjectMemberBulkAddService#lock_projects, once per project in id order, in one transaction per batch. The batch size drops from 1,000 to 100 so fewer locks are held at once.
2. Existing members lookup (Members::CreatorService, modified)
On master this lookup is inline in parse_users_list. On this branch it moves to lock_and_find_existing_members. The change is users_by_emails.values.compact. Before, an invited email with no matching user put nil in the list, so the query also loaded every pending invite of the source.
a. No email-only invitees: old and new SQL are identical
SELECT "members".* FROM "members"
WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = <project_id> AND "members"."source_type" = 'Project'
AND "members"."user_id" = <user_id>- Project 278964: Index Scan on
idx_members_on_user_and_source_and_source_type_and_member_role, 1 row, 0.20 ms warm, buffers 5 hit / 0 read, planning 2.1 ms. - Group 9970 (
type = 'GroupMember',source_type = 'Namespace'): same index, 1 row, 0.10 ms warm, buffers 5 hit / 0 read, planning 2.3 ms.
b. A user plus an email with no account
Before:
SELECT "members".* FROM "members"
WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = <project_id> AND "members"."source_type" = 'Project'
AND ("members"."user_id" = <user_id> OR "members"."user_id" IS NULL)- Project 278964 (0 pending invites): BitmapOr of two Bitmap Index Scans on
idx_members_on_user_and_source_and_source_type_and_member_role, 1 row, 0.11 ms warm, buffers 9 hit / 0 read, planning 1.8 ms. - Group 9970 (5 pending invites): same plan, 6 rows, 0.14 ms warm, buffers 14 hit / 0 read, planning 1.7 ms.
- Worst case on production data, the group with the most pending invites (104): Index Scan on
index_members_on_source_and_type_and_access_levelwith a filter (291 rows removed), 105 rows, 0.64 ms warm, buffers 368 hit / 0 read, planning 1.7 ms.
After: the same SQL as 2a ("members"."user_id" = <user_id>).
- Same worst-case group: Index Scan on
idx_members_on_user_and_source_and_source_type_and_member_role, 1 row, 0.09 ms warm, buffers 5 hit / 0 read, planning 1.9 ms.
c. Email-only invitees (no matching users)
Before:
SELECT "members".* FROM "members"
WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = <group_id> AND "members"."source_type" = 'Namespace'
AND "members"."user_id" IS NULL- Project 278964: Index Scan on
idx_members_on_user_and_source_and_source_type_and_member_role, 0 rows, 0.07 ms warm, buffers 4 hit / 0 read, planning 1.7 ms. - Group 9970: same index, 5 rows, 0.10 ms warm, buffers 9 hit / 0 read, planning 1.7 ms.
After:
SELECT "members".* FROM "members"
WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = <group_id> AND "members"."source_type" = 'Namespace'
AND 1=0ResultwithOne-Time Filter: false: reads no rows, 0.04 ms warm, buffers 0, planning 1.1 ms.
The highest pending-invite count on any single source is 104, so the old query read at most about 100 extra rows. The new query never reads them.
Plans
2a, project 278964:
Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (cost=0.57..3.59 rows=1 width=207) (actual time=0.034..0.035 rows=1 loops=1)
Index Cond: ((user_id = <user_id>) AND (source_id = <project_id>) AND ((source_type)::text = 'Project'::text))
Filter: ((type)::text = 'ProjectMember'::text)
Buffers: shared hit=5
Planning:
Buffers: shared hit=698
Planning Time: 2.083 ms
Execution Time: 0.201 ms2a, group 9970:
Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (cost=0.57..3.59 rows=1 width=207) (actual time=0.039..0.040 rows=1 loops=1)
Index Cond: ((user_id = <user_id>) AND (source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text))
Filter: ((type)::text = 'GroupMember'::text)
Buffers: shared hit=5
Planning:
Buffers: shared hit=698
Planning Time: 2.312 ms
Execution Time: 0.100 ms2b before, project 278964:
Bitmap Heap Scan on members (cost=4.16..5.68 rows=1 width=207) (actual time=0.040..0.041 rows=1 loops=1)
Recheck Cond: (((user_id = <user_id>) AND (source_id = <project_id>) AND ((source_type)::text = 'Project'::text)) OR ((user_id IS NULL) AND (source_id = <project_id>) AND ((source_type)::text = 'Project'::text)))
Filter: ((type)::text = 'ProjectMember'::text)
Heap Blocks: exact=1
Buffers: shared hit=9
-> BitmapOr (cost=4.16..4.16 rows=1 width=0) (actual time=0.024..0.025 rows=0 loops=1)
Buffers: shared hit=8
-> Bitmap Index Scan on idx_members_on_user_and_source_and_source_type_and_member_role (cost=0.00..2.08 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
Index Cond: ((user_id = <user_id>) AND (source_id = <project_id>) AND ((source_type)::text = 'Project'::text))
Buffers: shared hit=4
-> Bitmap Index Scan on idx_members_on_user_and_source_and_source_type_and_member_role (cost=0.00..2.08 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((user_id IS NULL) AND (source_id = <project_id>) AND ((source_type)::text = 'Project'::text))
Buffers: shared hit=4
Planning:
Buffers: shared hit=698
Planning Time: 1.811 ms
Execution Time: 0.105 ms2b before, group 9970:
Bitmap Heap Scan on members (cost=4.16..5.68 rows=1 width=207) (actual time=0.045..0.079 rows=6 loops=1)
Recheck Cond: (((user_id = <user_id>) AND (source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text)) OR ((user_id IS NULL) AND (source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text)))
Filter: ((type)::text = 'GroupMember'::text)
Heap Blocks: exact=6
Buffers: shared hit=14
-> BitmapOr (cost=4.16..4.16 rows=1 width=0) (actual time=0.029..0.030 rows=0 loops=1)
Buffers: shared hit=8
-> Bitmap Index Scan on idx_members_on_user_and_source_and_source_type_and_member_role (cost=0.00..2.08 rows=1 width=0) (actual time=0.017..0.017 rows=1 loops=1)
Index Cond: ((user_id = <user_id>) AND (source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text))
Buffers: shared hit=4
-> Bitmap Index Scan on idx_members_on_user_and_source_and_source_type_and_member_role (cost=0.00..2.08 rows=1 width=0) (actual time=0.011..0.011 rows=5 loops=1)
Index Cond: ((user_id IS NULL) AND (source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text))
Buffers: shared hit=4
Planning:
Buffers: shared hit=698
Planning Time: 1.745 ms
Execution Time: 0.139 ms2b before, group with the most pending invites:
Index Scan using index_members_on_source_and_type_and_access_level on members (cost=0.57..4.97 rows=1 width=207) (actual time=0.035..0.587 rows=105 loops=1)
Index Cond: ((source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text) AND ((type)::text = 'GroupMember'::text))
Filter: ((user_id = <user_id>) OR (user_id IS NULL))
Rows Removed by Filter: 291
Buffers: shared hit=368
Planning:
Buffers: shared hit=698
Planning Time: 1.732 ms
Execution Time: 0.641 ms2b after, same group:
Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (cost=0.57..3.59 rows=1 width=207) (actual time=0.037..0.038 rows=1 loops=1)
Index Cond: ((user_id = <user_id>) AND (source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text))
Filter: ((type)::text = 'GroupMember'::text)
Buffers: shared hit=5
Planning:
Buffers: shared hit=698
Planning Time: 1.917 ms
Execution Time: 0.094 ms2c before, project 278964 / group 9970:
Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (cost=0.57..3.59 rows=1 width=207) (actual time=0.021..0.022 rows=0 loops=1)
Index Cond: ((user_id IS NULL) AND (source_id = <project_id>) AND ((source_type)::text = 'Project'::text))
Filter: ((type)::text = 'ProjectMember'::text)
Buffers: shared hit=4
Planning:
Buffers: shared hit=698
Planning Time: 1.728 ms
Execution Time: 0.070 ms
Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (cost=0.57..3.59 rows=1 width=207) (actual time=0.022..0.050 rows=5 loops=1)
Index Cond: ((user_id IS NULL) AND (source_id = <group_id>) AND ((source_type)::text = 'Namespace'::text))
Filter: ((type)::text = 'GroupMember'::text)
Buffers: shared hit=9
Planning:
Buffers: shared hit=698
Planning Time: 1.714 ms
Execution Time: 0.096 ms2c after:
Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
Planning:
Buffers: shared hit=462
Planning Time: 1.109 ms
Execution Time: 0.041 ms3. Linked groups to lock (Authn::SyncScimGroupMembersWorker#lock_linked_groups, new)
Runs once per job, at the start of its transaction. The ids come from SamlGroupLink.by_scim_group_uid, which the worker already loads.
SELECT "namespaces".* FROM "namespaces"
WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (<group_ids>)Production has no saml_group_links rows with scim_group_uid set yet. As a realistic large set I used the groups linked to one SAML group name, the name linked to the most groups: 401 groups, all under one top-level group.
- Index Scan on
index_namespaces_on_type_and_id, 401 rows, 1.1 ms warm, buffers 608 hit / 0 read, planning 2.5 ms.
That job then holds 401 advisory locks until it commits.
Plan
Index Scan using index_namespaces_on_type_and_id on namespaces (cost=0.57..903.80 rows=50 width=355) (actual time=0.023..1.042 rows=401 loops=1)
Index Cond: (((type)::text = 'Group'::text) AND (id = ANY ('{<401 group_ids>}'::integer[])))
Buffers: shared hit=608
Planning:
Buffers: shared hit=819
Planning Time: 2.540 ms
Execution Time: 1.109 msAll numbers are warm second runs.
References
Screenshots or screen recordings
Not applicable, no UI change.
How to set up and validate locally
- In a Rails console, enable the flag:
Feature.enable(:lock_member_creation). - Add the same user to the same project from several threads at once, for example 8 threads calling
project.add_member(user, :developer). - Check
project.members.where(user: user).count. It's always 1 with the flag on. With it off, the same test produced up to 4 rows per round in GDK.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.