Locks coordinate concurrent thread access to shared resources. In relational databases, they prevent data corruption during simultaneous reads and writes. MySQL delegates lock implementation to storage engines, resulting in distinct behaviors. MyISAM and MEMORY rely exclusively on table-level locking. InnoDB supports both table and row-level locking, defaulting to row-level granularity for DML operations.

Table locks impose minimal overhead and acquire quickly but serialize access across the entire table, drastically reducing concurrency. Row locks increase acquisision cost and introduce deadlock potential but maximize parallel throughput by isolating conflicts to specific records. Selection depends on workload patterns: table locks suit read-heavy, index-driven batch updates, while row locks excel in high-concurrency OLTP environments with frequent, targeted modifications.

MyISAM Table Locking Behavior

MyISAM implements two primary lock modes: Table Read Locks and Table Write Locks. Read operations are non-blocking for other readers but block writers. Write operations are exclusive, blocking all other reads and writes. Consequently, MyISAM serializes read-write and write-write interactions.

Schema Setup:

CREATE TABLE inventory_log (
  record_id INT NOT NULL AUTO_INCREMENT,
  item_code VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (record_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

INSERT INTO inventory_log (item_code) VALUES ('SKU-001'), ('SKU-002'), ('SKU-003');

Write Lock Blocking Reads: When a session acquires a write lock, it holds exclusive modification rights. All external read/write requests queue until release.

Read Lock Blocking Writes: A read lock permits concurrent selects but rejects modifications from any session, including the lock holder. Accessing unlocked tables within the same session also triggers errors.

Concurrent Inserts with READ LOCAL: MyISAM supports a specialized READ LOCAL lock that permits concurrent inserts at the table's end while blocking updates and deletes. Monitoring contention involves querying server status variables:

SHOW GLOBAL STATUS LIKE 'Table_locks%';
-- Table_locks_immediate: Successful immediate acquisitions
-- Table_locks_waited: Requests that required waiting

High Table_locks_waited ratios indicate severe table-level bottlenecks.

Transactional Integrity and Isolation

InnoDB relies on ACID-compliant transactions. Concurrency introduces three primary read anomalies:

• Dirty Read: Reading uncommitted modifications from another transaction.
• Non-Repeatable Read: Fetching the same row twice yields different values due to intervening commits.
• Phantom Read: Re-executing a range query returns a different row count because another transaction inserted or deleted matching records.

Isolation levels mitigate these anomaleis at the cost of throughput:

Row lock contention metrics are available via:

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
-- Key metrics: Innodb_row_lock_waits, Innodb_row_lock_time_avg

InnoDB Row Lock Modes and Acquisition

InnoDB utilizes Shared (S) and Exclusive (X) locks. S locks allow concurrent reads but block writes. X locks grant sole read/write access to the holder. Standard SELECT statements perform consistent reads without locking. Explicit locking requires:

• SELECT ... FOR UPDATE (Acquires X lock)
• SELECT ... LOCK IN SHARE MODE (Acquires S lock)

DML statements (INSERT, UPDATE, DELETE) automatically apply X locks to affected rows.

Index Dependency and Lock Granularity

InnoDB attaches row locks to index entries, not physical data pages. If a query lacks an applicable index, InnoDB escalates to a full table lock, negating row-level concurrency benefits.

Scenario A: Missing Index Triggers Table Lock

CREATE TABLE unindexed_records (entry_id INT, payload VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO unindexed_records VALUES (10, 'alpha'), (20, 'beta'), (30, 'gamma');

Despite targeting different rows, the absence of an index forces a table-level X lock, causing Session B to wait.

Scenario B: Indexed Query Enables Row Locking

CREATE TABLE indexed_records (entry_id INT, payload VARCHAR(20)) ENGINE=InnoDB;
CREATE INDEX idx_entry ON indexed_records(entry_id);
INSERT INTO indexed_records VALUES (10, 'alpha'), (20, 'beta'), (30, 'gamma');

The index allows precise row-level locking, enabling parallel execution.

Scenario C: Index Key Collision Since locks bind to endex keys, duplicate index values can cause unexpected blocking even when filtering by non-indexed columns.

INSERT INTO indexed_records VALUES (10, 'delta'); -- Duplicate entry_id

Both sessions target the same index key (entry_id = 10). The lock applies to the index entry, causing contention despite different payload values.

Engineering Guidelines for Lock Optimization

• Prefer lower isolation levels when business logic tolerates minor anomalies to reduce lock overhead.
• Design queries to leverage indexes consistently. Full table scans trigger table locks in InnoDB.
• Keep transactions short and focused. Prolonged transactions hold locks longer, increasing conflict probability.
• Request the highest necessary lock level immediately. Upgrading from S to X locks within a transaction frequently causes deadlocks.
• Standardize table access order across application modules. Consistent ordering prevents circular wait conditions.
• Use equality conditions (=) over range queries (BETWEEN, >) where possible to minimize gap lock interference with concurrent inserts.
• Avoid explicit locking (FOR UPDATE) unless strict serialization is mandatory. Rely on MVCC for standard reads.
• Consider explicit table locks for bulk maintenance operations to bypass row-lock overhead and prevent deadlocks during massive batch processing.