Lock Fundamentals

Core Concepts

Lock mechanisms coordinate concurrent access to shared resources across multiple sessions and threads. These systems integrate closely with MySQL's key components: indexing, locking, and transactions. This analysis focuses on MySQL 5.6 scenarios to demonstrate typical locking behaviors.

Common MySQL lock types include:

• Shared read locks
• Exclusive write locks
• Table-level locks
• Row-level locks
• Gap locks

Storage Engines and Lock Support

• MyISAM Engine: Implements table-level locking with read/write modes
• InnoDB Engine: Provides row-level read/write locks, gap locks across rows, and also supports table-level locking

Lock Management Commands

LOCK TABLE table_name [READ|WRITE]; -- Acquire table lock
UNLOCK TABLES; -- Release all table locks

MyISAM Lock Implementation

Lock Behavior Characteristics

MyISAM implements two table-level lock modes: shared read locks (Read-Lock) and exclusive write locks (Write-Lock). Read operations don't block other reads but prevent writes. Write operations block both reads and writes from other threads. All operation become serialized during write lock acquisition.

Practical Validation

Sample Table Structures

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20),
  `phone` varchar(20),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `user_profiles` (
  `user_id` int(11) NOT NULL,
  `city` varchar(20),
  `country` varchar(20),
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM;

Shared Read Lock Demonstration

Session A

-- Acquire read lock
LOCK TABLE users READ;

-- Query succeeds
SELECT * FROM users;

-- Insert fails
INSERT INTO users (username, phone) VALUES ('test', '123456789');

-- Cross-table query fails
SELECT * FROM user_profiles;

-- Release lock
UNLOCK TABLES;

Session B

-- Query succeeds
SELECT * FROM users;

-- Insert blocked until lock release
INSERT INTO users (username, phone) VALUES ('test', '123456789');

-- Operations succeed after lock release
INSERT INTO users (username, phone) VALUES ('test', '123456789');
SELECT * FROM users;

Exclusive Write Lock Verification

Exclusive write locks prevent all concurrent access to locked tables.

Monitoring Lock Contention

SHOW STATUS LIKE 'table%';

Higher Table_locks_waited values indicate severe contention and reduced performance.

Concurrent Insert Handling

MyISAM supports configurable concurrent inserts through concurrent_insert parameter:

• concurrent_insert=0: Disables concurent inserts
• concurrent_insert=1: Allows inserts at table end when no gaps exist in data
• concurrent_insert=2: Permits concurrent inserts regardless of data gaps

With READ LOCAL locks, queries proceed while inserts continue concurrently.

Lock Priority Configuration

By default, write operations receive priority over reads. This behavior can be modified:

SET LOW_PRIORITY_UPDATES=1; -- Lower current session write priority
-- Or use LOW_PRIORITY attribute in DML statements

Practical Applications

Table locking ensures data consistency during critical operations like inventory reconciliation where simultaneous access to related tables must be prevented.

InnoDB Lock Implementation

Transaction Principles

ACID Properties

• Atomicity: All operations succeed or fail together
• Consistency: Data integrity maintained before and after transactions
• Isolation: Transactions don't interfere with each other
• Durability: Committed changes persist permanently

Concurrency Issues

• Dirty Reads: Reading uncommitted data from other transactions
• Non-repeatable Reads: Inconsistent data during repeated reads due to updates
• Phantom Reads: Different result sets during repeated queries due to inserts

Isolation Levels

• READ UNCOMMITTED: Allows reading uncommitted data
• READ COMMITTED: Only reads committed data
• REPEATABLE READ: Prevents dirty and non-repeatable reads
• SERIALIZABLE: Highest isolation preventing all concurrency issues

Lock Types

Shared Locks (S-Locks)

Allow reading but prevent modifications. Multiple shared locks can coexist.

Exclusive Locks (X-Locks)

Permit updates and block all other lock types.

Implementation Examples

Table Definitions

CREATE TABLE `unindexed_users` (
  `id` int(11),
  `username` varchar(20),
  `phone` varchar(20)
) ENGINE=InnoDB;

CREATE TABLE `indexed_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20),
  `phone` varchar(20),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Unindexed Table Behavior

Session A

SET AUTOCOMMIT = 0;
SELECT * FROM unindexed_users WHERE id=1;
-- Following FOR UPDATE fails due to missing index
SELECT * FROM unindexed_users WHERE id=1 FOR UPDATE;
SET AUTOCOMMIT = 1;

Session B

SET AUTOCOMMIT = 0;
SELECT * FROM unindexed_users WHERE id=2;
-- Insertion waits for lock
INSERT INTO unindexed_users (id, username, phone) VALUES (3, 'test', '123456789');
-- Lock request waits
SELECT * FROM unindexed_users WHERE id=2 FOR UPDATE;
SET AUTOCOMMIT = 1;

Indexed Table Behavior

Session A

SET AUTOCOMMIT = 0;
SELECT * FROM indexed_users WHERE id=1;
-- Successful lock acquisition
SELECT * FROM indexed_users WHERE id=1 FOR UPDATE;
SET AUTOCOMMIT = 1;

Session B

SET AUTOCOMMIT = 0;
SELECT * FROM indexed_users WHERE id=2;
SELECT * FROM indexed_users WHERE id=1; -- Read lock succeeds
INSERT INTO indexed_users (username, phone) VALUES ('test', '123456789');
-- Lock succeeds for different row
SELECT * FROM indexed_users WHERE id=2 FOR UPDATE;
-- Lock fails for same row
SELECT * FROM indexed_users WHERE id=1 FOR UPDATE;
SET AUTOCOMMIT = 1;

Index Usage Monitoring

EXPLAIN SELECT * FROM indexed_users WHERE id=1;

Lock Performance Metrics

SHOW STATUS LIKE 'innodb_row_lock%';

High Innodb_row_lock_waits and Innodb_row_lock_time_avg values indicate performance bottlenecks.

Next-Key Locking Algorithm

Concept Overview

Next-Key locking combines record locks and gap locks to prevent phantom reads:

Next-Key Lock = Record Lock + Gap Lock

Gap Lock Validation

CREATE TABLE `gap_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idx_value` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_value` (`idx_value`)
) ENGINE=InnoDB;

