SELECT FOR UPDATE Statement in Postgres

The SELECT FOR UPDATE statement in PostgreSQL allows us to implement the concurrent query executions and prevent transaction conflicts during data updates.

In this tutorial, we will discuss what this command does, how it works, and provide some examples of when it may come in handy.

Please note that this tutorial assumes that you already have an installed PostgreSQL and you know how to connect to a database.

PostgreSQL SELECT FOR UPDATE Statement

Before diving into the practical parts on how to work with the SELECT FOR UPDATE clause, let us understand what this command does and why it is functional.

The SELECT FOR UPDATE clause is like a locking mechanism that locks the rows in a given database to prevent other transactions from modifying the data that is stored in that table until the current transaction is complete.

If another transaction tries to update or delete the locked rows, it must wait until the locks are released. This feature is extremely useful, especially in concurrent database environments, as it prevents data loss and inconsistencies.

PostgreSQL SELECT FOR UPDATE Examples

To better understand how this command works, look at a practical example with a database table.

Let us start by setting up a test table called “order”. We can run the query as follows:

We can then insert the sample data as follows:

Once the table is set up, we can learn how to use the SELECT FOR UPDATE. The command syntax is as follows:

For example, suppose we wish to update the OrderStatus for OrderID = 1 from pending to processing. Then, we can use the SELECT FOR UPDATE command to prevent other transactions from updating the same row while the current transaction is in progress.

The command locks the row with OrderID = 1 for the update. Any other transactions that try to update or delete this row will be blocked until the current transaction ends.

Once we update the row, we MUST end the transaction with the COMMIT command. This relases the lock and allows you or any other session to modify the rows.

There are some key factors to consider before locking the rows using the SELECT FOR UPDATE clause.

For example:

1. Always keep the transactions as short as possible to minimize the time when the rows are locked. This can, in turn, help with concurrency issues and resource unavailability.
2. Always remember to COMMIT the transactions. If a transaction is uncommitted, the target rows will remain locked until the parent session is terminated. This can be a huge problem for other clients that attempt to perform the actions on the database.
3. Once the rows are locked, they can only be unlocked via terminating the session, commit, or a rollback.

PostgreSQL SELECT FOR UPDATE NOWAIT Statement

PostgreSQL also offers us the SELECT FOR UPDATE NOWAIT command which allows us to acquire a lock on selected rows. The command attempts to acquire a lock for the locked rows or return an error if another transaction locks the rows.

Consider the example as follows:

The benefit of NOWAIT is that it prevents the transaction from waiting indefinitely if a long-running transaction locks the row.

PostgreSQL SELECT FOR UPDATE SKIP LOCKED Statement

Another statement that we can use in concurrent situations is the SELECT FOR UPDATE SKIP LOCKED clause.

This clause allows us to process the rows that are not locked while ignoring the ones that are locked.

Example:

In this case, the SKIP LOCKED option enables the query to skip over any already locked rows.

Conclusion

We learned how we could work with the SELECT FOR UPDATE clause in PostgreSQL to offer concurrent transactions in a given database without concurrency-related issues.