JPA 乐观锁与悲观锁

      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=?
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=?
  两个 EntityManager 都加载 alice:version = 0 / 0,balance = 100
      SQL> update Acc set balance=?,owner=?,version=? where id=? and version=?
  EM1 加 50 并提交成功:version 现在是 1(每次成功更新 +1)
      SQL> update Acc set balance=?,owner=?,version=? where id=? and version=?
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=?
  EM2 加 30 并提交:RollbackException ← OptimisticLockException ← StaleObjectStateException:Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [Acc#1]
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=?
  最终库里:balance = 150,version = 1(EM2 的修改被拒绝,没有覆盖 EM1 的)
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=?
  没有真正修改任何字段再提交:version 仍是 1(没有变化就不发 UPDATE,版本不增加)

  EM1 以 OPTIMISTIC 读了 alice(没有修改),EM2 随后修改并提交了 alice
  EM1 提交:RollbackException ← OptimisticLockException ← OptimisticEntityLockException:Newer version [1] of entity [[Acc#1]] found in database
  对照:EM1 用默认锁模式读 alice(没有修改),EM2 修改并提交
  EM1 提交成功(没有任何检查:读到的数据已经过期但不知道)
  OPTIMISTIC_FORCE_INCREMENT 读 bob 但不修改:提交后 version 0 → 1(提交时强制把版本号 +1,用来保护聚合根)

      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=? for no key update
  EM1 以 PESSIMISTIC_WRITE 读 alice(持有行锁)
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=? for no key update
  线程 2 的 PESSIMISTIC_WRITE 已经等了约 800 ms,线程状态 RUNNABLE,仍未拿到
  EM1 提交释放锁之后,线程 2 拿到了锁:从申请到拿到共等了 807 ms
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=? for no key update
  EM1 再次持有 alice 的行锁:
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=? for no key update nowait
  线程 2 用 NOWAIT(jakarta.persistence.lock.timeout = 0):LockTimeoutException ← PessimisticLockException ← PSQLException:ERROR: could not obtain lock on row in relation "acc",用时 6 ms
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=? for no key update skip locked
  线程 2 用 SKIP LOCKED(timeout = -2):find 返回 null,用时 0 ms
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 order by a1_0.id for no key update skip locked
  SKIP LOCKED 的查询(select a from Acc a order by id,锁 alice 的事务还没提交):返回 1 行(跳过了被锁的 alice,返回 bob)
      SQL> select a1_0.id,a1_0.balance,a1_0.owner,a1_0.version from Acc a1_0 where a1_0.id=? for no key update
  线程 2 用 jakarta.persistence.lock.timeout = 1000 ms:2.5 秒之后线程状态 RUNNABLE(没有在 1 秒时超时,PostgreSQL 方言不支持大于 0 的锁超时,超时设置被忽略);等待结束的耗时 尚未结束
  EM1 回滚之后线程 2 拿到了锁,整个等待 2508 ms

  线程 1(先锁 alice 再锁 bob):OptimisticLockException ← LockAcquisitionException ← PSQLException:ERROR: deadlock detected Detail: Process 43408 waits for ShareLock on transaction 749; blocked by process 43407. Process 43407 waits for ShareLock on
  线程 2(先锁 bob 再锁 alice):提交成功
  从两个事务各自锁好第一行到有一方失败共用了约 1015 ms(PostgreSQL 的 deadlock_timeout 默认 1 秒)

  无锁(没有 @Version)              期望 1600,最终计数 253(丢失了 1347 次),重试 / 异常 0 次,耗时 0.22 s,约 7201 次提交/秒
  乐观锁(@Version)+ 冲突后重试         期望 1600,最终计数 1600(正确),重试 / 异常 6216 次,耗时 0.72 s,约 2229 次提交/秒
  悲观锁(PESSIMISTIC_WRITE)       期望 1600,最终计数 1600(正确),重试 / 异常 0 次,耗时 0.19 s,约 8460 次提交/秒