SSI

来自 PostgreSQL Wiki
跳转到导航跳转到搜索


PostgreSQL 中的可序列化快照隔离 (SSI) 与普通快照隔离 (SI) 相比的文档。从 PostgreSQL 9.1 版本开始，这分别对应于串行化和可重复读取事务隔离级别。

概述

对于真正的可序列化事务来说，如果你能够证明事务在没有并发事务的情况下会执行正确，那么在任何可序列化事务组合中它也能正常执行，或以错误回滚。然后可以从头重新尝试该事务。尽管该错误通常是串行化失败，但它也可能是表示约束违反的错误。失败的可序列化事务的任何查询结果都必须忽略；使用重试的结果。

本文件显示了可重复读取事务隔离级别下事务的某些组合可能出现的问题，以及从 PostgreSQL 9.1 版本开始如何在可序列化事务隔离级别中避免这些问题。

本文件面向应用程序员或数据库管理员。有关 SSI 实现的内部信息，请参阅可序列化 Wiki 页面。有关如何使用此隔离级别的更多信息，请参阅当前的 PostgreSQL 文档。

示例

对于通过依赖于 SSI 避免基于锁定的完整性保护的环境，数据库通常在 (postgresql.conf) 中配置为

default_transaction_isolation = 'serializable'

因此，所有示例都使用此设置进行测试，并且通过使用简单的 "begin" 避免混乱，而无需显式声明每个事务的事务隔离级别。

简单的写入偏差

当两个并发事务通过读取与另一个事务写入的内容重叠的数据集来确定自己正在写入的内容时，您可能得到如果其中一个事务在另一个事务之前运行则不会出现的状态。这称为写入偏差，是 SSI 保护您的最简单的序列化异常形式。

当 SSI 中存在写入偏差时，两个事务都会继续进行，直到一个事务提交为止。第一个提交者获胜，另一个事务回滚。该“第一个提交者获胜”规则确保了进度，而且可以立即重试回滚的事务。

黑白

在本例中，有些行有一个颜色列，其中包含“黑色”或“白色”。两个用户同时尝试让所有行的颜色值相匹配，但它们的尝试方向相反。一个尝试将所有白色行更新为黑色，另一个尝试将所有黑色行更新为白色。

如果这些更新是按顺序运行的，则所有颜色都将匹配。如果它们在 REPEATABLE READ 模式的并发模式下运行，则这些值将被切换，这不符合任何顺序的运行。如果它们在 SERIALIZABLE 模式下的并发模式下运行，SSI 会注意到写入偏差并会回滚其中一个事务。

可以使用以下语句设置该示例

create table dots
  (
    id int not null primary key,
    color text not null
  );
insert into dots
  with x(id) as (select generate_series(1,10))
  select id, case when id % 2 = 1 then 'black'
    else 'white' end from x;
黑白示例
会话 1 会话 2
begin;
update dots set color = 'black'
  where color = 'white';

begin;
update dots set color = 'white'
  where color = 'black';

此时其中一个事务注定会失败。

commit;

第一个提交者获胜。

select * from dots order by id;

 id | color
----+-------
  1 | white
  2 | white
  3 | white
  4 | white
  5 | white
  6 | white
  7 | white
  8 | white
  9 | white
 10 | white
(10 rows)

它像自己运行一样运行。

commit;

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

序列化失败。我们回滚并重试。

rollback;
begin;
update dots set color = 'black'
  where color = 'white';
commit;

没有并发事务干扰。

select * from dots order by id;

 id | color
----+-------
  1 | black
  2 | black
  3 | black
  4 | black
  5 | black
  6 | black
  7 | black
  8 | black
  9 | black
 10 | black
(10 rows)

此事务在其他事务之后独自运行。

相交数据

此示例取自 PostgreSQL 文档。两个并发事务读取数据，每个事务都使用该数据来更新另一个事务读取的范围。一个简单但有些牵强的数据偏差示例。

可以使用以下语句设置该示例

CREATE TABLE mytab
(
  class int NOT NULL,
  value int NOT NULL
);
INSERT INTO mytab VALUES
(1, 10), (1, 20), (2, 100), (2, 200);
相交数据示例
会话 1 会话 2
BEGIN;
SELECT SUM(value) FROM mytab WHERE class = 1;

 sum
-----
  30
(1 row)

INSERT INTO mytab VALUES (2, 30);

BEGIN;
SELECT SUM(value) FROM mytab WHERE class = 2;

 sum
-----
 300
(1 row)

INSERT INTO mytab VALUES (1, 300);

