MySQL InnoDB锁机制深度剖析（乐观锁、悲观锁、共享锁、排它锁、行锁、表 锁、死锁）

MySQL InnoDB锁机制深度剖析

1. 乐观锁 (Optimistic Locking)

乐观锁是一种并发控制方法，它假设多用户并发访问数据库时不会彼此影响，所以不会预先加锁。

实现方式：

1. 版本号机制
2. 时间戳机制
3. 状态机制

详细示例：

-- 创建表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT,
    version INT
);

-- 插入初始数据
INSERT INTO products VALUES (1, 'iPhone 12', 999.99, 100, 1);

-- 使用乐观锁更新商品价格
BEGIN;
-- 先查询当前版本
SELECT version FROM products WHERE id = 1;
-- 假设查询结果为1

-- 更新操作（在应用层执行）
UPDATE products 
SET price = 1099.99, version = version + 1
WHERE id = 1 AND version = 1;

-- 检查更新是否成功（在应用层执行）
SELECT ROW_COUNT(); -- 如果返回1，表示更新成功；如果是0，表示更新失败
COMMIT;

常见使用场景：

1. 商品库存管理
2. 并发编辑文档
3. 多用户投票系统

底层实现细节：

InnoDB在执行UPDATE语句时，会先获取该行的排他锁。然后检查WHERE条件，如果版本不匹配，则不更新数据并立即释放锁。这个过程是原子的，保证了并发安全。

2. 悲观锁 (Pessimistic Locking)

悲观锁假设并发操作会导致问题，因此在整个数据处理过程中，将数据锁定。

实现方式：

1. SELECT … FOR UPDATE
2. SELECT … LOCK IN SHARE MODE

详细示例：

-- 创建表
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    balance DECIMAL(10, 2)
);

-- 插入初始数据
INSERT INTO accounts VALUES (1, 'Alice', 1000.00), (2, 'Bob', 500.00);

-- 使用悲观锁进行转账操作
BEGIN;
-- 锁定付款人账户
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 锁定收款人账户
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;

-- 执行转账（假设转账100元）
UPDATE accounts SET balance = balance - 100