Differences

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

--- mysql:tx [2026/04/28 02:17] – created phong2018
+++ mysql:tx [2026/05/17 21:57] (current) – removed phong2018
@@ Line 1: / Line 1: @@
-====== Transactions & Concurrency (MySQL) ======
-===== 1. Overview =====
-A **transaction** is a group of SQL statements executed as a single unit.
-A **concurrent system** allows multiple users/sessions to access the database at the same time.
-Goal:
-  * Ensure **data consistency**
-  * Avoid **conflicts** when multiple users read/write data
----
-===== 2. ACID Properties =====
-Transactions follow **ACID**:
-  * **Atomicity**: All succeed or all fail
-  * **Consistency**: Data remains valid
-  * **Isolation**: Transactions don't interfere incorrectly
-  * **Durability**: Data is saved permanently after commit
----
-===== 3. Basic Transaction Syntax =====
-<code sql>
-START TRANSACTION;
--- SQL statements
-UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-COMMIT;
-</code>
-Rollback example:
-<code sql>
-START TRANSACTION;
-UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-ROLLBACK;
-</code>
----
-===== 4. Concurrency Problems =====
-==== 4.1 Dirty Read ====
-Reading uncommitted data from another transaction
-==== 4.2 Non-repeatable Read ====
-Same query returns different results within a transaction
-==== 4.3 Phantom Read ====
-New rows appear in repeated queries
----
-===== 5. Isolation Levels =====
-^ Level ^ Dirty Read ^ Non-repeatable Read ^ Phantom Read ^
-| READ UNCOMMITTED | YES | YES | YES |
-| READ COMMITTED   | NO  | YES | YES |
-| REPEATABLE READ  | NO  | NO  | YES |
-| SERIALIZABLE     | NO  | NO  | NO  |
-Set isolation level:
-<code sql>
-SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-</code>
----
-===== 6. Locks in MySQL =====
-Locks control access to data during transactions.
-==== 6.1 Read Lock (Shared Lock) ====
-Allows multiple reads, blocks writes.
-<code sql>
-SELECT * FROM users WHERE id = 1 FOR SHARE;
-</code>
-Behavior:
-  * Same transaction: can read
-  * Other transactions: can read
-  * No one can write
----
-==== 6.2 Write Lock (Exclusive Lock) ====
-Blocks both reads (in some cases) and writes.
-<code sql>
-SELECT * FROM users WHERE id = 1 FOR UPDATE;
-</code>
-Behavior:
-  * Same transaction: read/write
-  * Others: blocked
----
-==== 6.3 Table Locks ====
-<code sql>
-LOCK TABLES users READ;
-LOCK TABLES users WRITE;
-UNLOCK TABLES;
-</code>
-^ Lock Type ^ Same Session ^ Other Sessions ^
-| READ  | Read only | Read only |
-| WRITE | Read + Write | Blocked |
----
-===== 7. Deadlocks =====
-Deadlock happens when two transactions wait for each other.
-Example:
-Transaction A:
-<code sql>
-SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
-</code>
-Transaction B:
-<code sql>
-SELECT * FROM table2 WHERE id = 2 FOR UPDATE;
-</code>
-Then:
-  * A tries to lock table2
-  * B tries to lock table1
-=> Deadlock occurs
-MySQL will automatically rollback one transaction.
----
-===== 8. Best Practices =====
-  * Use **transactions** for critical operations
-  * Keep transactions **short**
-  * Use **FOR UPDATE** for critical updates
-  * Avoid **LOCK TABLES** unless necessary
-  * Handle **deadlock retry logic** in application
----
-===== 9. Real-world Example =====
-Prevent overselling:
-<code sql>
-START TRANSACTION;
-SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-UPDATE products SET stock = stock - 1 WHERE id = 1;
-COMMIT;
-</code>
----
-===== 10. Key Takeaways =====
-  * Transactions ensure **safe data operations**
-  * Locks control **concurrent access**
-  * Use **row-level locking (InnoDB)** instead of table locks
-  * Always **COMMIT or ROLLBACK**