-- Transfer $500 from Account A to Account B
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- Suppose a power failure or application crash occurs here

UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

-- Without atomicity: Account A loses $500 but Account B never receives it (partial failure)
-- With atomicity: on crash/error, the entire transaction rolls back to original state

-- Inspect active transactions and undo log usage
SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
  trx_rows_modified,
  trx_undo_log_size_bytes,
  trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- Undo log size is bounded by innodb_max_undo_log_size
SHOW VARIABLES LIKE 'innodb_max_undo_log_size';
-- Default: 1GB — long transactions can fill this, causing errors

-- Check undo tablespace status
SELECT TABLESPACE_NAME, FILE_NAME, STATE
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

-- NOT NULL constraint enforces consistency
START TRANSACTION;
INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', NULL);  -- email is NOT NULL
-- ERROR 1048: Column 'email' cannot be null
-- INSERT is rejected; database remains consistent

-- FOREIGN KEY constraint enforces referential integrity
INSERT INTO orders (customer_id, total)
VALUES (99999, 100);  -- customer 99999 does not exist
-- ERROR 1452: Cannot add or update a child row: foreign key constraint fails

-- UNIQUE constraint prevents duplicates
INSERT INTO users (email) VALUES ('alice@example.com');
INSERT INTO users (email) VALUES ('alice@example.com');
-- ERROR 1062: Duplicate entry for key 'email'

-- CHECK constraint (MySQL 8.0.16+) enforces business rules at the DB level
ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

UPDATE products SET price = -5 WHERE product_id = 1;
-- ERROR 3819: Check constraint 'chk_price_positive' is violated

-- MySQL default isolation level: REPEATABLE READ
SHOW VARIABLES LIKE 'transaction_isolation';
-- Value: REPEATABLE-READ

-- Session A: start a transaction and read
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;  -- reads 1000

-- Session B (concurrent): update and commit
UPDATE accounts SET balance = 900 WHERE account_id = 1;
COMMIT;

-- Session A: reads again — still sees 1000 (MVCC snapshot from START TRANSACTION)
SELECT balance FROM accounts WHERE account_id = 1;  -- still 1000
COMMIT;  -- snapshot released; next read would show 900

-- To get the latest committed value within a transaction, use READ COMMITTED:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;  -- 900 (latest committed)

-- Session A: range query with a lock
START TRANSACTION;
SELECT * FROM orders WHERE total BETWEEN 100 AND 200 FOR UPDATE;
-- InnoDB places gap locks on the range [100, 200]

-- Session B: tries to INSERT a row in the locked range
INSERT INTO orders (customer_id, total) VALUES (1, 150);
-- BLOCKS until Session A commits or rolls back

-- Session A: commits — Session B's INSERT can now proceed
COMMIT;

-- Check current setting
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- For financial/critical data: always use 1 (default)
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- For less critical high-write workloads (e.g. analytics ingestion):
-- value 2 is acceptable with a battery-backed RAID controller
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- Group commit tuning variables
SHOW VARIABLES LIKE 'binlog_group_commit_sync_delay';    -- microseconds to wait (default 0)
SHOW VARIABLES LIKE 'binlog_group_commit_sync_no_delay_count'; -- max transactions per batch

-- Monitor group commit efficiency
SHOW GLOBAL STATUS LIKE 'Binlog_commits';
SHOW GLOBAL STATUS LIKE 'Binlog_group_commits';
-- If Binlog_group_commits << Binlog_commits, batching is working well

-- Check redo log configuration
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';   -- MySQL 8.0.30+ (in bytes)
SHOW VARIABLES LIKE 'innodb_log_file_size';        -- Older MySQL
SHOW VARIABLES LIKE 'innodb_log_files_in_group';   -- Number of redo log files

-- Monitor redo log usage (how full is the redo log?)
SELECT
  (VARIABLE_VALUE / 1024 / 1024) AS redo_log_mb_used
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_redo_log_current_lsn';

