Use `UPDATE` with `FOR UPDATE SKIP LOCKED` CTE

[YeonwooSung]

Image you are designing logic for short URL generation system.
Your API need to find if there is any expired short URL, and assign it to the new user.
In this case, it would be useful to use CTE for selection and update for write new updates.
Below is the sample SQL query:

WITH candidate as (
    SELECT *
    FROM UrlMappings
    WHERE expiryTime < NOW()
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE UrlMappings
SET expiryTime = NOW() + interval '1 year',
        user_id = 4,
        long_url = "mini-mini-moe.rock-scissor-paper-toast.com"
FROM candidate
WHERE UrlMappings.id = candidate.id
RETURNING UrlMappings.*;

In the query above, we use FOR UPDATE SKIP LOCKED clause, which is used for concurrency safeness.
It protects the server to claim same expired url to more than 2 processes!

SKIP LOCKED and RETURNING clauses are PostgreSQL specific syntax!