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

—

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

—

START TRANSACTION;
 
-- SQL statements
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
COMMIT;

Rollback example:

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 
ROLLBACK;

—

Reading uncommitted data from another transaction

Same query returns different results within a transaction

New rows appear in repeated queries

—

Set isolation level:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

—

Locks control access to data during transactions.

Allows multiple reads, blocks writes.

SELECT * FROM users WHERE id = 1 FOR SHARE;

Behavior:

• Same transaction: can read
• Other transactions: can read
• No one can write

—

Blocks both reads (in some cases) and writes.

SELECT * FROM users WHERE id = 1 FOR UPDATE;

Behavior:

• Same transaction: read/write
• Others: blocked

—

LOCK TABLES users READ;
LOCK TABLES users WRITE;
UNLOCK TABLES;

—

Deadlock happens when two transactions wait for each other.

Example:

Transaction A:

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

Transaction B:

SELECT * FROM table2 WHERE id = 2 FOR UPDATE;

Then:

• A tries to lock table2
• B tries to lock table1

⇒ Deadlock occurs

MySQL will automatically rollback one transaction.

—

• 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

—

Prevent overselling:

START TRANSACTION;
 
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
 
UPDATE products SET stock = stock - 1 WHERE id = 1;
 
COMMIT;

—

• Transactions ensure safe data operations
• Locks control concurrent access
• Use row-level locking (InnoDB) instead of table locks
• Always COMMIT or ROLLBACK