Practical Usage of Snapshot Isolation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- =====================================
-- PostgreSQL SI Monitoring Queries
-- =====================================
 
-- 1. Long-running transactions (pinning snapshots)
SELECT pid, now() - xact_start as age, query
FROM pg_stat_activity 
WHERE xact_start IS NOT NULL 
AND state != 'idle'
ORDER BY age DESC 
LIMIT 10;
 
-- 2. Table bloat (dead tuples)
SELECT relname, n_live_tup, n_dead_tup, 
       n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) as dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables 
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
 
-- 3. Vacuum progress
SELECT * FROM pg_stat_progress_vacuum;
 
-- 4. Transaction ID age (wraparound risk)
SELECT datname, age(datfrozenxid) as xid_age
FROM pg_database
ORDER BY xid_age DESC;
 
-- 5. Serialization failures (check logs or app metrics)
-- These appear as:
--   ERROR: could not serialize access due to concurrent update
--   ERROR: could not serialize access due to read/write dependencies
 
-- =====================================
-- MySQL InnoDB Monitoring
-- =====================================
 
-- 1. Transaction list
SELECT * FROM information_schema.innodb_trx 
ORDER BY trx_started;
 
-- 2. History list length (undo not yet purged)
SHOW ENGINE INNODB STATUS;  -- Look for "History list length"
 
-- 3. Alternative: innodb_metrics
SELECT NAME, COUNT FROM information_schema.innodb_metrics 
WHERE NAME LIKE 'trx%' OR NAME LIKE 'purge%';
 
-- 4. Lock waits
SELECT * FROM performance_schema.data_lock_waits;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- =====================================
-- Performance Optimization Examples
-- =====================================
 
-- 1. Use appropriate isolation per query
BEGIN ISOLATION LEVEL READ COMMITTED;  -- Default, cheapest
SELECT * FROM products WHERE category = 'electronics';
COMMIT;
 
-- Only use higher isolation when needed
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE user_id = 123;
SELECT * FROM transactions WHERE account_id IN (...);
-- Consistent view of account + transactions
COMMIT;
 
-- 2. Reduce read set with selective queries
-- BAD: Reads all columns, all rows
SELECT * FROM large_table;
 
-- GOOD: Reads only needed columns, filtered rows
SELECT id, name, status FROM large_table 
WHERE created_at > NOW() - INTERVAL '1 day'
AND status = 'active';
 
-- 3. Use SKIP LOCKED for queue-like patterns
-- Works well with SI for parallel processing
BEGIN;
SELECT id, task FROM job_queue 
WHERE status = 'pending'
ORDER BY priority DESC
LIMIT 10
FOR UPDATE SKIP LOCKED;
 
UPDATE job_queue SET status = 'processing', worker = 'me'
WHERE id IN (...);
COMMIT;
 
-- 4. Avoid holding transactions during external calls
-- BAD: Transaction open during HTTP call
BEGIN;
SELECT data FROM orders WHERE id = 123;
-- ... HTTP call to payment provider (1-2 seconds) ...
UPDATE orders SET status = 'paid' WHERE id = 123;
COMMIT;
 
-- GOOD: Minimize transaction scope
SELECT data FROM orders WHERE id = 123;
-- ... HTTP call to payment provider (outside transaction) ...
BEGIN;
UPDATE orders SET status = 'paid', payment_id = ? WHERE id = 123 AND status = 'pending';
COMMIT;