Preventing Double Execution from Race Conditions with PostgreSQL Partial Unique Indexes
機械翻訳 / Machine-translated
機械翻訳 / Machine-translated
When a bulk-action button is double-clicked or re-executed in another tab, a graceful skip at the application layer alone cannot prevent the phantom race under READ COMMITTED, allowing duplicate rows to be created. We designed a solution that serializes with a partial unique index and detects conflicting INSERTs as error 23505.
For bulk-action buttons in school administrator dashboards — such as "Copy to Next Academic Year" — a "graceful skip" approach that checks for existing rows before inserting is not sufficient to prevent duplicate rows caused by double-clicks or re-execution in another tab. Under READ COMMITTED, if two transactions perform an existence-check SELECT at the same moment, both will see "0 rows" and both will proceed to INSERT. This phantom race is unobservable from the application layer, so we delegated final serialization to a partial unique index at the database level, designing the system to detect conflicting INSERTs as 23505 and return them as conflicts.
Detailed steps, how to verify the behavior, and frequently asked questions are summarized in the original article.