每个事务都修改了另一个事务会读取到的数据。如果允许这两项提交，这将破坏可序列化行为，因为如果它们逐个运行，则其中一项事务会看到另一个提交的 INSERT。我们在回滚任何内容之前等待其中一个事务成功提交，以便确保进度并防止崩溃。

COMMIT;

COMMIT;

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

因此，现在我们回滚失败的事务并从头开始重试。

ROLLBACK;
BEGIN;
SELECT SUM(value) FROM mytab WHERE class = 1;

 sum
-----
 330
(1 row)

INSERT INTO mytab VALUES (2, 330);
COMMIT;

此操作成功，最终结果与按顺序执行事务一致。

SELECT * FROM mytab;

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200
     1 |   300
     2 |   330
(6 rows)

透支保护

假设银行允许存款人在他们的全部账户中提取的总额以内提取钱财。银行会自动转帐，以平衡各个账户并结束每个账户一天的余额。在单一交易中，会检查各个账户的总额是否超过所要求的金额。

某个用户试图通过对两个账户余额均为 500 美元的账户提交 900 美元的提取请求来利用银行系统漏洞。在 REPEATABLE READ 事务隔离级别下，这会成功；但如果使用 SERIALIZABLE 事务隔离级别，SSI 会检测到读取/写入模式中的“危险结构”并拒绝其中的一个事务。

可以使用以下语句设置该示例

create table account
  (
    name text not null,
    type text not null,
    balance money not null default '0.00'::money,
    primary key (name, type)
  );
insert into account values
  ('kevin','saving', 500),
  ('kevin','checking', 500);
透支保护示例
会话 1 会话 2
begin;
select type, balance from account
  where name = 'kevin';

   type   | balance
----------+---------
 saving   | $500.00
 checking | $500.00
(2 rows)

总额是 1000 美元，因此 900 美元的提取请求是允许的。

begin;
select type, balance from account
  where name = 'kevin';

   type   | balance
----------+---------
 saving   | $500.00
 checking | $500.00
(2 rows)

总额是 1000 美元，因此 900 美元的提取请求是允许的。

update account
  set balance = balance - 900::money
  where name = 'kevin' and type = 'saving';

至此，一切正常。

update account
  set balance = balance - 900::money
  where name = 'kevin' and type = 'checking';

这时出了问题。这与其他事务活动不能共存。我们尚未取消，因为如果重试，该事务会在相同冲突上失败。第一个提交者将会赢，另一个事务会尝试继续，然后失败。

commit;

这是碰巧第一个提交的事务。其工作得以持续。

commit;

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

该事务未提取到钱款。现在我们回滚并重试该事务。

rollback;
begin;
select type, balance from account
  where name = 'kevin';

   type   | balance
----------+----------
 saving   | -$400.00
 checking |  $500.00
(2 rows)

我们看到净额为 100 美元。应用会拒绝此 900 美元的请求。

三个或更多事务

更复杂的访问模式可能导致串行化异常，包括三个或更多事务。

基本色

这与“黑与白”写偏差示例类似，除了我们使用三种原色。一个事务尝试将红色更新为黄色，下一个尝试将黄色更新为蓝色，第三个尝试将蓝色更新为红色。如果一个接一个地执行这些操作，你会在表中留下一种或两种颜色，具体取决于执行顺序。如果两个同时执行，正在尝试读取另一个正在更新的行的事务将会率先执行，因为它不会看到另一个事务的工作，因此不会发生问题。无论另一个事务是在它之前还是之后运行，结果都与某种串行执行顺序一致。

如果三个全部同时运行，则在外表的执行顺序中存在循环。Repeatable Read 事务不会检测到这一点，且表仍会有三种颜色。Serializalble 事务会检测到问题，并使其中一个事务回滚，出现串行化失败。

可以使用以下语句设置该示例

create table dots
  (
    id int not null primary key,
    color text not null
  );
insert into dots
  with x(id) as (select generate_series(1,9000))
  select id, case when id % 3 = 1 then 'red'
    when id % 3 = 2 then 'yellow'
    else 'blue' end from x;
create index dots_color on dots (color);
analyze dots;
基本色示例
会话 1 会话 2 会话 3
begin;
update dots set color = 'yellow'
  where color = 'red';

begin;
update dots set color = 'blue'
  where color = 'yellow';

begin;
update dots set color = 'red'
  where color = 'blue';

在这一点上，这三个事务中至少有一个注定会失败。要确保进程，我们等待直到其中一个提交为止。提交会成功，这会不仅确保已完成工作，且一个已失败事务的立即重试在同一事务组合中不会再次失败。

