如何选择事务隔离级别？

Question

我有一个数据库中的表，负责存储有序/可重新排序的列表。它的形式如下:

| id | listId | index | title | ... |

其中id是主键，listId是外键，用于标识该项属于哪个列表，title和其他列是项目的内容。 index属性负责项目在列表中的位置。它是一个整数计数器（从0开始），在列表范围内是唯一的，但可能在列表之间重复。示例数据：

| id      | listId  | index | title    | ...
---------------------------------------------
| "item1" | "list1" | 0     | "title1" | ...
| "item2" | "list1" | 1     | "title2" | ...
| "item3" | "list1" | 2     | "title3" | ...
| "item4" | "list2" | 0     | "title4" | ...
| "item5" | "list2" | 1     | "title5" | ...

用户可以创建/删除条目，将它们在列表内或列表之间移动。

为确保执行这些操作时索引的一致性，我采取以下措施：

创建条目：

1. 计算此列表中的项目数

SELECT COUNT(DISTINCT "Item"."id") as "cnt" 
FROM "item" "Item" 
WHERE "Item"."listId" = ${listId}

2. 插入新项，索引从步骤1计数：

INSERT INTO "item"("id", "listId", "index", "title", ...) 
VALUES (${id}, ${listId}, ${count}, ${title})

这样，每插入一个项目，索引就会随之增长。

移动项目:

1. 检索项目的当前listId和索引：

SELECT "Item"."listId" AS "Item_listId", "Item"."index" AS "Item_index" 
FROM "item" "Item" 
WHERE "Item"."id" = ${id}

2. 如有必要，更改“移动”的项目的索引，以保持顺序一致。例如，如果将项目向前移动，则其当前位置（不包括）和下一个位置（包括）之间的所有项目都需要将其索引减少1：

UPDATE "item" 
  SET "index" = "index" - 1 
WHERE "listId" = ${listId} 
  AND "index" BETWEEN ${sourceIndex + 1} AND ${destinationIndex}

我会忽略跨列表移动的变化，因为它非常相似。

3. 更新该项本身：

UPDATE "item" 
   SET "index" = ${destinationIndex} 
WHERE "id" = ${id}

删除项目:

1. 检索项目的索引和列表ID

2. 将与此项相邻的同一列表中的所有项目向后移动1步，以消除间隙

UPDATE "item" 
  SET "index" = "index" - 1 
WHERE "listId" = ${listId} 
  AND "index" > ${itemIndex}

删除项目：

DELETE FROM "item" 
WHERE "id" = ${id}

问题是：

每个操作应提供哪些事务隔离级别？对于我来说保持索引列的一致性非常重要，不能有间隙，并且最重要的是不能有重复。我理解得对吗，创建项目操作受幽灵读取影响，因为它按某些条件计算项目数量，所以应该是可串行化的吗？其他操作呢？

- Kit Isaev

---

无论您选择哪种隔离级别以及如何更改表中的数据，都应使用 (ListID, Index) 上的唯一索引强制执行最重要的约束条件 - 不允许重复。唯一索引约束条件很容易实现并且始终保持不变。 - Vladimir Baranov

当删除记录时，降低大于已删除记录1的所有记录的ID可能会在数据量大时导致性能问题。我的建议：

1. 创建一个序列对象，并将其用于新Id。不要使'自动递增'到Id列，并使用序列手动管理它们。
2. 当记录被删除时，将具有大于已删除记录的Id的记录的Id减少1。并在同一事务中将序列值减小1。
3. 使用“读未提交”类型作为隔离级别。

- Gurcan

我要警告你，如果你在UI操作中使用Id，当记录被删除时更新Id会给你带来巨大的操作问题。因为，另一个人可以同时编辑具有更大Id值的另一条记录。 - Gurcan

@Gurcan 我需要ids是持久的/无序的，并且更喜欢它们是uuid而不是整数。因此，id和index是单独的列。增加/减少记录索引对我来说不是问题，因为该列不打算拥有超过~10个项目。 - Kit Isaev

