Making use of FOR SHARE and FOR UPDATE

Sometimes, data is selected from the database; then some processing happens in the application and, finally, some changes are made back on the database side. This is a classic example of SELECT FOR UPDATE.

Here is an example:

BEGIN;
SELECT * FROM  invoice WHERE  processed = false; 
** application magic  will  happen here  **  
UPDATE invoice SET processed = true  ...  
COMMIT; 

The problem here is that two people might select the same unprocessed data. Changes made to these processed rows will then be overwritten. In short, a race condition will occur.

To solve this problem, developers can make use of SELECT FOR UPDATE. Here is how it works:

BEGIN; 
SELECT * FROM invoice WHERE processed = false FOR UPDATE; 
** application magic  will  happen here  **  
UPDATE invoice SET processed = true ...  
COMMIT; 

The SELECT FOR UPDATE will lock rows just like an UPDATE would. This means that no changes can happen concurrently. All locks will be released on COMMIT as usual.

If one SELECT FOR UPDATE is waiting for some other SELECT FOR UPDATE, one has to wait until the other one completes (COMMIT or ROLLBACK). If the first transaction does not want to end, for whatever reason, the second transaction might potentially wait forever. To avoid this, it is possible to use SELECT FOR UPDATE NOWAIT.

Here is how it works:

If   NOWAIT is not flexible enough for you, consider using lock_timeout. It will contain the amount of time you want to wait on locks. You can set this on a per-session level:

test=# SET lock_timeout TO 5000;  
SET 

In this, the value is set to 5 seconds.

While SELECT does basically no locking, SELECT FOR UPDATE can be pretty harsh. Just imagine the following business process: we want to fill up an airplane providing 200 seats. Many people want to book seats concurrently. In this case, the following might happen:

The trouble is that only one seat can be booked at a time. There are potentially 200 seats available but everybody has to wait for the first person. While the first seat is blocked, nobody else can book a seat even if people don't care which seat they get in the end.

SELECT FOR UPDATE SKIP LOCKED will fix the problem. Let's create some sample data first:

test=# CREATE TABLE t_flight AS 
  SELECT * FROM generate_series(1, 200) AS id;  
SELECT 200 

Now comes the magic:

If everybody wants to fetch two rows, we can serve 100 concurrent transactions at a time without having to worry about blocking transactions.

However, there is more. In some cases, a FOR UPDATE can have unintended consequences. Most people are not aware of the fact that FOR UPDATE will have an impact on foreign keys. Let's assume that we have two tables: one to store currencies and the other to store accounts:

CREATE TABLE t_currency (id int, name text, PRIMARY KEY (id));  
INSERT INTO t_currency VALUES (1, 'EUR');       
INSERT INTO t_currency VALUES (2, 'USD'); 
 
CREATE TABLE t_account (
       id           int, 
       currency_id  int         REFERENCES t_currency (id) 
                                      ON UPDATE CASCADE 
                                      ON DELETE CASCADE, 

       balance      numeric); 
INSERT INTO t_account VALUES (1, 1, 100);  
INSERT INTO t_account VALUES (2, 1, 200);

Now, we want to run SELECT FOR UPDATE on the account table:

Although there is a SELECT FOR UPDATE on accounts, the UPDATE on the currency table be will be blocked. This is necessary because otherwise, there is a chance of breaking the foreign key constraint altogether. In a fairly complex data structure, you can therefore easily end up with contentions in an area where they are least expected (some highly important lookup tables).

On top of FOR UPDATE, there are FOR SHARE, FOR NO KEY UPDATE, and FOR KEY SHARE. The following listing describes what these modes actually mean:

• FOR NO KEY UPDATE: This one is pretty similar to FOR UPDATE. However, the lock is weaker and, therefore, it can coexist with SELECT FOR SHARE.
• FOR SHARE: FOR UPDATE is pretty strong and works on the assumption that you are definitely going to change rows. FOR SHARE is different because more than one transaction can hold a FOR SHARE lock at the same time.
• FOR KEY SHARE: This behaves similarly to FOR SHARE, except that the lock is weaker. It will block FOR UPDATE but will not block FOR NO KEY UPDATE.

The important thing here is to simply try things out and observe what happens. Improving locking behavior is really important as it can dramatically improve the scalability of your application.