-- Setup
CREATE TABLE wallets (
  wallet_id INT PRIMARY KEY,
  owner     VARCHAR(100) NOT NULL,
  balance   DECIMAL(10,2) NOT NULL DEFAULT 0,
  CONSTRAINT chk_balance_non_negative CHECK (balance >= 0)
) ENGINE = InnoDB;

INSERT INTO wallets VALUES (1, 'Alice', 1000.00), (2, 'Bob', 500.00);

-- ATOMICITY: partial failure rolls back everything
START TRANSACTION;
UPDATE wallets SET balance = balance - 200 WHERE wallet_id = 1;   -- OK: 1000 -> 800
UPDATE wallets SET balance = balance - 9999 WHERE wallet_id = 2;  -- FAILS: CHECK violation
-- Both updates are rolled back — Alice still has 1000, Bob still has 500
ROLLBACK;
SELECT * FROM wallets;  -- Both at original values

-- CONSISTENCY: constraint prevents invalid state
START TRANSACTION;
UPDATE wallets SET balance = -100 WHERE wallet_id = 1;
-- ERROR 3819: Check constraint 'chk_balance_non_negative' is violated
ROLLBACK;

-- ISOLATION (MVCC): Session A sees snapshot, not Session B's uncommitted change
-- [Session A] START TRANSACTION;
-- [Session A] SELECT balance FROM wallets WHERE wallet_id = 1;  -- 1000
-- [Session B] UPDATE wallets SET balance = 800 WHERE wallet_id = 1; (not committed)
-- [Session A] SELECT balance FROM wallets WHERE wallet_id = 1;  -- still 1000
-- [Session B] COMMIT;
-- [Session A] SELECT balance FROM wallets WHERE wallet_id = 1;  -- still 1000 (snapshot)
-- [Session A] COMMIT;

-- DURABILITY: after COMMIT, data survives a server restart
START TRANSACTION;
UPDATE wallets SET balance = balance - 100 WHERE wallet_id = 1;
UPDATE wallets SET balance = balance + 100 WHERE wallet_id = 2;
COMMIT;
-- Even after: sudo systemctl restart mysql
-- SELECT * FROM wallets;  -- Alice 900, Bob 600 — data is permanent

-- Change isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Change isolation level for a single transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- ... all reads here use serializable isolation ...
COMMIT;

-- Check current isolation level
SELECT @@transaction_isolation;  -- or @@tx_isolation on older MySQL

-- Check autocommit status
SELECT @@autocommit;  -- 1 = enabled (default)

-- With autocommit ON, each statement is its own transaction
INSERT INTO logs (msg) VALUES ('event 1');   -- immediately committed
INSERT INTO logs (msg) VALUES ('event 2');   -- immediately committed

-- To make them atomic together, use an explicit transaction
START TRANSACTION;
INSERT INTO logs (msg) VALUES ('event 1');
INSERT INTO logs (msg) VALUES ('event 2');
COMMIT;  -- both committed atomically

-- Disable autocommit for the session (BEGIN still works when autocommit is ON)
SET SESSION autocommit = 0;

-- After crash recovery completes, check InnoDB status
SHOW ENGINE INNODB STATUSG
-- Look for: "Log sequence number", "Last checkpoint at"
-- These confirm the redo log was replayed successfully

-- Monitor recovery progress in the MySQL error log
-- tail -f /var/log/mysql/error.log
-- You will see lines like:
-- [Note] InnoDB: Starting crash recovery.
-- [Note] InnoDB: Doing recovery: scanned up to log sequence number ...
-- [Note] InnoDB: Database was not shut down normally!
-- [Note] InnoDB: Starting crash recovery...
-- [Note] InnoDB: Recovered in X seconds.

-- Verify redo log is properly sized for your write workload
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';   -- MySQL 8.0.30+
-- Recommended: set to cover at least 1 hour of peak write I/O
-- Too small: frequent checkpoints reduce write throughput
-- Too large: longer recovery time after crash

-- Set isolation level for a single transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

-- Confirm current level within a transaction
SELECT @@transaction_isolation;

-- Set for current session (all future transactions in this session)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set globally (affects all new connections)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Persist in my.cnf for all connections:
-- transaction_isolation = REPEATABLE-READ