Sequence Table Migration Phases
Phase 0 - Current Flow
Generate sequence ID from old sequence table only
Old Sequence Table (Cluster 0)
ID
Shard ID
Group ID
1
101
group_a
2
102
group_b
3
103
group_c
4
104
group_d
Auto Increment: 5
New Sequence Table (Sequence Generator)
ID
Shard ID
Group ID
No entries yet
Auto Increment: 1
Phase 1 - Dual Write (Old → New)
Generate from old table, then copy to new table with same ID
Old Table First
→
Then Copy to New
Old Sequence Table (Cluster 0)
ID
Shard ID
Group ID
1
101
group_a
2
102
group_b
3
103
group_c
4
104
group_d
5
105
group_e
6
106
group_f
Auto Increment: 7
New Sequence Table (Sequence Generator)
ID
Shard ID
Group ID
5
105
group_e
6
106
group_f
Auto Increment: 7
🔄 Buffer Phase: Set auto increment for new table = max(id) + buffer
New table auto increment set to ensure no conflicts
Phase 2 - Dual Write (New → Old)
Generate from new table, then copy to old table with same ID
New Table First
→
Then Copy to Old
New Sequence Table (Sequence Generator)
ID
Shard ID
Group ID
...
...
...
107
201
group_x
108
202
group_y
109
203
group_z
Auto Increment: 110
Old Sequence Table (Cluster 0)
ID
Shard ID
Group ID
...
...
...
107
201
group_x
108
202
group_y
109
203
group_z
Auto Increment: 110
Phase 3 - New Table Only
Generate sequence ID from new sequence table only (Migration Complete)
New Table Only
✅
Migration Complete
New Sequence Table (Active)
ID
Shard ID
Group ID
...
...
...
150
301
group_new1
151
302
group_new2
152
303
group_new3
153
304
group_new4
Auto Increment: 154
Old Sequence Table (Deprecated)
ID
Shard ID
Group ID
No new entries
(To be cleaned up later)
Deprecated
⚠️ CRITICAL CONCURRENCY ISSUE
Problem: Users with old code (Phase 0) can cause ID conflicts during Phase 2 & 3
Scenario: Phase 2 with Mixed Code Versions
Both tables have Auto Increment = 110
👤 User with Old Code (Phase 0)
1. Inserts into Old Table only
2. Gets ID = 110
3. Old table AI becomes 111
Old Table
ID: 110 | Data: user1_data
AI: 111
VS
👤 User with New Code (Phase 2)
1. Inserts into New Table first
2. Gets ID = 110
3. Tries to copy to Old Table
4. ❌ FAILS! ID 110 already exists
New Table
ID: 110 | Data: user2_data
AI: 111
→
Old Table
ID: 110 | Data: user1_data
❌ Can't insert ID: 110
AI: 111
💡 Solution Requirements
• Ensure ALL services are updated before enabling Phase 2
• Add prerequisite checks in CI/CD pipeline
• Monitor for any remaining Phase 0 code in production
• Maintain sufficient buffer between auto-increment values
• Implement proper error handling for duplicate key conflicts