MySQL locks and transactions-prevent data corruption

Database locks prevent data corruption when multiple queries access the same row simultaneously. InnoDB uses row-level locking for fine-grained control. MyISAM uses table-level locking which is slower under concurrency.

Types of locks

Row-level locks (InnoDB)

• Read lock (shared): Multiple queries can read simultaneously
• Write lock (exclusive): Only one query can write; blocks reads

Table-level locks (MyISAM)

• Read lock: Blocks all writes to the table
• Write lock: Blocks all reads and writes

Deadlocks

Deadlock: Transaction A waits for Transaction B, and B waits for A. InnoDB detects and rolls back the youngest transaction.

Example:

Transaction A: UPDATE users SET balance = balance - 100 WHERE id = 1;
Transaction B: UPDATE users SET balance = balance - 100 WHERE id = 2;

# If both try to read before updating:
# A locks row 1, tries to read row 2 (blocked by B)
# B locks row 2, tries to read row 1 (blocked by A)
# DEADLOCK!

Check for locks

SHOW OPEN TABLES WHERE In_use > 0;  # Tables with locks
SHOW ENGINE INNODB STATUS\G  # Detailed lock info

ACID transactions

START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE id = 123;
UPDATE inventory SET qty = qty - 1 WHERE product_id = 456;
COMMIT;  # Both succeed or both fail

# If error:
ROLLBACK;  # Undo all changes

Related: InnoDB engine | Slow queries

Related articles