commit;

第一次提交获胜。第 2 个会话在这一步确定会失败，因为在提交期间，已确定如果立即重试，该会话成功的几率更大。

select color, count(*) from dots
  group by color
  order by color;

 color  | count
--------+-------
 blue   |  3000
 yellow |  6000
(2 rows)

这似乎在其他更新之前已运行。

commit;

如果此时尝试，此操作会成功。如果第 2 个会话先做了更多工作，则此事务可能也需要取消并重试。

select color, count(*) from dots
  group by color
  order by color;

 color  | count
--------+-------
 red    |  3000
 yellow |  6000
(2 rows)

这似乎在第 1 个会话的事务后已运行。

commit;

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

序列化失败。我们回滚并重试。

rollback;
begin;
update dots set color = 'blue'
  where color = 'yellow';
commit;

重试后，一切正常。

select color, count(*) from dots
  group by color
  order by color;

 color | count
-------+-------
 blue  |  6000
 red   |  3000
(2 rows)

这似乎已最后运行，确实如此。

有趣的一点是，如果在第 1 个会话之后而第 3 个会话之前，第 2 个会话尝试提交，则仍然会失败，并且重试依然会成功，但第 3 个会话的事务结果不确定。它可能已成功，或者获取序列化失败并需要重试。

这是因为作为冲突检测一部分使用的谓词锁取决于实际访问的页面和元组，并且在插入键相等的索引词条时使用随机因子，以最大程度减少竞争；因此，即使运行序列相同，也可能会看到序列化失败发生位置的不同之处。这就是为什么在依靠可串行化的事务来管理并发性时，必须采用一些概括的技术来识别序列化失败并从开头重试事务的原因。

同样值得注意的是，如果第 2 个会话在其事务提交之前提交了重试事务，则任何后续查询（其查看了已由第 2 个会话成功地从黄色更新为蓝色的行）将决定性地导致第 3 个会话的事务失败，因为第 3 个会话不会将这些行视为蓝色并更新为红色。为了成功提交第 3 个会话的事务，必须将其视为先于第 2 个会话的成功事务运行，因此即使展示了第 2 个会话事务的工作但未展示第 3 个会话事务的工作，也意味着第 3 个会话的事务必须失败。查看最新修改的数据库状态的动作可能导致序列化失败。这将在其他示例中进一步探讨。

在触发器中强制执行业务规则

如果所有事务都是可序列化的，那么无需考虑与其他事务隔离级别相关的问题，就可以在触发器中执行业务规则。如果某个声明约束条件有效，那么它通常速度更快、更容易实现和维护，并且不太容易出错，因此只有在声明约束条件不可行的情况下，才应以这种方式使用触发器。

类似于唯一约束

假设你想要类似于唯一约束的内容，但它稍微复杂一点。对于此示例，我们希望文本列的前六个字符唯一性。

可以使用以下语句设置该示例

create table t (id int not null, val text not null);
with x (n) as (select generate_series(1,10000))
  insert into t select x.n, md5(x.n::text) from x;
alter table t add primary key(id);
create index t_val on t (val);
vacuum analyze t;
create function t_func()
  returns trigger
  language plpgsql as $$
declare
  st text;
begin
  st := substring(new.val from 1 for 6);
  if tg_op = 'UPDATE' and substring(old.val from 1 for 6) = st then
    return new;
  end if;
  if exists (select * from t where val between st and st || 'z') then
    raise exception 't.val not unique on first six characters: "%"', st;
  end if;
  return new;
end;
$$;
create trigger t_trig
  before insert or update on t
  for each row execute procedure t_func();

要确认在单个连接上没有并发问题时触发器是否正在执行业务规则

insert into t values (-1, 'this old dog');
insert into t values (-2, 'this old cat');

ERROR:  t.val not unique on first six characters: "this o"

现在我们尝试两个并发会话。

类似于唯一约束的示例
会话 1 会话 2
begin;
insert into t values (-3, 'the river flows');

begin;
insert into t values (-4, 'the right stuff');

当其他事务的工作对当前事务不可见时，这暂时有效，但无论哪个事务都可能在不违反业务规则的情况下提交。

commit;

第一个提交方胜出。这个事务是安全的。

在此执行提交会导致失败，但尝试在此注定失败的事务中运行任何其他语句也会导致失败。

select * from t where id < 0;

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

由于这是一次序列化失败，应重试该事务。

rollback;
begin;
insert into t values (-3, 'the river flows');

在重试时，我们收到一条对用户更有帮助的错误消息。

