sql - 同一事务的两个语句与单个语句的隔离级别

Question

我有下表:

DROP TABLE IF EXISTS event;
CREATE TABLE event(
    kind VARCHAR NOT NULL,
    num  INTEGER NOT NULL
);
ALTER TABLE event ADD PRIMARY KEY (kind, num);

我的想法是，我想使用 num 列为每个 kind 事件维护单独的增量计数器。所以 num 就像是针对每个不同的 kind 的专用自动递增序列。

假设多个客户端/线程同时将事件(可能是相同的 kind)写入该表，在事务隔离所需级别方面是否存在差异:(a) 执行以下操作 block :

BEGIN TRANSACTION;
DO
$do$
DECLARE
    nextNum INTEGER;
BEGIN    
    SELECT COALESCE(MAX(num),-1)+1 FROM event WHERE kind='A' INTO nextNum;
    INSERT INTO event(kind, num) VALUES('A', nextNum);
END;
$do$;
COMMIT;

... 和 (b) 将 select 和 insert 合并到一个语句中:

INSERT INTO event(kind, num)
(SELECT 'A', COALESCE(MAX(num),-1)+1 FROM event WHERE kind='A');

从我运行的一些测试看来，在这两种情况下我都需要 serializable transaction isolation level .更重要的是，即使使用 serializable 事务隔离级别，由于在高并发情况下出现以下错误，我的代码也必须准备重试:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.  

换句话说，将 select 合并到 insert 似乎不会在原子性方面带来任何好处，也不会允许设置任何更低/更宽松的事务隔离级别.我错过了什么吗？

( This question 广泛相关，因为它要求使用 PostgreSQL 模式来促进表内多个序列的生成。所以要明确一点，我并不是要求正确的模式来做这类事情；我只是想了解两个语句 block 是否与单个合并的 INSERT/SELECT 语句有任何不同)。

Answer 1

任务的问题是可能的并发写访问。将 SELECT 和 INSERT 合并到一个语句中将时间范围 可能的冲突降至最低，并且在任何情况下都是更好的方法。冲突的可能性仍然存在。是的，可序列化事务隔离是一种可能的(如果昂贵的话)解决方案。

通常(但这不是你要问的)，最好的解决方案是不要尝试你正在尝试的。无缝序列号在具有并发写入访问权限的数据库中是一个难题。如果可能，请改用 serial 列，它会为您提供唯一的升序数字 - 可能存在间隙。您可以稍后或在 VIEW 中动态消除间隙。详情:

• Serial numbers per group of rows for compound key

另外:您不需要在 SELECT 两边加上括号:

INSERT INTO event(kind, num)
SELECT 'A', COALESCE(MAX(num) + 1, 0) FROM event WHERE kind='A';