Transactions and Locking
Atomic units, isolation, and explicit locks. Results appear in the same fence: same-line -- comments when short, multiline -- blocks below the sample when not.
//
Busca en todas las páginas de la documentación
Atomic units, isolation, and explicit locks. Results appear in the same fence: same-line -- comments when short, multiline -- blocks below the sample when not.
Atomic multi-statement units.
BEGIN;
UPDATE accounts SET bal = bal - 10 WHERE id = 1;
UPDATE accounts SET bal = bal + 10 WHERE id = 2;
COMMIT;
-- both or neitherREAD COMMITTED default; SERIALIZABLE strictest.
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... queries ...
COMMIT;Lock rows to update without lost races.
BEGIN;
SELECT * FROM jobs WHERE id = 1 FOR UPDATE;
UPDATE jobs SET locked_by = 'w1' WHERE id = 1;
COMMIT;App-level locks by bigint key.
SELECT pg_advisory_lock(42);
-- critical section
SELECT pg_advisory_unlock(42);
-- t when unlockedPartial rollback inside a transaction.
BEGIN;
SAVEPOINT sp1;
-- risky dml
ROLLBACK TO sp1;
COMMIT;Abort the current transaction.
BEGIN;
DELETE FROM t;
ROLLBACK;
-- t unchangedWorkers claim rows without waiting.
SELECT id FROM jobs
WHERE status = 'ready'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- next free jobError instead of waiting on lock.
SELECT * FROM t WHERE id = 1 FOR UPDATE NOWAIT;
-- errors if lockedCoarse table lock modes.
LOCK TABLE t IN SHARE ROW EXCLUSIVE MODE;
-- blocks conflicting DDL/DML per modeInspect transaction id (debug).
SELECT txid_current();
-- e.g. 12345Avoid long open transactions - they hold snapshots/locks.
-- commit/rollback promptly; set idle_in_transaction_session_timeoutSafety for reporting sessions.
BEGIN READ ONLY;
SELECT count(*) FROM events;
COMMIT;Check DEFERRABLE constraints at commit.
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- multi-row fixes
COMMIT; -- checks run herePREPARE TRANSACTION for external coordinators - rare in apps.
-- PREPARE TRANSACTION 'xid';
-- COMMIT PREPARED 'xid';On 40P01, retry the transaction from the start.
-- SQLSTATE 40P01: deadlock_detected
-- app: rollback and retry with backoffStack versions: PostgreSQL 18.4 (stable 18, maintenance 17) · pgvector 0.8+
Revisado por Chris St. John·Última actualización: 19 jul 2026