Are Nested Sql Queries Atomic?

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

Solution 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 ISNULLFORUPDATESKIP LOCKED
               LIMIT 1)
RETURNING port;

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