处理竞争条件：一个实际示例

create table shifts (
    id serial primary key,
    doctor_name text not null,
    shift_id integer not null,
    on_call boolean not null default false
);

john --begin------doctors on call: 2-------leave on call-----commit--------> (t)
          \                 \                      \             \
           \                 \                      \             \ 
database ------------------------------------------------------------------> (t)
               /               /                      /              /
              /               /                      /              /
jack ------begin------doctors on call: 2-----leave on call----commit-------> (t)

     ✓ at least one doctor on call for shiftid=1
     ✓ at least one doctor on call for shiftid=2
     ✗ at least one doctor on call for shiftid=3
      ↳  36% — ✓ 123 / ✗ 217

    // init transaction with serializable isolation level
    tx, err := db.begintxx(c.request().context(),    &sql.txoptions{
        isolation: sql.levelserializable,
    })

create or replace function update_on_call_status_with_serializable_isolation(shift_id_to_update int, doctor_name_to_update text, on_call_to_update boolean)
returns void as $$
declare
    on_call_count int;
begin
    -- check the current number of doctors on call for this shift
    select count(*) into on_call_count from shifts s where s.shift_id = shift_id_to_update and s.on_call = true;

    if on_call_to_update = false and on_call_count = 1 then
        raise exception '[serializableisolation] cannot set on_call to false. at least one doctor must be on call for this shiftid: %', shift_id_to_update;
    else
        update shifts s
        set on_call = on_call_to_update
        where s.shift_id = shift_id_to_update and s.doctor_name = doctor_name_to_update;
    end if;

end;
$$ language plpgsql;

error:  could not serialize access due to read/write dependencies among transactions

begin;

-- attempt to acquire advisory lock and handle failure with exception
    if not pg_try_advisory_xact_lock(shift_id_to_update) then
        raise exception '[advisorylock] could not acquire advisory lock for shift_id: %', shift_id_to_update;
    end if;

-- perform necessary operations

-- commit will automatically release the lock
commit;

-- Function to Manage On Call Status with Advisory Locks, automatic release when the trx commits
CREATE OR REPLACE FUNCTION update_on_call_status_with_advisory_lock(shift_id_to_update INT, doctor_name_to_update TEXT, on_call_to_update BOOLEAN)
RETURNS VOID AS $$
DECLARE
    on_call_count INT;
BEGIN
    -- Attempt to acquire advisory lock and handle failure with NOTICE
    IF NOT pg_try_advisory_xact_lock(shift_id_to_update) THEN
        RAISE EXCEPTION '[AdvisoryLock] Could not acquire advisory lock for shift_id: %', shift_id_to_update;
    END IF;

    -- Check the current number of doctors on call for this shift
    SELECT COUNT(*) INTO on_call_count FROM shifts s WHERE s.shift_id = shift_id_to_update AND s.on_call = TRUE;

    IF on_call_to_update = FALSE AND on_call_count = 1 THEN
        RAISE EXCEPTION '[AdvisoryLock] Cannot set on_call to FALSE. At least one doctor must be on call for this shiftId: %', shift_id_to_update;
    ELSE
        UPDATE shifts s
        SET on_call = on_call_to_update
        WHERE s.shift_id = shift_id_to_update AND s.doctor_name = doctor_name_to_update;
    END IF;
END;
$$ LANGUAGE plpgsql;