Wiki.Quizz.vn

User Tools

Site Tools

Trace: • locking
mysql:tx:isolation

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
mysql:tx:isolation [2026/05/16 03:31] – created phong2018mysql:tx:isolation [2026/05/16 03:52] (current) – [Default level by database] phong2018
Line 1: Line 1:
-# SQL Isolation Levels+====== SQL Isolation Levels ======
  
 > A complete guide to understanding the 4 isolation levels in SQL — what they protect against, how they work internally, and when to use each one. > A complete guide to understanding the 4 isolation levels in SQL — what they protect against, how they work internally, and when to use each one.
  
----+----
  
-## Table of Contents+===== Table of Contents =====
  
-1. [What is an Isolation Level?](#1-what-is-an-isolation-level) +  - [[#1_what_is_an_isolation_level|1. What is an Isolation Level?]] 
-2. [READ UNCOMMITTED](#2-read-uncommitted) +  - [[#2_read_uncommitted|2. READ UNCOMMITTED]] 
-3. [READ COMMITTED](#3-read-committed) +  - [[#3_read_committed|3. READ COMMITTED]] 
-4. [REPEATABLE READ](#4-repeatable-read) +  - [[#4_repeatable_read|4. REPEATABLE READ]] 
-5. [SERIALIZABLE](#5-serializable) +  - [[#5_serializable|5. SERIALIZABLE]] 
-6. [How Isolation Works Internally](#6-how-isolation-works-internally) +  - [[#6_how_isolation_works_internally|6. How Isolation Works Internally]] 
-7. [Full Comparison Table](#7-full-comparison-table) +  - [[#7_full_comparison_table|7. Full Comparison Table]] 
-8. [How to Set Isolation Level](#8-how-to-set-isolation-level) +  - [[#8_how_to_set_isolation_level|8. How to Set Isolation Level]] 
-9. [Practical Guide — Which One to Use](#9-practical-guide--which-one-to-use)+  - [[#9_practical_guide_which_one_to_use|9. Practical Guide — Which One to Use]]
  
----+----
  
-## 1. What is an Isolation Level?+===== 1. What is an Isolation Level? =====
  
 When multiple transactions run at the same time, they can interfere with each other and produce incorrect results. **Isolation level** is a setting that controls **how much one transaction is shielded from the effects of other concurrent transactions**. When multiple transactions run at the same time, they can interfere with each other and produce incorrect results. **Isolation level** is a setting that controls **how much one transaction is shielded from the effects of other concurrent transactions**.
Line 25: Line 25:
 The SQL standard defines 4 isolation levels, ordered from weakest to strongest: The SQL standard defines 4 isolation levels, ordered from weakest to strongest:
  
-```+<code>
 READ UNCOMMITTED  →  READ COMMITTED  →  REPEATABLE READ  →  SERIALIZABLE READ UNCOMMITTED  →  READ COMMITTED  →  REPEATABLE READ  →  SERIALIZABLE
    (weakest)                                                   (strongest)    (weakest)                                                   (strongest)
Line 31: Line 31:
  highest performance                                      highest data safety  highest performance                                      highest data safety
  lowest data safety                                      lowest performance  lowest data safety                                      lowest performance
-```+</code>
  
 There is always a **trade-off**: stronger isolation = safer data, but more locking = slower performance. You pick the level that matches what your use case actually needs. There is always a **trade-off**: stronger isolation = safer data, but more locking = slower performance. You pick the level that matches what your use case actually needs.
  
-### The 3 read anomalies defined by the SQL standard+==== The 3 read anomalies defined by the SQL standard ====
  
 Before diving in, these are the problems isolation levels are designed to prevent: Before diving in, these are the problems isolation levels are designed to prevent:
  
-| Anomaly | Description | +^ Anomaly ^ Description ^
-|---------|-------------|+
 | **Dirty read** | Reading uncommitted changes from another transaction | | **Dirty read** | Reading uncommitted changes from another transaction |
 | **Non-repeatable read** | Reading the same row twice and getting different values (someone updated it) | | **Non-repeatable read** | Reading the same row twice and getting different values (someone updated it) |
 | **Phantom read** | Running the same query twice and getting different rows (someone inserted/deleted) | | **Phantom read** | Running the same query twice and getting different rows (someone inserted/deleted) |
  
----+----
  
-## 2. READ UNCOMMITTED+===== 2. READ UNCOMMITTED =====
  
-### What it does+==== What it does ====
  
 The **lowest** isolation level. A transaction can read data that other transactions have modified but **not yet committed**. There are virtually no restrictions. The **lowest** isolation level. A transaction can read data that other transactions have modified but **not yet committed**. There are virtually no restrictions.
  
-### Anomalies+==== Anomalies ====
  
-| Dirty read | Non-repeatable read | Phantom read | +^ Dirty read ^ Non-repeatable read ^ Phantom read ^
-|:---:|:---:|:---:|+
 | ❌ Can happen | ❌ Can happen | ❌ Can happen | | ❌ Can happen | ❌ Can happen | ❌ Can happen |
  
-### How it works+==== How it works ====
  
 No read locks are acquired. A transaction reads directly from the latest in-memory state of the data, regardless of whether other transactions have committed or not. No read locks are acquired. A transaction reads directly from the latest in-memory state of the data, regardless of whether other transactions have committed or not.
  
-### Example+==== Example ====
  
-```+<code>
 balance = 100 balance = 100
  
Line 72: Line 70:
 Txn B:  ROLLBACK         →  balance reverts to 100 Txn B:  ROLLBACK         →  balance reverts to 100
 Txn A:  is now working with 200, which never existed Txn A:  is now working with 200, which never existed
-```+</code>
  
-### SQL+==== SQL ====
  
-```sql+<code sql>
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 BEGIN; BEGIN;
Line 82: Line 80:
   -- May return uncommitted values from other transactions   -- May return uncommitted values from other transactions
 COMMIT; COMMIT;
-```+</code>
  
-### When to use+==== When to use ====
  
 Almost **never** in production. The only theoretical use case is large read-only analytics queries where approximate results are acceptable and you want to avoid any locking overhead at all. In practice, even then there are better options. Almost **never** in production. The only theoretical use case is large read-only analytics queries where approximate results are acceptable and you want to avoid any locking overhead at all. In practice, even then there are better options.
  
----+----
  
-## 3. READ COMMITTED+===== 3. READ COMMITTED =====
  
-### What it does+==== What it does ====
  
 The most **commonly used** isolation level. A transaction can only read data that has been **committed**. Any uncommitted changes from other transactions are invisible. The most **commonly used** isolation level. A transaction can only read data that has been **committed**. Any uncommitted changes from other transactions are invisible.
  
-### Anomalies+==== Anomalies ====
  
-| Dirty read | Non-repeatable read | Phantom read | +^ Dirty read ^ Non-repeatable read ^ Phantom read ^
-|:---:|:---:|:---:|+
 | ✅ Prevented | ❌ Can happen | ❌ Can happen | | ✅ Prevented | ❌ Can happen | ❌ Can happen |
  
-### How it works+==== How it works ====
  
 Each `SELECT` statement gets a **fresh snapshot** of committed data at the moment the statement runs — not at the moment the transaction began. This means if you run the same `SELECT` twice in one transaction, you may get different results if another transaction committed a change in between. Each `SELECT` statement gets a **fresh snapshot** of committed data at the moment the statement runs — not at the moment the transaction began. This means if you run the same `SELECT` twice in one transaction, you may get different results if another transaction committed a change in between.
  
-### Example+==== Example ====
  
-```+<code>
 balance = 1000 balance = 1000
  
Line 115: Line 112:
 Txn A:  SELECT balance  →  500           ← new snapshot, sees B's commit Txn A:  SELECT balance  →  500           ← new snapshot, sees B's commit
          same transaction, different result = non-repeatable read          same transaction, different result = non-repeatable read
-```+</code>
  
-### SQL+==== SQL ====
  
-```sql+<code sql>
 -- Default in PostgreSQL and SQL Server -- Default in PostgreSQL and SQL Server
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Line 133: Line 130:
  
 COMMIT; COMMIT;
-```+</code>
  
-### When to use+==== When to use ====
  
 The right default for **most web applications**. It prevents the most dangerous anomaly (dirty read) while keeping performance high. Used by default in: The right default for **most web applications**. It prevents the most dangerous anomaly (dirty read) while keeping performance high. Used by default in:
-- **PostgreSQL** ✓ +  * **PostgreSQL** ✓ 
-- **SQL Server** ✓ +  * **SQL Server** ✓ 
-- **Oracle** ✓+  * **Oracle** ✓
  
----+----
  
-## 4. REPEATABLE READ+===== 4. REPEATABLE READ =====
  
-### What it does+==== What it does ====
  
 A transaction always sees the **same snapshot** of data as it did at the start of the transaction. If you read a row, that row will return the same value for the entire duration of the transaction — even if another transaction updates it and commits. A transaction always sees the **same snapshot** of data as it did at the start of the transaction. If you read a row, that row will return the same value for the entire duration of the transaction — even if another transaction updates it and commits.
  
-### Anomalies+==== Anomalies ====
  
-| Dirty read | Non-repeatable read | Phantom read | +^ Dirty read ^ Non-repeatable read ^ Phantom read ^
-|:---:|:---:|:---:|+
 | ✅ Prevented | ✅ Prevented | ❌ Can happen* | | ✅ Prevented | ✅ Prevented | ❌ Can happen* |
  
-> \* MySQL's `REPEATABLE READ` also prevents phantom reads via MVCC. PostgreSQL's does not fully prevent them in all cases.+> * MySQL's `REPEATABLE READ` also prevents phantom reads via MVCC. PostgreSQL's does not fully prevent them in all cases.
  
-### How it works+==== How it works ====
  
 The database takes a **snapshot at the start of the transaction** (`BEGIN`). All `SELECT` statements in that transaction read from this fixed snapshot — they never see changes committed by other transactions after the transaction began. The database takes a **snapshot at the start of the transaction** (`BEGIN`). All `SELECT` statements in that transaction read from this fixed snapshot — they never see changes committed by other transactions after the transaction began.
  
-```+<code>
 Transaction starts → snapshot created → all reads use this snapshot Transaction starts → snapshot created → all reads use this snapshot
                                               ↓                                               ↓
Line 168: Line 164:
                                               ↓                                               ↓
                               those changes are invisible to this transaction                               those changes are invisible to this transaction
-```+</code>
  
-### Example+==== Example ====
  
-```+<code>
 balance = 1000 balance = 1000
  
Line 180: Line 176:
 Txn A:  SELECT balance  →  still 1000  ← reads from snapshot, not current data Txn A:  SELECT balance  →  still 1000  ← reads from snapshot, not current data
 Txn A:  COMMIT Txn A:  COMMIT
-```+</code>
  
-### SQL+==== SQL ====
  
-```sql+<code sql>
 -- Default in MySQL (InnoDB) -- Default in MySQL (InnoDB)
 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Line 198: Line 194:
  
 COMMIT; COMMIT;
-```+</code>
  
-### Phantom read still possible (PostgreSQL)+==== Phantom read still possible (PostgreSQL) ====
  
 Even though each row is stable, new rows can still appear: Even though each row is stable, new rows can still appear:
  
-```sql+<code sql>
 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- PostgreSQL BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- PostgreSQL
  
Line 215: Line 211:
   -- → 4 rows  ← phantom read! (PostgreSQL)   -- → 4 rows  ← phantom read! (PostgreSQL)
   -- → 3 rows  ← MySQL MVCC prevents this   -- → 3 rows  ← MySQL MVCC prevents this
-```+</code>
  
-### When to use+==== When to use ====
  
 Use when your transaction needs **consistent reads across multiple queries** — reports, analytics, financial summaries. Used by default in: Use when your transaction needs **consistent reads across multiple queries** — reports, analytics, financial summaries. Used by default in:
-- **MySQL (InnoDB)** ✓+  * **MySQL (InnoDB)** ✓
  
----+----
  
-## 5. SERIALIZABLE+===== 5. SERIALIZABLE =====
  
-### What it does+==== What it does ====
  
 The **strongest** isolation level. Transactions behave as if they were executed **one at a time, serially** — even though they actually run concurrently. All anomalies are prevented, including write skew. The **strongest** isolation level. Transactions behave as if they were executed **one at a time, serially** — even though they actually run concurrently. All anomalies are prevented, including write skew.
  
-### Anomalies+==== Anomalies ====
  
-| Dirty read | Non-repeatable read | Phantom read | Lost update | Write skew | +^ Dirty read ^ Non-repeatable read ^ Phantom read ^ Lost update ^ Write skew ^
-|:---:|:---:|:---:|:---:|:---:|+
 | ✅ Prevented | ✅ Prevented | ✅ Prevented | ✅ Prevented | ✅ Prevented | | ✅ Prevented | ✅ Prevented | ✅ Prevented | ✅ Prevented | ✅ Prevented |
  
-### How it works+==== How it works ====
  
 There are two implementation approaches depending on the database: There are two implementation approaches depending on the database:
  
 **Approach 1 — Lock-based (traditional)** **Approach 1 — Lock-based (traditional)**
 +
 The database places range locks on all rows and gaps scanned by a query. Other transactions cannot insert, update, or delete data in those ranges until the lock is released. The database places range locks on all rows and gaps scanned by a query. Other transactions cannot insert, update, or delete data in those ranges until the lock is released.
  
 **Approach 2 — SSI (Serializable Snapshot Isolation — PostgreSQL)** **Approach 2 — SSI (Serializable Snapshot Isolation — PostgreSQL)**
 +
 Transactions run freely using snapshots (no extra locking). The database tracks read/write dependencies between transactions. At commit time, if a conflict is detected that would violate serializability → one transaction is aborted with a `serialization failure` error and must retry. Transactions run freely using snapshots (no extra locking). The database tracks read/write dependencies between transactions. At commit time, if a conflict is detected that would violate serializability → one transaction is aborted with a `serialization failure` error and must retry.
  
-### Example — Write skew prevented+==== Example — Write skew prevented ====
  
-```+<code>
 Rule: at least 1 doctor must be on call Rule: at least 1 doctor must be on call
 Currently: Alice on_call=true, Bob on_call=true Currently: Alice on_call=true, Bob on_call=true
Line 257: Line 254:
 Without SERIALIZABLE → both succeed → 0 doctors on call (rule violated) Without SERIALIZABLE → both succeed → 0 doctors on call (rule violated)
 With SERIALIZABLE    → one transaction is aborted → must retry → rule preserved With SERIALIZABLE    → one transaction is aborted → must retry → rule preserved
-```+</code>
  
-### SQL+==== SQL ====
  
-```sql+<code sql>
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 BEGIN; BEGIN;
Line 273: Line 270:
 -- If a conflict is detected: ERROR: could not serialize access -- If a conflict is detected: ERROR: could not serialize access
 -- → catch this error in your code and retry the transaction -- → catch this error in your code and retry the transaction
-```+</code>
  
-### Retry pattern for serialization failures+==== Retry pattern for serialization failures ====
  
-```python+<code python>
 import psycopg2 import psycopg2
  
Line 293: Line 290:
             print(f"Serialization conflict, retrying... ({attempt + 1})")             print(f"Serialization conflict, retrying... ({attempt + 1})")
     raise Exception("Failed after max retries")     raise Exception("Failed after max retries")
-```+</code>
  
-```javascript+<code javascript>
 async function runSerializable(pool, fn) { async function runSerializable(pool, fn) {
   const MAX_RETRY = 5;   const MAX_RETRY = 5;
Line 319: Line 316:
   throw new Error('Failed after max retries');   throw new Error('Failed after max retries');
 } }
-```+</code>
  
-### When to use+==== When to use ====
  
 Use for **critical financial operations** where correctness is non-negotiable: Use for **critical financial operations** where correctness is non-negotiable:
-- Bank transfers +  * Bank transfers 
-- Booking systems (rooms, flights, tickets) +  * Booking systems (rooms, flights, tickets) 
-- Inventory reservation under high concurrency +  * Inventory reservation under high concurrency 
-- Any operation with complex shared invariants+  * Any operation with complex shared invariants
  
 **Performance note:** `SERIALIZABLE` is significantly slower under high concurrency because of the extra tracking or locking overhead. Only apply it to transactions that truly need it — not the entire application. **Performance note:** `SERIALIZABLE` is significantly slower under high concurrency because of the extra tracking or locking overhead. Only apply it to transactions that truly need it — not the entire application.
  
----+----
  
-## 6. How Isolation Works Internally+===== 6. How Isolation Works Internally =====
  
 Databases implement isolation using two main techniques: Databases implement isolation using two main techniques:
  
-### Technique 1 — Locking+==== Technique 1 — Locking ====
  
 The traditional approach. Transactions acquire locks on data they read or write. Other transactions that need the same data must wait. The traditional approach. Transactions acquire locks on data they read or write. Other transactions that need the same data must wait.
  
-```+<code>
 Txn A reads row 1  →  acquires Shared Lock (S) Txn A reads row 1  →  acquires Shared Lock (S)
 Txn B wants to write row 1  →  must wait for A to release S lock Txn B wants to write row 1  →  must wait for A to release S lock
 Txn A commits  →  releases lock Txn A commits  →  releases lock
 Txn B proceeds Txn B proceeds
-```+</code>
  
 **Lock types:** **Lock types:**
-| Lock | Who can hold it | Blocks | + 
-|------|----------------|--------|+^ Lock ^ Who can hold it ^ Blocks ^
 | Shared (S) | Multiple readers simultaneously | Writers only | | Shared (S) | Multiple readers simultaneously | Writers only |
 | Exclusive (X) | One writer at a time | All readers and writers | | Exclusive (X) | One writer at a time | All readers and writers |
Line 356: Line 353:
 **Downside:** Can cause **deadlocks** — A holds lock 1 waiting for lock 2, B holds lock 2 waiting for lock 1. Both wait forever. Databases detect and resolve this by aborting one transaction. **Downside:** Can cause **deadlocks** — A holds lock 1 waiting for lock 2, B holds lock 2 waiting for lock 1. Both wait forever. Databases detect and resolve this by aborting one transaction.
  
----+----
  
-### Technique 2 — MVCC (Multi-Version Concurrency Control)+==== Technique 2 — MVCC (Multi-Version Concurrency Control) ====
  
 Used by **PostgreSQL**, **MySQL InnoDB**, and Oracle. Instead of blocking readers, the database keeps **multiple versions** of each row. Each transaction reads from its own consistent snapshot. Used by **PostgreSQL**, **MySQL InnoDB**, and Oracle. Instead of blocking readers, the database keeps **multiple versions** of each row. Each transaction reads from its own consistent snapshot.
  
-```+<code>
 Row versions stored in database: Row versions stored in database:
   balance=1000  (committed at T=1)   balance=1000  (committed at T=1)
Line 371: Line 368:
 Txn B started at T=7  →  always sees balance=500 Txn B started at T=7  →  always sees balance=500
 New query at T=10     →  sees balance=200 New query at T=10     →  sees balance=200
-```+</code>
  
 **Key benefit:** Readers never block writers, writers never block readers. Much higher concurrency than pure locking. **Key benefit:** Readers never block writers, writers never block readers. Much higher concurrency than pure locking.
Line 377: Line 374:
 **Downside:** Old row versions accumulate and must be cleaned up (PostgreSQL calls this **VACUUM**). **Downside:** Old row versions accumulate and must be cleaned up (PostgreSQL calls this **VACUUM**).
  
----+----
  
-### Which technique does each database use?+==== Which technique does each database use? ====
  
-| Database | Technique | Notes | +^ Database ^ Technique ^ Notes ^
-|----------|-----------|-------|+
 | PostgreSQL | MVCC + SSI for SERIALIZABLE | Readers never block writers | | PostgreSQL | MVCC + SSI for SERIALIZABLE | Readers never block writers |
 | MySQL InnoDB | MVCC + gap locks | MVCC for reads, locks for writes | | MySQL InnoDB | MVCC + gap locks | MVCC for reads, locks for writes |
Line 388: Line 384:
 | Oracle | MVCC | Similar to PostgreSQL | | Oracle | MVCC | Similar to PostgreSQL |
  
----+----
  
-## 7. Full Comparison Table+===== 7. Full Comparison Table =====
  
-### Anomaly protection by level+==== Anomaly protection by level ====
  
-| Isolation Level | Dirty Read | Non-repeatable Read | Phantom Read | Lost Update | Write Skew | +^ Isolation Level ^ Dirty Read ^ Non-repeatable Read ^ Phantom Read ^ Lost Update ^ Write Skew ^
-|-----------------|:---:|:---:|:---:|:---:|:---:|+
 | READ UNCOMMITTED | ❌ | ❌ | ❌ | ❌ | ❌ | | READ UNCOMMITTED | ❌ | ❌ | ❌ | ❌ | ❌ |
 | READ COMMITTED | ✅ | ❌ | ❌ | ❌ | ❌ | | READ COMMITTED | ✅ | ❌ | ❌ | ❌ | ❌ |
Line 401: Line 396:
 | SERIALIZABLE | ✅ | ✅ | ✅ | ✅ | ✅ | | SERIALIZABLE | ✅ | ✅ | ✅ | ✅ | ✅ |
  
-### Default level by database+==== Default level by database ====
  
-| Database | Default Level | Dirty Read | Non-repeatable Read | Phantom Read | +^ Database ^ Default Level ^ Dirty Read ^ Non-repeatable Read ^ Phantom Read ^
-|----------|---------------|:---:|:---:|:---:| +
-| PostgreSQL | READ COMMITTED | ✅ No | ❌ Possible | ❌ Possible |+
 | MySQL InnoDB | REPEATABLE READ | ✅ No | ✅ No | ✅ No* | | MySQL InnoDB | REPEATABLE READ | ✅ No | ✅ No | ✅ No* |
 +| PostgreSQL | READ COMMITTED | ✅ No | ❌ Possible | ❌ Possible |
 | SQL Server | READ COMMITTED | ✅ No | ❌ Possible | ❌ Possible | | SQL Server | READ COMMITTED | ✅ No | ❌ Possible | ❌ Possible |
 | Oracle | READ COMMITTED | ✅ No | ❌ Possible | ❌ Possible | | Oracle | READ COMMITTED | ✅ No | ❌ Possible | ❌ Possible |
  
-> \* MySQL prevents phantom reads via MVCC at REPEATABLE READ level.+> * MySQL prevents phantom reads via MVCC at REPEATABLE READ level.
  
-### Performance vs safety trade-off+==== Performance vs safety trade-off ====
  
-```+<code>
 READ UNCOMMITTED  ──────────────────────────────►  SERIALIZABLE READ UNCOMMITTED  ──────────────────────────────►  SERIALIZABLE
         ↑                                                 ↑         ↑                                                 ↑
Line 420: Line 414:
    Most concurrent                             Least concurrent    Most concurrent                             Least concurrent
    Least safe                                     Most safe    Least safe                                     Most safe
-```+</code>
  
----+----
  
-## 8. How to Set Isolation Level+===== 8. How to Set Isolation Level =====
  
-### For a single transaction+==== For a single transaction ====
  
-```sql+<code sql>
 -- Set before BEGIN -- Set before BEGIN
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Line 439: Line 433:
   -- your SQL here   -- your SQL here
 COMMIT; COMMIT;
-```+</code>
  
-### For the current session+==== For the current session ====
  
-```sql+<code sql>
 -- PostgreSQL -- PostgreSQL
 SET default_transaction_isolation = 'repeatable read'; SET default_transaction_isolation = 'repeatable read';
Line 453: Line 447:
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 -- (applies to all subsequent transactions in the session) -- (applies to all subsequent transactions in the session)
-```+</code>
  
-### For all new connections (global default)+==== For all new connections (global default) ====
  
-```sql+<code sql>
 -- MySQL: set globally (requires SUPER privilege) -- MySQL: set globally (requires SUPER privilege)
 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Line 463: Line 457:
 -- PostgreSQL: set in postgresql.conf -- PostgreSQL: set in postgresql.conf
 -- default_transaction_isolation = 'read committed' -- default_transaction_isolation = 'read committed'
-```+</code>
  
-### In application code+==== In application code ====
  
-```python+<code python>
 # Python (psycopg2 — PostgreSQL) # Python (psycopg2 — PostgreSQL)
 import psycopg2 import psycopg2
Line 474: Line 468:
 conn = psycopg2.connect(...) conn = psycopg2.connect(...)
 conn.set_isolation_level(extensions.ISOLATION_LEVEL_REPEATABLE_READ) conn.set_isolation_level(extensions.ISOLATION_LEVEL_REPEATABLE_READ)
-```+</code>
  
-```javascript+<code javascript>
 // Node.js (pg — PostgreSQL) // Node.js (pg — PostgreSQL)
 const client = await pool.connect(); const client = await pool.connect();
Line 482: Line 476:
 // ... your queries ... // ... your queries ...
 await client.query('COMMIT'); await client.query('COMMIT');
-```+</code>
  
-```java+<code java>
 // Java (JDBC) // Java (JDBC)
 connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
Line 490: Line 484:
 // ... your queries ... // ... your queries ...
 connection.commit(); connection.commit();
-```+</code>
  
-```go+<code go>
 // Go (database/sql) // Go (database/sql)
 tx, err := db.BeginTx(ctx, &sql.TxOptions{ tx, err := db.BeginTx(ctx, &sql.TxOptions{
     Isolation: sql.LevelRepeatableRead,     Isolation: sql.LevelRepeatableRead,
 }) })
-```+</code>
  
----+----
  
-## 9. Practical Guide — Which One to Use+===== 9. Practical Guide — Which One to Use =====
  
-### Decision flowchart+==== Decision flowchart ====
  
-```+<code>
 Does your transaction read data more than once Does your transaction read data more than once
 and needs consistent results throughout? and needs consistent results throughout?
Line 518: Line 512:
                   └── Yes → SERIALIZABLE                   └── Yes → SERIALIZABLE
                             (or SELECT FOR UPDATE for specific rows)                             (or SELECT FOR UPDATE for specific rows)
-```+</code>
  
-### Recommendations by use case+==== Recommendations by use case ====
  
-| Use Case | Recommended Level | Why | +^ Use Case ^ Recommended Level ^ Why ^
-|----------|-------------------|-----|+
 | Read a single row | `READ COMMITTED` | No repeated reads needed | | Read a single row | `READ COMMITTED` | No repeated reads needed |
 | Display a news feed or product listing | `READ COMMITTED` | Slight inconsistency is acceptable | | Display a news feed or product listing | `READ COMMITTED` | Slight inconsistency is acceptable |
Line 533: Line 526:
 | Update a user profile | `READ COMMITTED` + Optimistic lock | Conflicts are rare | | Update a user profile | `READ COMMITTED` + Optimistic lock | Conflicts are rare |
  
-### The most practical approach for most apps+==== The most practical approach for most apps ====
  
 Most production applications **do not need to change the default isolation level**. Instead: Most production applications **do not need to change the default isolation level**. Instead:
  
-```sql+<code sql>
 -- 1. Use READ COMMITTED (default) for regular queries -- 1. Use READ COMMITTED (default) for regular queries
  
Line 554: Line 547:
  
 -- 5. Only raise to SERIALIZABLE for truly complex invariants -- 5. Only raise to SERIALIZABLE for truly complex invariants
-```+</code>
  
-### Common mistakes to avoid+==== Common mistakes to avoid ====
  
-| Mistake | Problem | Fix | +^ Mistake ^ Problem ^ Fix ^
-|---------|---------|-----|+
 | Using `READ UNCOMMITTED` | Dirty reads, unreliable data | Use `READ COMMITTED` or higher | | Using `READ UNCOMMITTED` | Dirty reads, unreliable data | Use `READ COMMITTED` or higher |
 | Setting everything to `SERIALIZABLE` | Severe performance degradation | Only use where necessary | | Setting everything to `SERIALIZABLE` | Severe performance degradation | Only use where necessary |
Line 566: Line 558:
 | Ignoring serialization failure errors | Silent data corruption | Always catch and retry | | Ignoring serialization failure errors | Silent data corruption | Always catch and retry |
  
----+----
  
-*For related topics, see: SQL Transaction Anomalies, Locking Strategies, MVCC Internals.*+''For related topics, see: SQL Transaction Anomalies, Locking Strategies, MVCC Internals.''
mysql/tx/isolation.1778902282.txt.gz · Last modified: by phong2018