ERROR:  t.val not unique on first six characters: "the ri"

类似于外键的约束

有时，两个表必须与外键关系非常类似，但存在额外的条件，导致外键不足以完全涵盖必要的完整性检查。在此示例中，project 表包含一个 project_manager 列中 person 表的主键的引用，但并非任何人都可以充当项目经理；指定的人员必须标记为项目经理。

可以使用以下语句设置该示例

create table person
  (
    person_id int not null primary key,
    person_name text not null,
    is_project_manager boolean not null
  );
create table project
  (
    project_id int not null primary key,
    project_name text not null,
    project_manager int not null
  );
create index project_manager
  on project (project_manager);

create function person_func()
  returns trigger
  language plpgsql as $$
begin
  if tg_op = 'DELETE' and old.is_project_manager then
    if exists (select * from project
               where project_manager = old.person_id) then
      raise exception
        'person cannot be deleted while manager of any project';
    end if;
  end if;
  if tg_op = 'UPDATE' then
    if new.person_id is distinct from old.person_id then
      raise exception 'change to person_id is not allowed';
    end if;
    if old.is_project_manager and not new.is_project_manager then
      if exists (select * from project
                 where project_manager = old.person_id) then
        raise exception
          'person must remain a project manager while managing any projects';
      end if;
    end if;
  end if;
  if tg_op = 'DELETE' then
    return old;
  else
    return new;
  end if;
end;
$$;
create trigger person_trig
  before update or delete on person
  for each row execute procedure person_func();

create function project_func()
  returns trigger
  language plpgsql as $$
begin
  if tg_op = 'INSERT'
  or (tg_op = 'UPDATE' and new.project_manager <> old.project_manager) then
    if not exists (select * from person
                     where person_id = new.project_manager
                       and is_project_manager) then
      raise exception
        'project_manager must be defined as a project manager in the person table';
    end if;
  end if;
  return new;
end;
$$;
create trigger project_trig
  before insert or update on project
  for each row execute procedure project_func();

insert into person values (1, 'Kevin Grittner', true);
insert into person values (2, 'Peter Parker', true);
insert into project values (101, 'parallel processing', 1);
类似于外键的约束示例
会话 1 会话 2

某个人员正在更新，不再成为项目经理。

begin;
update person
  set is_project_manager = false
  where person_id = 2;

与此同时，某个项目正在更新，将该人员指定为该项目的经理。

begin;
update project
  set project_manager = 2
  where project_id = 101;

这两个操作不能同时提交。第一个提交将胜出。

commit;

将人员分配到项目的操作会先提交，因此另一事务现在注定会失败。如果任何一个事务在不同的隔离级别中运行，则两个事务都可能已经提交，从而导致违反业务规则。

commit;

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

序列化失败。我们回滚并重试。

rollback;
begin;
update person
  set is_project_manager = false
  where person_id = 2;

ERROR:  person must remain a project manager
        while managing any projects

重试时，我们收到一条有意义的消息。

只读事务

虽然只读事务不会造成数据库中持续存在的异常，但在可重复读事务隔离中，它可以看到与任何事务的顺序（一次执行一个）执行不一致的状态。使用 SSI 实现的可序列化事务绝不会看到这些瞬态异常。

存款报告

需要使用只读事务处理的一般类型的问题是批处理，即一个表控制当前插入的目标批次。通过更新控制表关闭批次，此时批次被认为“已锁定”且无法进行进一步更改，并且将处理该批次。

在现实世界记账中，一个特定的示例是收据。可以将收据添加到由存款日期标识的批次或（如果一天有多次存款）一个抽象收据批次编号。在一天中的某个时间，当银行仍在营业时，批次被关闭，打印出收到的钱的报告，然后将钱存入银行。

可以使用以下语句设置该示例

create table control
  (
    deposit_no int not null
  );
insert into control values (1);
create table receipt
  (
    receipt_no serial primary key,
    deposit_no int not null,
    payee text not null,
    amount money not null
  );
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Crosby', '100');
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Stills', '200');
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Nash', '300');
存款报告示例
会话 1 会话 2

在收据柜台，另一种收据添加到当前批次。

begin;  -- T1
insert into receipt
  (deposit_no, payee, amount)
  values
  (
    (select deposit_no from control),
    'Young', '100'
  );

此事务可以查看其自己的插入，但直到提交，其他事务都看不到它。

 select * from receipt;

 receipt_no | deposit_no | payee  | amount  
------------+------------+--------+---------
          1 |          1 | Crosby | $100.00
          2 |          1 | Stills | $200.00
          3 |          1 | Nash   | $300.00
          4 |          1 | Young  | $100.00
