Overview - Ghost

Ghost

7 days30 days

Latest CI Pipeline Executions

Fuzzy

Succeeded
29460
131d9df1 Fixed deadlock when recording the send of automation emails closes https://linear.app/ghost/issue/NY-1481 ref e9ef52762c386dd03a0effbd3375106f5e026c19 What ---- When we record the send of an automation email, we do two things: 1. Insert an `automated_email_recipients` row 2. Increment `automation_action_revisions.email_sent_count` This patch switches the order. Why --- To fix a race condition. We recently saw an error like this (reformatted slightly): ``` Failed to record automated email recipient for step abc123: update `automation_action_revisions` set `email_sent_count` = COALESCE(`email_sent_count`, 0) + 1 where `id` = 'abc123' Deadlock found when trying to get lock; try restarting transaction ``` Why did this happen? Before I explain why this happened, you need to a couple of things about [shared and exclusive locks in MySQL][0]: - **Shared locks** lock rows for reading. - **Exclusive locks** lock rows for updates. - If you try to get an exclusive lock on a row, you wait for existing locks to be released first. - You can get stuck waiting for a lock, which causes deadlock errors like this. This bug can happen when tracking the send for the same revision simultaneously. Here's a scenario: 1. Transaction A is opened. It wants to insert Recipient X and increment Revision R. 2. Transaction B is opened. It wants to insert Recipient Y and increment Revision R. 3. Transaction A goes to insert Recipient X. *Because its `automation_action_revisions` row is a foreign key reference*, MySQL acquires a *shared lock* on Revision R to prevent it from being deleted. 4. Transaction B goes to insert Recipient Y. Same thing: a *shared lock* is acquired on Revision R. 5. Transaction A goes to increment Revision R's `email_sent_count`. Before it can do that, it requests an *exclusive lock*, waiting on Transaction B's shared lock. 6. Transaction B does the same. It can't get its lock because it's waiting on the shared lock from Transaction A. A deadlock occurs! The fix is straightforward: switch the order. Now the following will happen: 1. Transaction A is opened. It wants to insert Recipient X and increment Revision R. 2. Transaction B is opened. It wants to insert Recipient Y and increment Revision R. 3. Transaction A acquires an exclusive lock on Revision R. 4. Transaction B requests an exclusive lock on Revision R, but is blocked. 5. Transaction A finishes. 6. Transaction B is unblocked and finishes. [0]: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-shared-exclusive-locks
by Evan Hahn