INSERT INTO `gap_test` VALUES (1,2), (3,4), (6,7), (8,7), (9,9);

Session A

START TRANSACTION;
-- Locks records where idx_value=7
SELECT * FROM gap_test WHERE idx_value=7 FOR UPDATE;
COMMIT;

Session B

START TRANSACTION;
-- Blocked - within gap range
INSERT INTO gap_test VALUES (4, 6);
INSERT INTO gap_test VALUES (4, 4);
-- Success - outside gap range
INSERT INTO gap_test VALUES (4, 3);
-- Blocked - within gap range
INSERT INTO gap_test VALUES (7, 9);
-- Success - outside gap range
INSERT INTO gap_test VALUES (7, 10);

Gaps exist between values 4-7 and 7-9, creating lock range [4,9].

Deadlock Handling

Definition

Circular resource dependencies occur when multiple transactions hold locks while requesting resources held by others.

Deadlock Example

Session A

START TRANSACTION;
SELECT * FROM gap_test WHERE id=6 FOR UPDATE;
-- Waits for Session B resource
SELECT * FROM gap_test WHERE id=9 FOR UPDATE;

Session B

START TRANSACTION;
SELECT * FROM gap_test WHERE id=9 FOR UPDATE;
-- Creates deadlock condition
SELECT * FROM gap_test WHERE id=6 FOR UPDATE;

InnoDB automatically detects deadlocks and terminates one transaction to resolve the cycle. Minimizing lock scope and duration helps prevent deadlocks in production environments.