تراکنش، ACID، سطوح ایزوله و MVCCTransactions, ACID, Isolation & MVCC

-- PostgreSQL: outside a block, every statement is its own implicit transaction
BEGIN;                                  -- or START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                                 -- or ROLLBACK;

-- Oracle: there is no explicit BEGIN; the first DML opens the transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                                 -- or ROLLBACK;
-- SET TRANSACTION only sets properties: SET TRANSACTION NAME 'transfer';

-- PostgreSQL: DDL is transactional! A whole migration can be rolled back
BEGIN;
  CREATE TABLE audit_log (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                          msg text NOT NULL);
  ALTER TABLE accounts ADD COLUMN note text;
ROLLBACK;   -- both vanish; the table was never created

-- Oracle: every DDL carries an implicit COMMIT *before* and *after* itself
INSERT INTO audit_log(msg) VALUES ('migration v42');   -- transaction opened
CREATE TABLE tmp_x (id NUMBER PRIMARY KEY);            -- <-- the INSERT is COMMITTED here!
ROLLBACK;                                              -- nothing left to undo
-- Compensation must be manual:  DROP TABLE tmp_x PURGE;

BEGIN;
  INSERT INTO orders(id, status) VALUES (1, 'NEW');
  SAVEPOINT before_risky;
  INSERT INTO order_items(order_id, sku) VALUES (1, 'NOPE');  -- FK violation
  ROLLBACK TO SAVEPOINT before_risky;    -- without this the whole tx is dead
  INSERT INTO order_items(order_id, sku) VALUES (1, 'SKU-1');
COMMIT;

SAVEPOINT before_risky;
INSERT INTO order_items(order_id, sku) VALUES (1, 'NOPE');  -- ORA-02291
-- In Oracle only that statement was rolled back; the transaction is still healthy
ROLLBACK TO before_risky;               -- the keyword SAVEPOINT is optional
INSERT INTO order_items(order_id, sku) VALUES (1, 'SKU-1');
COMMIT;

-- Deferred constraint: not checked until COMMIT
ALTER TABLE order_items ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders(id) DEFERRABLE INITIALLY DEFERRED;
BEGIN;
  INSERT INTO order_items(order_id, sku) VALUES (999, 'SKU-1');  -- no error yet
  INSERT INTO orders(id, status) VALUES (999, 'NEW');
COMMIT;   -- the check happens here

