ERROR: could not serialize access due to concurrent update:PostgreSQL 三个隔离级别、写偏斜、40001 重试和丢失更新实测

    A: begin  →  BEGIN
    A: select bal from acct where id = 1  →  [(100,)]
    B: update acct set bal = 200 where id = 1  →  UPDATE 1
    A: select bal from acct where id = 1(同一个事务,B 已提交)  →  [(200,)]
    A: commit  →  COMMIT
  UPDATE 遇到别人刚改过的行会重新检查 WHERE:A、B 同时执行 update acct set bal = bal + 1 where bal = 200,B 先改并提交,A 等到 B 提交后重新评估
    B: begin; update acct set bal = 300 where id = 1  →  BEGIN
    A:update acct set bal = bal + 1 where bal = 200(被 B 的行锁挡住,正在等)
    B: commit  →  COMMIT
    A: (解除阻塞之后的结果)  →  UPDATE 0
    最终 bal = 300 (A 的 WHERE bal = 200 在 B 提交后重新评估,发现 bal 已经是 300,不再匹配,所以一行都没改)

    A: begin isolation level repeatable read  →  BEGIN
    A: select bal from acct  →  [(100,)]
    B: update acct set bal = 150 where id = 1; insert into acct values (2, 5)  →  UPDATE 1
    A: select bal from acct(同一个快照,看不到 B 的修改)  →  [(100,)]
    A: select count(*) from acct(看不到 B 新插入的行,没有幻读)  →  [(1,)]
    A: update acct set bal = bal + 1 where id = 1  →  SerializationFailure(SQLSTATE 40001): could not serialize access due to concurrent update
    A: commit(事务已经处于中止状态)  →  ROLLBACK
  报错之后这个事务不能继续,只能 rollback 重来:select bal from acct 再试一次 → [(150,), (5,)]

    repeatable read:X 看到值班 2 人,Y 看到值班 2 人,于是 X 让 alice 请假、Y 让 bob 请假
    X commit → COMMIT;Y commit → COMMIT
    最终值班人数 = 0
    serializable:X 看到值班 2 人,Y 看到值班 2 人,于是 X 让 alice 请假、Y 让 bob 请假
    X commit → COMMIT;Y commit → SerializationFailure(SQLSTATE 40001): 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.
    最终值班人数 = 1

  serializable:8 个会话各做 50 次「读出 n、加 1 写回」,每次失败就重试:最终 n = 400,共重试 1180 次,耗时 0.54 s
  repeatable read:8 个会话各做 50 次「读出 n、加 1 写回」,每次失败就重试:最终 n = 400,共重试 1301 次,耗时 0.57 s

  read-modify-write :最终 n = 89(应为 400),丢了 311 次更新,耗时 0.08 s
  atomic            :最终 n = 400(应为 400),丢了 0 次更新,耗时 0.04 s
  for update        :最终 n = 400(应为 400),丢了 0 次更新,耗时 0.17 s