Are nested SQL queries atomic?

Question

I have some micro service (almost) system where each program has its own port. Moreover, not all ports in the system are available. For simplicity, I decided to keep a list of free ports in a table in PostgreSQL. Accordingly, I have this table:

service | port
-------------------
sm_srv  | 5600
null    | 5601
...

Respectively, if the value in the service column is null, then the port is free, otherwise it is busy. However, it is logical that if you first select all the free ports, and then occupy one of them, the operation becomes non-atomic. This can lead to a situation where two services will try to occupy the same port.

I'm trying to make this operation atomic. To do this, I decided not do select, but do update immediately. However, here I was faced with a lack of knowledge in SQL and could not find anything on this topic on the Internet. My final request:

UPDATE table 
  SET service='asd' 
WHERE port IN (SELECT port FROM table WHERE service IS NULL LIMIT 1)
RETURNING port;

Question: is such an operation atomic? Or maybe I can make what I need easier somehow?

Answer 1

You should write the subquery so that it locks the row it has found against concurrent modifications:

UPDATE services                                      
   SET service='asd'
WHERE port IN (SELECT port
               FROM services
               WHERE service IS NULL
               FOR NO KEY UPDATE SKIP LOCKED
               LIMIT 1)
RETURNING port;

The SKIP LOCKED causes the query to ignore locked rows rather than wait behind the lock.