Postgres Concurrency Control: SELECT FOR UPDATE

This video introduces how to use the `SELECT FOR UPDATE` statement in PostgreSQL to address concurrency problems. It begins by setting up a common scenario involving an `accounts` table in a financial application, which typically includes an `ID` and a `balance` column. This table structure is fundamental for understanding how transactions interact with stored money or similar values, where data consistency is paramount.

Initially, a simple dataset is inserted into the `accounts` table, featuring a single row with an `ID` and an initial balance of 100. The standard process for updating this balance is demonstrated through a new transaction. An application queries the current balance, applies business logic (e.g., checking if the balance is sufficient for a 100 withdrawal), and then updates the row by subtracting 100. After committing the transaction, the balance correctly reflects 0, illustrating expected behavior in a single-client environment.

The standard transaction flow involves several distinct steps: starting a new transaction, querying the current value of the balance, applying specific business logic (such as verifying if the balance is greater than or equal to 100), and then conditionally updating the row. For example, if the balance check passes, 100 is subtracted from the balance, simulating a withdrawal or expenditure. Finally, the transaction is committed, making the changes permanent and reflecting the new balance of 0, which is the desired outcome for a single, sequential operation.

In real-world scenarios, multiple clients often make concurrent requests to the database, leading to potential concurrency problems. An example demonstrates two separate transactions, Transaction A and Transaction B, both attempting to update the same row simultaneously. Each transaction queries the initial balance of 100, intending to subtract 100. When Transaction A commits its update, the row is locked during its execution, causing Transaction B to wait. Once Transaction A finishes, Transaction B's update proceeds.

The core issue arises when both transactions proceed without proper synchronization. Transaction A selects the balance, sees 100, and decides to subtract 100, committing to a balance of 0. Concurrently, Transaction B also selects the balance, sees 100 (as Transaction A's commit hasn't been seen yet), and also subtracts 100. When Transaction B commits, it overwrites Transaction A's result, leading to a final balance of -100, which is an incorrect and undesirable state.

Now it says minus 100 which is not good. This is not the expected result. So that's the problem right here.

To prevent the concurrency problem of incorrect balances, `SELECT FOR UPDATE` is introduced as a solution. This statement modifies the `SELECT` query in both transactions by appending `FOR UPDATE`. The crucial difference is that this statement locks the row immediately after it is selected, rather than waiting for the `UPDATE` statement. This proactive locking ensures that any subsequent business logic performed on the selected data is based on an exclusive view of the row, preventing other transactions from interfering until the current transaction completes.

Implementing `SELECT FOR UPDATE` ensures that a row is locked right after the `SELECT` statement executes. Previously, the lock was only applied when the `UPDATE` statement was executed. By locking the row earlier, `SELECT FOR UPDATE` covers the critical period where an application performs business logic between reading the data and writing the update. This prevents other transactions from modifying the row or reading its potentially stale state during this decision-making interval, thereby preserving data consistency.

The effectiveness of `SELECT FOR UPDATE` is demonstrated by re-running the two-transaction scenario. Both Transaction A and Transaction B begin, but when Transaction A executes `SELECT FOR UPDATE`, it acquires an immediate lock on the target row. Consequently, when Transaction B attempts its own `SELECT FOR UPDATE` on the same row, it is forced to wait until Transaction A finishes. Once Transaction A completes its update and commits, releasing the lock, Transaction B can then proceed. By this point, Transaction B reads the updated balance (now 0) and correctly determines that no further withdrawal is possible, thus preventing the negative balance issue.

The demonstration vividly shows that after Transaction A commits, the `SELECT FOR UPDATE` statement in Transaction B, which was previously pending, successfully executes. It retrieves the updated balance of 0. At this point, the application logic in Transaction B correctly identifies that the balance is insufficient for another 100 withdrawal. Therefore, Transaction B should not proceed with its `UPDATE` statement, and the transaction can be rolled back or handled appropriately. This ensures that the final balance remains consistent, avoiding the problematic -100 outcome observed in the race condition scenario.

`SELECT FOR UPDATE` is a highly recommended solution in many PostgreSQL concurrency control scenarios, as highlighted in various Postgres books. However, it is not a universally applicable solution and has specific limitations. One notable challenge arises when dealing with foreign keys. If a row is locked using `SELECT FOR UPDATE`, it not only blocks direct updates to that row but can also inadvertently lock `INSERT` statements on other tables that reference this locked row via a foreign key. This can lead to unexpected delays or deadlocks in more complex database schemas.

The interaction with foreign keys represents a significant limitation. When `SELECT FOR UPDATE` locks a row, any `INSERT` operation into a separate table that contains a foreign key referencing the locked row will be forced to wait. This occurs because the database needs to ensure referential integrity, and it cannot validate the new foreign key reference against a row that is currently locked for update. Consequently, this can stall parts of an application that are trying to insert related data, potentially causing performance bottlenecks or even application freezes. Therefore, while a powerful tool, `SELECT FOR UPDATE` requires careful consideration of its broader implications on the database schema and concurrent operations.