March SFPUG: Foreign Key Locks and their Discontents
Details
What: how to avoid FK deadlocks
Who: CB, DBA at Etsy.com
When: 7pm Tuesday March 15th
Where: EZRez offices (address will be updated)
Food & drink Sponsored by EZRez, but only if you RSVP!
EzRez: the leader in online travel booking services: http://ezrez.com
Description:
I am a Database Engineer at Etsy.com, a major online consumer retail site. We've long experienced problems with deadlocks that involved ShareLocks that the application was not explicitly making. It turns out that Postgres itself was creating the ShareLocks, as part of the way it enforces referential integrity of foreign key constraints. This is not well documented, and little understood amongst Postgres users, but it has caused us grief, and no doubt others; this can be a problem even with very simple schemas and applications. Our own experience at Etsy has led us to gain an understanding of how Postgres creates these ShareLocks, and how to recognize that they may be causing deadlocks. We have found that once recognized, these deadlocks can be avoided with relatively small changes to the application code. The presentation will describe the phenomoenon, and how to recognize its signature pattern in the postgres logs, and how to design or update an application to avoid the problem.
March SFPUG: Foreign Key Locks and their Discontents