Can my DB?

-- Set for the current transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Snapshot is held for the duration of the transaction
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

-- Set default for the session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

• Default is READ COMMITTED. READ UNCOMMITTED is accepted but runs as READ COMMITTED.
• REPEATABLE READ holds a transaction-start snapshot, so it blocks phantoms naturally.
• SERIALIZABLE uses Serializable Snapshot Isolation: no read locks, but write conflicts can raise 40001 and force retry.

-- Set for the next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

-- Set for the session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set globally
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

• Default is REPEATABLE READ, not READ COMMITTED.
• REPEATABLE READ reads from a consistent snapshot and uses gap and next-key locks to block phantoms.
• SERIALIZABLE adds shared locks on every read row on top of the REPEATABLE READ behavior.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

• Same four ANSI levels and same REPEATABLE READ default as MySQL.
• InnoDB reads are nonblocking at every level except SERIALIZABLE.

-- Read Committed (default)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Read Only (consistent snapshot, no writes)
SET TRANSACTION READ ONLY;

• Only READ COMMITTED (default) and SERIALIZABLE are offered. No REPEATABLE READ keyword; use SERIALIZABLE for equivalent guarantees.
• Oracle MVCC prevents dirty reads at every level, so READ UNCOMMITTED is not meaningful.
• SERIALIZABLE takes a transaction-start snapshot and raises ORA-08177 on write conflicts.
• READ ONLY gives read-consistent views without serializable write-conflict detection.

-- Standard isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

-- Snapshot isolation (MVCC-based, requires DB option)
-- ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

• Five levels: the four ANSI plus SNAPSHOT. Default is READ COMMITTED, lock-based.
• READ_COMMITTED_SNAPSHOT (database option) swaps the default READ COMMITTED for a versioned, lock-free implementation.
• SNAPSHOT is separate: it takes a transaction-start snapshot and resolves write conflicts with error 3960.
• Both row-versioning modes keep their version store in tempdb unless Accelerated Database Recovery moves it per-database.

-- WAL mode allows concurrent readers alongside a single writer
PRAGMA journal_mode=WAL;

-- SQLite serializes all write transactions by default
-- BEGIN DEFERRED, BEGIN IMMEDIATE, BEGIN EXCLUSIVE control locking
BEGIN IMMEDIATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

• No SET TRANSACTION ISOLATION LEVEL syntax. Isolation is a function of journal mode and BEGIN variant.
• Writers are single-threaded, so writes are effectively serializable.
• WAL mode lets readers run concurrently with the one writer on a consistent snapshot.
• BEGIN DEFERRED, IMMEDIATE, and EXCLUSIVE control when the write lock is acquired.

-- Inspect the database isolation level
SELECT db_name, isolation_level
FROM stv_db_isolation_level;

BEGIN;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

• Isolation is set per database: SNAPSHOT (default on new clusters and serverless) or SERIALIZABLE.
• The four ANSI levels are not exposed.
• Write conflicts under either level can still raise a serialization error the client must retry.

-- Set isolation at statement level
SELECT balance FROM accounts WHERE id = 1 WITH UR; -- Uncommitted Read
SELECT balance FROM accounts WHERE id = 1 WITH CS; -- Cursor Stability (default)
SELECT balance FROM accounts WHERE id = 1 WITH RS; -- Read Stability
SELECT balance FROM accounts WHERE id = 1 WITH RR; -- Repeatable Read

-- Set for the session
SET CURRENT ISOLATION = CS;

• Four levels under DB2 names: UR (Uncommitted Read), CS (Cursor Stability, default, ≈ RC), RS (Read Stability, ≈ RR), RR (Repeatable Read, ≈ SERIALIZABLE).
• Isolation can be set per statement with WITH UR | CS | RS | RR on a SELECT.

-- Snowflake only supports Read Committed
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Each statement sees committed data as of statement start
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

• READ COMMITTED is the only level. Each statement in a multi-statement transaction takes a fresh snapshot at statement start.
• There is no way to hold one snapshot across statements (no REPEATABLE READ or SERIALIZABLE).

-- BigQuery multi-statement transaction
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT TRANSACTION;

• Isolation is fixed and cannot be configured. Single statements are atomic.
• Multi-statement transactions run at snapshot isolation, anchored at transaction start.
• Conflicting mutations are resolved optimistically: the loser gets an error and must retry.