(4 rows)

大约在同一时间，主管单击按钮以关闭收据批次。

begin;  -- T2
select deposit_no from control;

 deposit_no 
------------
          1
(1 row)

应用程序记录即将关闭的收据批次，增加批次号，并将其保存到控制表中。

update control set deposit_no = 2;
commit;

T1，插入旧批次最后收据的事务，即使批次已关闭，但尚未提交。如果 T1 在任何人查看已关闭批次的内容之前进行提交，则一切正常。到目前为止，我们还没有遇到问题；收据在批次关闭之前似乎已添加。我们具有符合事务一次一个执行的操作：T1 -> T2。

出于演示目的，我们将在最后一个收据提交之前启动存款报告。

begin;  -- T3
select * from receipt where deposit_no = 1;

 receipt_no | deposit_no | payee  | amount  
------------+------------+--------+---------
          1 |          1 | Crosby | $100.00
          2 |          1 | Stills | $200.00
          3 |          1 | Nash   | $300.00
(3 rows)

现在我们遇到了问题。T3 启动时知道 T2 已成功提交，因此 T3 被认为在 T2 之后运行。（如果 T3 独立运行并从控制表中选择，则看到更新后的 deposit_no，则也可能如此。）但 T3 看不到 T1 的工作，所以 T1 似乎在 T3 之后运行。因此，我们形成一个循环 T1 -> T2 -> T3 -> T1。从实际角度看，这将是一个问题；批次应该关闭并且不可更改，但稍后会弹出更改 -- 也许在去银行后。

在 REPEATABLE READ 隔离级别下，这将静默进行，而不会注意到异常情况。在 SERIALIZABLE 隔离级别下，将回滚一个事务以保护系统的完整性。由于如果 T1 仍然处于活动状态，则 T3 的回滚和重试也会遇到相同的错误，因此 PostgreSQL 将取消 T1，以便立即重试成功。

commit;

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

好的，我们重试。

rollback;
begin;  -- T1 retry
insert into receipt
  (deposit_no, payee, amount)
  values
  (
    (select deposit_no from control),
    'Young', '100'
  );

现在收据表是什么样子的？

select * from receipt;

 receipt_no | deposit_no | payee  | amount  
------------+------------+--------+---------
          1 |          1 | Crosby | $100.00
          2 |          1 | Stills | $200.00
          3 |          1 | Nash   | $300.00
          5 |          2 | Young  | $100.00
(4 rows)

收据现在落入下一个批次，从而使 T3 中的存款报告正确无误！

commit;

现在没有问题了。

commit;

在 T3 的 SELECT 之后任何时候这都会很好。

翻转

虽然只读事务本身很少被回滚以防止序列化失败，但如果其他事务已经提交，它就会发生。

可以使用以下语句设置该示例

create table rollover (id int primary key, n int not null);
insert into rollover values (1,100), (2,10);
回滚示例
会话 1 会话 2

某个事务查看第 2 行并更新第 1 行。

begin;  -- T1
update rollover
  set n = n + (select n from rollover where id = 2)
  where id = 1;

几乎同时，另一个连接提交了一个对第 2 行的更改。

begin;  -- T2
update rollover set n = n + 1 where id = 2;
commit;

到目前为止还没有问题；T1 看起来已经先执行了，因为它看到的是 T2 提交更改之前的第 2 行。

现在另一个事务启动，它需要获取一个快照。

begin transaction read only;  -- T3
select count(*) from pg_class;

T3 在一个快照中运行，该快照可以看到 T2 的工作，而看不到 T1 的工作，尽管我们已经确定 T1 看起来比 T2 先执行。不过只要 T3 不查看 T1 修改后的数据，就不会有问题。

T1 可以无错误地提交。

commit;

现在，如果只读事务尝试读取 T1 修改的数据，则执行的明显顺序中会出现一个循环，因此必须回滚该尝试。

select n from rollover where id in (1,2);

ERROR:  could not serialize access
        due to read/write dependencies
        among transactions
DETAIL:  Reason code: Canceled on conflict out
         to pivot 1117, during read.
HINT:  The transaction might succeed if retried.

我们重新尝试该事务。

rollback;
begin transaction read only;  -- T3 retry
select count(*) from pg_class;
select n from rollover where id in (1,2);
commit;

  n  
-----
 110
  11
(2 rows)

现在，我们看到一个数据视图，它与 T1 在 T2 之前运行一致。

取自 "https://wiki.postgresql.ac.cn/index.php?title=SSI&oldid=34576"