嵌套的SQL查询是原子的吗? [英] Are nested SQL queries atomic?

问题描述

我有一些微服务(几乎)系统，其中每个程序都有自己的端口.此外，并非系统中的所有端口都可用.为简单起见，我决定在PostgreSQL的表中保留可用端口的列表.因此，我有这张桌子:

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
...

分别，如果service列中的值为null，则该端口为空闲状态，否则为繁忙状态.但是，如果先选择所有空闲端口，然后再占用其中一个，则逻辑上是非原子的，这是合乎逻辑的.这可能导致两种服务将尝试占用同一端口的情况.

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.

我正在尝试使此操作原子化.为此，我决定不进行选择，而是立即进行更新.但是，这里我缺乏SQL知识，在Internet上找不到关于此主题的任何内容.我的最终要求:

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?

推荐答案

您应该编写子查询，以便它针对同时进行的修改锁定找到的行:

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 UPDATE SKIP LOCKED
               LIMIT 1)
RETURNING port;

SKIP LOCKED 使查询忽略锁定的行，而不是等待锁定.

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

这篇关于嵌套的SQL查询是原子的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！