25 大家都得排队

select lab.run('tx1', 'update credits set balance = 70 where account_id = 42') \g /dev/null
select dblink_send_query('tx2', 'update credits set balance = 80 where account_id = 42') \g /dev/null
select pg_sleep(0.3) \g /dev/null

select lab.prove(
  'tx2''s update waits for tx1''s row lock',
  (select wait_event_type = 'Lock' and wait_event = 'transactionid'
          and pg_blocking_pids(pid) = (select array_agg(pid) from lab.activity
                                       where application_name = 'tx1')
   from lab.activity where application_name = 'tx2'));

select lab.run('tx1', 'commit') \g /dev/null
select lab.finish('tx2') as tx2_result \gset
select lab.run('tx2', 'commit') \g /dev/null

select lab.prove(
  'under read committed tx2 proceeds without error and overwrites: balance 80, a lost update',
  :'tx2_result' = 'ok'
  and (select balance from credits where account_id = 42) = 80);

select lab.prove(
  'under repeatable read tx2 fails with 40001 could not serialize access due to concurrent update, and tx1''s 70 stands',
  :'tx2_result' = '40001: could not serialize access due to concurrent update'
  and (select balance from credits where account_id = 42) = 70);

select lab.prove(
  'serializable records what each transaction read as SIReadLock entries in pg_locks',
  exists (select from pg_locks l join lab.activity a using (pid)
          where a.application_name = 'tx1' and l.mode = 'SIReadLock'));

select lab.prove(
  'one transaction fails with 40001 could not serialize access due to read/write dependencies, and someone stays on call',
  :'outcome' like '%40001: could not serialize access due to read/write dependencies among transactions%'
  and (select count(*) from on_call where active) = 1);

begin;
select format('savepoint s%s', g), format('insert into subxact_demo values (%s)', g)
from generate_series(1, 64) g \gexec

select lab.prove(
  'after 64 savepoints, each followed by an insert: 64 cached, not overflowed',
  (select (subxact_count, subxact_overflowed) = (64, false) from lab.my_subxact()));

savepoint s65;
insert into subxact_demo values (65);

select lab.prove(
  'one more: the count stops at 64 and the overflow flag flips',
  (select (subxact_count, subxact_overflowed) = (64, true) from lab.my_subxact()));
rollback;

-- lab.locks_taken runs a statement, reads this session's table locks from
-- pg_locks, and rolls back
select lab.prove('add column takes AccessExclusiveLock',
  lab.locks_taken('alter table projects add column note text', '{projects}') = '{"projects AccessExclusiveLock"}');
select lab.prove('validate constraint takes ShareUpdateExclusiveLock',
  lab.locks_taken('alter table projects validate constraint name_not_empty', '{projects}')
    = '{"projects ShareUpdateExclusiveLock"}');
select lab.prove('create index takes ShareLock',
  lab.locks_taken('create index on projects (created_at)', '{projects}') = '{"projects ShareLock"}');

select lab.prove(
  'the migration waits for AccessExclusiveLock, blocked by reporting',
  exists (select from pg_locks where pid = :migration_pid and relation = 'accounts'::regclass
          and mode = 'AccessExclusiveLock' and not granted)
  and pg_blocking_pids(:migration_pid) = array[:reporting_pid]);

set lock_timeout = '2s';
select lab.prove(
  'with lock_timeout = 2s the migration gives up: canceling statement due to lock timeout',
  lab.try('alter table accounts add column note text')
    = '55P03: canceling statement due to lock timeout');

set lock_timeout = '1s';
select lab.prove(
  'an explicit FOR NO KEY UPDATE does not block it either',
  lab.try($$insert into events (account_id, project_id, user_id, kind) values (9, 1, 8, 'comment')$$)
    = 'ok');

-- now tx1 holds select ... for update on project 1, and tx2 sends the same insert
select lab.prove(
  'FOR UPDATE on the project blocks the insert: an ungranted ShareLock on the locker''s transactionid',
  exists (select from pg_locks l join lab.activity a using (pid)
          where a.application_name = 'tx2' and l.locktype = 'transactionid'
            and l.mode = 'ShareLock' and not l.granted)
  and (select pg_blocking_pids(pid) from lab.activity where application_name = 'tx2')
      = (select array_agg(pid) from lab.activity where application_name = 'tx1'));

select lab.prove(
  'two open child inserts make the parent row''s xmax a multixact of two FOR KEY SHARE members',
  :'mx_multi' = 't'
  and (select count(*) = 2 and bool_and(mode = 'keysh')
       from pg_get_multixact_members(:'mx_two'::xid)));

select lab.prove(
  'a third locker creates a new multixact holding all three, rather than changing the old one',
  :'mx_three' <> :'mx_two'
  and (select count(*) = 3 and bool_and(mode = 'keysh')
       from pg_get_multixact_members(:'mx_three'::xid)));

-- one transaction locks 100 small tables in AccessShare mode
select lab.prove(
  'PostgreSQL 18 has more than 16 fast-path slots, at most 64 at the default max_locks_per_transaction; the rest go to the shared lock table',
  :fp_fast > 16 and :fp_fast <= 64 and :fp_fast + :fp_slow = 100);

select lab.prove(
  'worker-b gets the next job immediately, not worker-a''s',
  :b_job = :a_job + 1);

select lab.prove(
  'without skip locked, worker-c queues behind worker-a''s lock until its lock_timeout fires',
  lab.run('worker-c', 'set lock_timeout = ''1s''') = 'ok'
  and lab.run('worker-c', $$select id from jobs
                            where queue = 'default' and status = 'queued' and run_at <= now()
                            order by run_at, id limit 1
                            for update$$)
      = '55P03: canceling statement due to lock timeout');

select lab.prove(
  'the first cron instance gets the lock, the second does not',
  (select got from dblink('cron-a', $$select pg_try_advisory_lock(hashtext('nightly-rollup'))$$) as t(got boolean))
  and not (select got from dblink('cron-b', $$select pg_try_advisory_lock(hashtext('nightly-rollup'))$$) as t(got boolean)));

select lab.prove(
  'a transaction-scoped advisory lock is released at commit',
  lab.run('cron-a', 'begin') = 'ok'
  and (select true from dblink('cron-a', 'select pg_advisory_xact_lock(1, 42)') as t(v text))
  and not (select got from dblink('cron-b', 'select pg_try_advisory_xact_lock(1, 42)') as t(got boolean))
  and lab.run('cron-a', 'commit') = 'ok'
  and (select got from dblink('cron-b', 'select pg_try_advisory_xact_lock(1, 42)') as t(got boolean)));

select lab.prove(
  'exactly one of the two fails with 40P01 deadlock detected; the other gets its lock',
  :'outcome' in ('40P01: deadlock detected / ok', 'ok / 40P01: deadlock detected'));

select lab.prove(
  'pg_stat_database counts the deadlock',
  (select deadlocks from pg_stat_database where datname = current_database()) > :deadlocks_before);