所以，从基本设计上看起来还不错。索引序列机制可以按照我建议的方式进行。你同意吗？ - Gurcan

Answer 1

如果不了解你的具体应用程序，最安全的选择确实是在访问该表时使用可序列化作为隔离级别，但即使这个级别可能对你的具体情况也不足够。

(listId, index)上的唯一约束可以防止重复（标题呢？它可以在同一列表中重复吗？），一些精心制作的"看门狗"查询可以进一步减轻问题，数据库序列或存储过程可以确保没有间隙，但事实上机制本身似乎很脆弱。

只知道你的问题很具体，你所遇到的是用户级别的并发问题，因为多个用户可以同时访问同一对象并对其进行更改。假设这是您典型的Web应用程序，具有无状态后端（因此本质上是分布式的），这可能会在用户体验方面产生大量影响，并反映在架构甚至功能要求上。例如，假设用户Foo将项目Car移动到当前由用户Bar处理的List B中。然后可以合法地假设，只要操作完成，Bar就需要立即看到项目Car，但是除非有某种机制立即通知List B的用户更改，否则不会发生。当您有更多用户在同一组列表上工作时，即使有通知，情况也会变得更糟，因为您会有越来越多的通知，直到用户看到事物不断变化，无法跟上它。

有很多假设会影响到回答的内容。我个人的看法是，你可能需要修订该应用程序的要求或确保管理层知道几个限制并接受它们。

这种问题在分布式应用程序中非常常见。通常会将某些数据集上的“锁”（通过数据库或共享内存池）放置，以便每次只能有一个用户对它们进行更改，或者提供工作流来处理冲突操作（类似于版本控制系统）。当两者都没有时，会记录操作日志以了解发生了什么，如果发现问题，则可以稍后纠正。

- Filippo Possenti

---

该应用程序是类似Trello的看板。更新通过WebSocket实时传递到前端，由于看板仅限于约20个用户，因此我不期望出现多个用户同时移动同一张卡片的冲突。另一方面，前端渲染/UI逻辑依赖于一致的索引排序，重复的索引或间隙可能导致未定义的行为。而且，保证后端的一致性似乎比尝试使前端意识到可能的不一致更加清晰。 - Kit Isaev

在这种情况下，“可序列化”可能过于严格。尝试使用“可重复读取”，但同时，在服务器端（而不是数据库端）实现一些逻辑来序列化对同一板块执行的操作。一种方法是为每个“当前活动”的板块构建一个对象，然后在该对象上使用synchronized阻止用户同时访问，从而序列化操作。另一种（可能更好的）方法是使用某种队列机制来序列化操作并在websocket上返回反馈。 - Filippo Possenti

Answer 2

根据您的限制，您可以在两个列上创建唯一索引：listId，index 可以定义为唯一。这将避免重复。

此外，为了避免间隙，我建议：

select listId, index, (select min(index) from Item i2 where listId = :listId and i2.index > i1.index) as nextIndex from Item i1 where nextIndex - index > 1 and listId = :listId

在每个事务结束时。与事务隔离级别："可重复读"和回滚并重复事务，如果唯一约束失败或我建议的语句返回记录，则应满足您的要求。

- aschoerk

---

将选择条件用作某种“模拟约束”的想法很聪明，我没有想到。不幸的是，唯一索引不起作用，因为列并不完全唯一。我在两个写操作中移动/删除它们（首先移动相邻项，然后移动/删除项本身），操作1将导致违反此索引。 - Kit Isaev

根据数据库管理系统的不同，唯一键约束可能会在事务结束时进行检查（请参见：https://www.postgresql.org/docs/9.1/index-unique-checks.html），那么您在移动时就不会遇到问题。否则，请扩展建议的选择语句以检测相同索引但不同ID的情况。 - aschoerk