April 04, 2025
Read Time: ~6 minutes
<aside> 📌 At Empower, we seek individuals who challenge personal assumptions, value ownership and trust, and strive for excellence to inspire and empower their team. If this article connected with you, join our team!
</aside>
<aside>
📎 A seemingly simple FK constraint drop turned into a high-stakes locking issue that threatened database stability. This post dives into the surprising chain reaction between two related tables and reveals a clever zero-downtime workaround using sp_rename. Perfect for engineers who love creative solutions to tricky schema changes—plus some spicy alternatives if you're feeling bold.
</aside>
https://open.spotify.com/episode/1ZdvbLfxB3zB2f4AgHNj0f?si=fe9d3738f87e4114
The command asks for a Sch-M (Schema Modification Lock). This shouldn’t be a problem though since Table_A isn’t often used. The real issue lies with the fact that the Parent table (Table_B) is also affected by this command since every single insert (of which there are thousands happening ever second) is asking Table_A if the constraint we’re trying to remove is being violated.
This causes a circular wait chain that will eventually consume many resources and has the potential to take down your database!
At this point, the realization that I won’t be able to remove this set it and it was time to investigate “alternative solutions” 😃
I still had the requirement of dropping this constraint, but this change wasn’t important enough to schedule downtime.
Enter sp_rename! The idea here was to create a duplicate of Table_A named Table_A_NEW, absent the FK constraint. We would then find a way to “swap” this new table for the original, the hope being that Table_B will then continue to create rows, without being stopped by the FK constraint. Since sp_rename only involves locks that affect the two tables directly, it does not impact Table_B at all, since the sp_rename is does not involve the FK constraint