ALTER TABLE order_items ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders(id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO order_items(order_id, sku) VALUES (999, 'SKU-1');    -- no error yet
INSERT INTO orders(id, status) VALUES (999, 'NEW');
COMMIT;   -- the check happens here
-- Per-transaction override: SET CONSTRAINTS ALL DEFERRED;

-- Trading durability for speed on non-critical writes
SET synchronous_commit = off;
INSERT INTO analytics_events(payload) VALUES ('{"e":"click"}');
COMMIT;
SET synchronous_commit = on;

INSERT INTO analytics_events(payload) VALUES ('{"e":"click"}');
COMMIT WRITE BATCH NOWAIT;     -- do not wait for the LGWR flush
-- Or session-wide: ALTER SESSION SET commit_wait = NOWAIT;

CREATE TABLE accounts (
  id      integer PRIMARY KEY,
  owner   text          NOT NULL,
  balance numeric(18,2) NOT NULL CHECK (balance >= 0),
  version bigint        NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO accounts(id, owner, balance) VALUES (1,'ali',100), (2,'sara',100);

CREATE TABLE accounts (
  id      NUMBER(10)    PRIMARY KEY,
  owner   VARCHAR2(100) NOT NULL,
  balance NUMBER(18,2)  NOT NULL CHECK (balance >= 0),
  version NUMBER(19)    DEFAULT 0 NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);
INSERT INTO accounts(id, owner, balance) VALUES (1,'ali',100);
INSERT INTO accounts(id, owner, balance) VALUES (2,'sara',100);
COMMIT;

-- Session A (READ COMMITTED = default)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 100
-- Session B: UPDATE accounts SET balance=120 WHERE id=1; COMMIT;
SELECT balance FROM accounts WHERE id = 1;   -- 120  <-- non-repeatable
COMMIT;

-- Session A (READ COMMITTED is Oracle's default too)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- 100
-- Session B: UPDATE accounts SET balance=120 WHERE id=1; COMMIT;
SELECT balance FROM accounts WHERE id = 1;   -- 120  <-- non-repeatable
COMMIT;

-- A:                                     B:
BEGIN;                                 -- BEGIN;
SELECT balance FROM accounts            -- SELECT balance FROM accounts
  WHERE id=1;             -- 100        --   WHERE id=1;             -- 100
UPDATE accounts SET balance=110         -- UPDATE accounts SET balance=110
  WHERE id=1;                           --   WHERE id=1;   (waits for A)
COMMIT;                                -- COMMIT;   -- result 110, not 120

-- A:                                     B:
SELECT balance FROM accounts            -- SELECT balance FROM accounts
  WHERE id=1;             -- 100        --   WHERE id=1;             -- 100
UPDATE accounts SET balance=110         -- UPDATE accounts SET balance=110
  WHERE id=1;                           --   WHERE id=1;   (waits for A)
COMMIT;                                -- COMMIT;   -- result 110, not 120

CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

-- A                                        B
BEGIN ISOLATION LEVEL REPEATABLE READ;   -- BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors             -- SELECT count(*) FROM doctors
  WHERE on_call;           -- 2          --   WHERE on_call;           -- 2
UPDATE doctors SET on_call=false         -- UPDATE doctors SET on_call=false
  WHERE name='alice';                    --   WHERE name='bob';
COMMIT;                                  -- COMMIT;   => zero doctors on call!
-- Under ISOLATION LEVEL SERIALIZABLE one of them gets 40001 and you are saved.

CREATE TABLE doctors (name VARCHAR2(50) PRIMARY KEY,
                      on_call NUMBER(1) NOT NULL CHECK (on_call IN (0,1)));
INSERT INTO doctors VALUES ('alice', 1);
INSERT INTO doctors VALUES ('bob', 1);
COMMIT;

-- A                                        B
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- same in B
SELECT count(*) FROM doctors             -- SELECT count(*) FROM doctors
  WHERE on_call=1;         -- 2          --   WHERE on_call=1;         -- 2
UPDATE doctors SET on_call=0             -- UPDATE doctors SET on_call=0
  WHERE name='alice';                    --   WHERE name='bob';
COMMIT;                                  -- COMMIT;   => zero doctors on call!
-- Oracle SERIALIZABLE does *not* prevent it: different rows, so no ORA-08177.

BEGIN;
SELECT count(*) FROM doctors WHERE on_call FOR UPDATE;   -- rows are locked
UPDATE doctors SET on_call = false
 WHERE name = 'alice'
   AND (SELECT count(*) FROM doctors WHERE on_call) > 1;
COMMIT;

SELECT count(*) FROM doctors WHERE on_call = 1 FOR UPDATE;
UPDATE doctors SET on_call = 0
 WHERE name = 'alice'
   AND (SELECT count(*) FROM doctors WHERE on_call = 1) > 1;
COMMIT;

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
COMMIT;
BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE;   -- compact form
COMMIT;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW transaction_isolation;

-- Must be the first statement of the transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
COMMIT;
-- These three are the only options that exist:
--   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   (default)
--   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--   SET TRANSACTION READ ONLY;
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;   -- session default

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
  SELECT sum(balance) FROM accounts;
  SELECT sum(amount)  FROM transactions;    -- both from one snapshot
COMMIT;
-- For a heavy report that must never get 40001:
-- BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

SET TRANSACTION READ ONLY;
  SELECT SUM(balance) FROM accounts;
  SELECT SUM(amount)  FROM transactions;    -- both from one SCN
COMMIT;   -- a read-only transaction still needs COMMIT/ROLLBACK to end

SELECT ctid, xmin, xmax, id, balance FROM accounts WHERE id = 1;
SELECT pg_current_xact_id();

-- Oracle does not keep versions in the table, so there is no xmin/xmax.
-- The closest equivalents: ROWID (physical address) and ORA_ROWSCN (last change SCN)
SELECT ROWID, ORA_ROWSCN, id, balance FROM accounts WHERE id = 1;
SELECT current_scn FROM v$database;

-- PostgreSQL has no built-in way to read "the data as of ten minutes ago".
-- You must build a history table yourself and fill it with triggers:
SELECT * FROM accounts_history
WHERE  id = 1
  AND  now() - interval '10 minutes' BETWEEN valid_from AND valid_to;

-- Oracle: Flashback Query — the same undo, now used for time travel
SELECT id, balance FROM accounts
  AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) WHERE id = 1;

SELECT versions_startscn, versions_operation, balance
FROM   accounts VERSIONS BETWEEN TIMESTAMP
         SYSTIMESTAMP - INTERVAL '30' MINUTE AND SYSTIMESTAMP
WHERE  id = 1;

BEGIN;
LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE;
COMMIT;
-- Inspect waits
SELECT pid, pg_blocking_pids(pid) AS blockers, wait_event, left(query,60)
FROM   pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE;   -- optional: ... WAIT 5 / NOWAIT
COMMIT;
-- Inspect waits
SELECT s.sid, s.username, s.blocking_session, s.event, s.seconds_in_wait
FROM   v$session s WHERE s.blocking_session IS NOT NULL;

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;   -- exclusive row lock
-- ... compute in the app ...
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;   -- no BEGIN needed
-- ... compute in the app ...
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;
-- Oracle can state which columns you intend to change:
-- SELECT balance FROM accounts WHERE id = 42 FOR UPDATE OF balance;

SET LOCAL lock_timeout = '3s';
SELECT balance FROM accounts WHERE id=42 FOR UPDATE;             -- after 3s: 55P03
SELECT balance FROM accounts WHERE id=42 FOR UPDATE NOWAIT;      -- immediately: 55P03
SELECT balance FROM accounts WHERE id=42 FOR UPDATE SKIP LOCKED;
-- Lock strengths: FOR UPDATE > FOR NO KEY UPDATE > FOR SHARE > FOR KEY SHARE

SELECT balance FROM accounts WHERE id=42 FOR UPDATE WAIT 3;      -- after 3s: ORA-30006
SELECT balance FROM accounts WHERE id=42 FOR UPDATE NOWAIT;      -- immediately: ORA-00054
SELECT balance FROM accounts WHERE id=42 FOR UPDATE SKIP LOCKED;
-- Oracle has no FOR SHARE / FOR KEY SHARE gradation; only FOR UPDATE [OF cols]

-- The single-statement pattern, extremely common in Postgres
UPDATE jobs SET status = 'RUNNING'
WHERE  id = (SELECT id FROM jobs WHERE status = 'READY'
             ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING id, payload;

-- Oracle: combining FETCH FIRST with FOR UPDATE is illegal (ORA-02014)!
DECLARE
  CURSOR c_jobs IS SELECT id, payload FROM jobs
                   WHERE status = 'READY' ORDER BY created_at
                   FOR UPDATE SKIP LOCKED;
  v_id jobs.id%TYPE;  v_payload jobs.payload%TYPE;
BEGIN
  OPEN c_jobs;  FETCH c_jobs INTO v_id, v_payload;
  IF c_jobs%FOUND THEN
    UPDATE jobs SET status = 'RUNNING' WHERE CURRENT OF c_jobs;
  END IF;
  CLOSE c_jobs;  COMMIT;
END;
/

-- The best Postgres approach: a conditional atomic update, no prior read
UPDATE inventory SET qty = qty - :d
WHERE  sku = :sku AND qty >= :d
RETURNING qty;      -- zero rows means there wasn't enough stock
-- For a very hot row: split the stock into N buckets and pick one at random.
-- PostgreSQL has no equivalent of Oracle's RESERVABLE.

-- Oracle 23ai: a RESERVABLE column — the lock is taken only at COMMIT
CREATE TABLE inventory (
  sku VARCHAR2(50) PRIMARY KEY,
  qty NUMBER RESERVABLE DEFAULT 0 NOT NULL,
  CONSTRAINT qty_non_negative CHECK (qty >= 0)
);
UPDATE inventory SET qty = qty - 1 WHERE sku = 'HOT-ITEM';
COMMIT;   -- the CHECK is validated and the delta applied here
-- 19c has nothing like this; write the same atomic pattern as Postgres:
-- UPDATE inventory SET qty = qty - :d WHERE sku = :sku AND qty >= :d;

-- A                                       B
BEGIN;                                  -- BEGIN;
UPDATE accounts SET balance=balance-10  -- UPDATE accounts SET balance=balance-10
  WHERE id=1;                           --   WHERE id=2;
UPDATE accounts SET balance=balance+10  -- UPDATE accounts SET balance=balance+10
  WHERE id=2;   -- waits for B           --   WHERE id=1;   -- waits for A => cycle
-- After deadlock_timeout (default 1s) one transaction is aborted *entirely*:
--   ERROR: deadlock detected (SQLSTATE 40P01)

-- A                                       B
UPDATE accounts SET balance=balance-10  -- UPDATE accounts SET balance=balance-10
  WHERE id=1;                           --   WHERE id=2;
UPDATE accounts SET balance=balance+10  -- UPDATE accounts SET balance=balance+10
  WHERE id=2;   -- waits for B           --   WHERE id=1;   -- waits for A => cycle
-- Oracle detects it in about 3 seconds and rolls back *only that statement*:
--   ORA-00060: deadlock detected while waiting for resource
-- The transaction is still alive and still holds its locks! You must ROLLBACK.

CREATE OR REPLACE FUNCTION transfer(p_from int, p_to int, p_amt numeric)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE lo int := least(p_from, p_to); hi int := greatest(p_from, p_to);
BEGIN
  PERFORM 1 FROM accounts WHERE id = lo FOR UPDATE;
  PERFORM 1 FROM accounts WHERE id = hi FOR UPDATE;
  UPDATE accounts SET balance = balance - p_amt WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amt WHERE id = p_to;
END $$;

CREATE OR REPLACE PROCEDURE transfer(p_from NUMBER, p_to NUMBER, p_amt NUMBER) IS
  v_lo NUMBER := LEAST(p_from, p_to);
  v_hi NUMBER := GREATEST(p_from, p_to);
  v_x  NUMBER;
BEGIN
  SELECT 1 INTO v_x FROM accounts WHERE id = v_lo FOR UPDATE;
  SELECT 1 INTO v_x FROM accounts WHERE id = v_hi FOR UPDATE;
  UPDATE accounts SET balance = balance - p_amt WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amt WHERE id = p_to;
END;
/

BEGIN;
  SELECT pg_advisory_xact_lock(hashtext('monthly-report'));   -- released at COMMIT
  -- ... only one session runs this block at a time ...
COMMIT;
SELECT pg_try_advisory_xact_lock(hashtext('monthly-report')); -- non-blocking variant

DECLARE
  v_handle VARCHAR2(128);  v_result INTEGER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('monthly-report', v_handle);
  v_result := DBMS_LOCK.REQUEST(lockhandle => v_handle,
                                lockmode   => DBMS_LOCK.X_MODE,
                                timeout    => 10,
                                release_on_commit => TRUE);
  -- 0=success 1=timeout 2=deadlock 3=param 4=already owned 5=illegal handle
  IF v_result <> 0 THEN RAISE_APPLICATION_ERROR(-20001, 'lock failed'); END IF;
  COMMIT;   -- the lock is released here
END;
/

UPDATE accounts
SET    balance = :new_balance, version = version + 1, updated_at = now()
WHERE  id = 42 AND version = :read_version
RETURNING version;      -- zero rows returned means you lost: reload and retry

UPDATE accounts
SET    balance = :new_balance, version = version + 1, updated_at = SYSTIMESTAMP
WHERE  id = 42 AND version = :read_version
RETURNING version INTO :out_version;   -- RETURNING needs a bind / PL-SQL
-- SQL%ROWCOUNT = 0 means you lost

-- PostgreSQL has no ORA_ROWSCN. Do NOT substitute xmin —
-- it changes after VACUUM FREEZE. The right way: a version column via trigger.
CREATE OR REPLACE FUNCTION bump_version() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.version := OLD.version + 1; NEW.updated_at := now(); RETURN NEW; END $$;
CREATE TRIGGER accounts_bump BEFORE UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION bump_version();

-- Oracle: ORA_ROWSCN as an implicit "version", valid only with ROWDEPENDENCIES
CREATE TABLE accounts_rd (id NUMBER PRIMARY KEY,
                          balance NUMBER(18,2) NOT NULL) ROWDEPENDENCIES;
SELECT balance, ORA_ROWSCN AS read_scn FROM accounts_rd WHERE id = 42;
UPDATE accounts_rd SET balance = :new_balance
WHERE  id = 42 AND ORA_ROWSCN = :read_scn;   -- ROWCOUNT=0 means the row changed

-- PostgreSQL has no autonomous transactions. The usual substitute: dblink
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE OR REPLACE FUNCTION log_error(p_msg text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  PERFORM dblink_exec('dbname=' || current_database(),
    format('INSERT INTO error_log(msg, at) VALUES (%L, now())', p_msg));
END $$;
-- Second option: a PROCEDURE with an internal COMMIT (PG 11+) — which is not
-- autonomous; it commits the outer transaction. Best option: log outside the DB.

CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log(msg, at) VALUES (p_msg, SYSTIMESTAMP);
  COMMIT;              -- mandatory: you must COMMIT or ROLLBACK before returning
EXCEPTION
  WHEN OTHERS THEN ROLLBACK; RAISE;
END;
/
-- The main transaction rolls back but the log entry survives
BEGIN
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  log_error('insufficient funds for account 1');
  ROLLBACK;
END;
/

-- PostgreSQL: explicit 2PC protocol — and it is *off* by default
-- ALTER SYSTEM SET max_prepared_transactions = 50;   -- requires restart
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'txn-42';     -- locks are held, the transaction is pending
COMMIT PREPARED 'txn-42';         -- or ROLLBACK PREPARED 'txn-42';

-- Forgotten pending transactions (dangerous: they paralyse vacuum)
SELECT gid, prepared, owner, now() - prepared AS age FROM pg_prepared_xacts;

-- Oracle: writing across a database link triggers 2PC automatically
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts@remote_db SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- In-doubt transactions
SELECT local_tran_id, global_tran_id, state, host FROM dba_2pc_pending;
-- Manual resolution (only when you are sure of the final decision):
-- COMMIT FORCE '1.23.45678';   /  ROLLBACK FORCE '1.23.45678';

BEGIN;
  UPDATE orders SET status = 'CONFIRMED' WHERE id = 42;
  INSERT INTO outbox(aggregate_id, event_type, payload)
  VALUES ('42', 'OrderConfirmed', jsonb_build_object('orderId', 42));
COMMIT;

-- The publisher scales out with SKIP LOCKED
UPDATE outbox SET published_at = now()
WHERE  id IN (SELECT id FROM outbox WHERE published_at IS NULL
              ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 100)
RETURNING id, event_type, payload;

UPDATE orders SET status = 'CONFIRMED' WHERE id = 42;
INSERT INTO outbox(aggregate_id, event_type, payload)
VALUES ('42', 'OrderConfirmed', JSON_OBJECT('orderId' VALUE 42));
COMMIT;

-- Oracle has an even better native option: transactional queues (TxEventQ / AQ)
-- DBMS_AQ.ENQUEUE inside the same transaction removes the dual-write problem.