MySQL锁机制三部曲：全局锁、表级锁、行级锁深度解析与实战

-- 查看当前连接的默认存储引擎
select @@default_storage_engine;
-- 确认是innodb，因为只有innodb支持行级锁

-- ========== 会话a（运维会话）==========
-- 1. 加全局读锁（flush tables with read lock）
flush tables with read lock;

-- 2. 查看当前锁状态
show processlist;
-- 可以看到状态：waiting for global read lock（被阻塞的会话）

-- 3. 查看锁定信息（mysql 8.0）
select * from performance_schema.metadata_locks 
where object_type = 'global' and lock_type = 'shared';

-- 4. 执行备份命令（命令行）
-- $ mysqldump -uroot -p --all-databases > full_backup.sql

-- 5. 确认备份完成后释放锁
unlock tables;

-- ========== 会话a ==========
flush tables with read lock;
-- query ok, 0 rows affected

-- ========== 会话b（业务会话）==========
-- 尝试插入数据
insert into test.users(name) values('张三');
-- 状态：waiting for global read lock（被阻塞）

-- ========== 会话c（监控会话）==========
-- 查看谁在等待全局锁
select 
    r.object_schema, r.object_name, 
    r.thread_id as waiting_thread,
    b.thread_id as blocking_thread,
    r.owner_event_id as waiting_event,
    b.owner_event_id as blocking_event
from performance_schema.metadata_locks r
join performance_schema.metadata_locks b on r.object_schema = b.object_schema 
    and r.object_name = b.object_name
where r.lock_status = 'pending' 
    and b.object_type = 'global' 
    and b.owner_thread_id != r.owner_thread_id;

-- 结果：显示会话b被会话a阻塞

-- 风险1：主库执行全局锁会导致业务停摆
-- 风险2：从库执行会导致主从复制延迟急剧增加

-- 现代替代方案：使用--single-transaction进行一致性备份（不锁库）
-- $ mysqldump -uroot -p --single-transaction --all-databases > backup.sql

-- 如果必须使用全局锁，建议设置超时（mysql 8.0）
set session lock_wait_timeout = 10; -- 10秒超时

-- ========== 会话a ==========
-- 1. 加表读锁（table read lock）
-- 本会话和其他会话都可以读，但都不能写
lock tables orders read;

-- 2. 加表写锁（table write lock）
-- 仅本会话可读写，其他会话完全阻塞
lock tables orders write; -- 会释放之前的read锁

-- 3. 查看当前表锁情况
show open tables where `table` = 'orders' and `database` = 'test';
-- in_use列显示1表示被锁定

-- 4. 释放锁
unlock tables;

-- 准备数据
create table if not exists inventory (
    id int primary key,
    product_name varchar(50),
    stock int
) engine=innodb;

insert into inventory values (1, 'iphone', 100), (2, 'macbook', 50);

-- ========== 会话a：加写锁 ==========
lock tables inventory write;
update inventory set stock = stock - 1 where id = 1;

-- ========== 会话b：尝试读 ==========
select * from inventory where id = 1;
-- 状态：waiting for table lock（被阻塞）

-- ========== 会话c：尝试写 ==========
update inventory set stock = stock - 1 where id = 2;
-- 状态：waiting for table lock（被阻塞）

-- 当会话a执行unlock tables后，会话b和c才能继续

-- ========== 会话a：长事务 ==========
begin;
select * from users where id = 1; -- 获取mdl读锁
-- 保持事务开启，不提交...

-- ========== 会话b：修改表结构 ==========
alter table users add column age int default 0;
-- 状态：waiting for table metadata lock
-- 原因：需要mdl写锁，但会话a持有mdl读锁

-- ========== 会话c：普通查询 ==========
select * from users where id = 2;
-- mysql 8.0以前：可能被阻塞（mdl读锁排队在mdl写锁后）
-- mysql 8.0：online ddl优化，通常能执行

-- ========== 会话d：诊断mdl锁等待 ==========
-- 查看mdl锁等待链
select 
    r.object_schema, r.object_name, 
    r.thread_id as waiting_thread, 
    r.owner_event_id as waiting_event,
    b.thread_id as blocking_thread,
    b.owner_event_id as blocking_event,
    r.lock_type as waiting_lock,
    b.lock_type as blocking_lock
from performance_schema.metadata_locks r
join performance_schema.metadata_locks b on r.object_schema = b.object_schema 
    and r.object_name = b.object_name
where r.lock_status = 'pending' 
    and b.lock_status = 'granted'
    and b.owner_thread_id != r.owner_thread_id;

-- 查看具体sql
select thread_id, sql_text 
from performance_schema.events_statements_current 
where thread_id in (select thread_id from performance_schema.metadata_locks 
                    where object_name = 'users' and lock_status = 'pending');

-- 解决方案：终止长事务
kill <blocking_thread_id>; -- 终止会话a

-- ========== 意向锁演示 ==========
-- 会话a：对某行加排他锁（自动在表上加ix意向锁）
begin;
select * from inventory where id = 1 for update;

-- 查看意向锁（mysql 8.0）
select 
    engine_transaction_id, object_name, 
    lock_type, lock_mode, lock_status
from performance_schema.data_locks
where object_type = 'table';
-- 结果：lock_mode = ix（意向排他锁）

-- 会话b：尝试加表锁（被阻塞）
lock tables inventory read;
-- 状态：table lock wait timeout...
-- 原因：表上有ix锁，与表级s锁不兼容

-- ========== 场景：读取并确保数据不被修改 ==========
-- 会话a：加共享锁（允许其他事务读，阻塞写）
begin;
select * from accounts where id = 1 lock in share mode;
-- mysql 8.0也可使用：for share

-- 会话b：可以加共享锁（兼容）
select * from accounts where id = 1 lock in share mode; -- ✅成功

-- 会话c：尝试修改（需要x锁，被阻塞）
update accounts set balance = balance - 100 where id = 1;
-- 状态：lock wait timeout exceeded

-- 会话d：查看行级锁等待
select 
    r.object_schema, r.object_name, 
    r.thread_id as waiting_thread,
    b.thread_id as blocking_thread,
    r.lock_mode as waiting_mode,
    b.lock_mode as blocking_mode
from performance_schema.data_locks r
join performance_schema.data_locks b 
    on r.object_schema = b.object_schema 
    and r.object_name = b.object_name
where r.lock_status = 'waiting' 
    and b.lock_status = 'granted';

-- ========== 场景：修改数据（自动加x锁）==========
-- 会话a：加排他锁
begin;
update accounts set balance = 900 where id = 1;
-- 自动对id=1的行加x锁

-- 或者显式加锁
select * from accounts where id = 1 for update;

-- 会话b：任何锁请求都被阻塞
select * from accounts where id = 1 for share; -- ❌等待

-- 会话c：快照读（snapshort read）可以执行，基于mvcc
select * from accounts where id = 1; -- ✅成功，读取undo log中的旧版本

-- 数据：id为主键，值为1, 3, 5, 7, 10
begin;
select * from accounts where id = 5 for update;
-- 仅锁定id=5这一行（记录锁）

-- 验证：其他事务可操作id=3和id=7
-- 但不能操作id=5

-- ========== 间隙锁演示 ==========
-- 会话a：范围查询加间隙锁（repeatable read隔离级别下）
begin;
select * from accounts where id > 3 and id < 8 for update;
-- 锁定间隙：(3,5) 和 (5,7)

-- 会话b：尝试插入（被阻塞）
insert into accounts (id, user_id, balance) values (4, 1004, 400);
-- ❌ waiting for lock：id=4落在间隙(3,5)内

insert into accounts (id, user_id, balance) values (6, 1006, 600);
-- ❌ waiting for lock：id=6落在间隙(5,7)内

insert into accounts (id, user_id, balance) values (2, 1002, 200);
-- ✅ 成功：id=2不在锁定范围

-- ========== 临键锁演示 ==========
-- 数据：id ∈ {1, 3, 5, 7, 10}
begin;
select * from accounts where id = 5 for update;
-- 临键锁锁定：(3, 5] （前一个间隙到当前记录）

-- 其他事务：
update accounts set balance = 500 where id = 5; -- ❌被阻塞（记录锁）
insert into accounts (id, user_id, balance) values (4, 1004, 400); -- ❌被阻塞（间隙锁，4在(3,5)内）

-- 但如果：
insert into accounts (id, user_id, balance) values (6, 1006, 600); -- ✅可能成功（看具体索引结构）

-- 会话a：a转给b 100元
begin;
update accounts set balance = balance - 100 where id = 1; -- 锁id=1
update accounts set balance = balance + 100 where id = 2; -- 锁id=2
commit;

-- 会话b：b转给a 50元（同时进行）
begin;
update accounts set balance = balance - 50 where id = 2;  -- 锁id=2
update accounts set balance = balance + 50 where id = 1;  -- ❌死锁！等待id=1
-- mysql会检测到死锁，回滚其中一个事务（通常是修改行数少的）

-- 都按id从小到大排序
begin;
-- 先处理id小的
update accounts set balance = balance - 100 where id = 1;
update accounts set balance = balance + 100 where id = 2;
commit;

-- 表结构添加version字段
alter table accounts add column version int default 0;

-- 会话a：读取版本号
begin;
select balance, version from accounts where id = 1;
-- 结果：balance=1000, version=1

-- 计算新余额并更新（带版本号检查）
update accounts 
set balance = 900, version = version + 1 
where id = 1 and version = 1;
-- 如果影响行数=1，成功；=0，说明数据被其他事务修改

commit;

-- name字段无索引
begin;
select * from accounts where name = 'alice' for update;
-- ❌危险！innodb无法通过索引定位，会扫描全表，对所有行加x锁！
-- 等同于表锁，并发归0

-- 查看实际加锁情况（mysql 8.0）
select 
    count(*) as locked_rows,
    object_name,
    lock_mode
from performance_schema.data_locks
where engine_transaction_id = (select trx_id from information_schema.innodb_trx 
                               where trx_mysql_thread_id = connection_id())
group by object_name, lock_mode;
-- 结果：locked_rows可能等于全表总行数！

-- mysql的锁会按需升级，但通常不建议：

-- 1. 行锁升级为表锁（当没有索引时）
-- 自动发生，危险！

-- 2. 意向锁协调
-- 自动发生，无害

-- 3. 手动调整锁策略（myisam场景，不推荐）
set session transaction isolation level read uncommitted; -- 降低锁粒度
-- 或
lock tables t1 write, t2 read; -- 手动控制表锁

-- 查看所有锁（包括持有和等待）
select 
    dl.engine_transaction_id as trx_id,
    dl.object_schema, 
    dl.object_name as table_name,
    dl.index_name,
    dl.lock_type,  -- table or record
    dl.lock_mode,  -- s, x, is, ix, gap, next-key等
    dl.lock_status, -- granted or waiting
    dl.lock_data,  -- 锁定的具体值（如主键值）
    t.trx_mysql_thread_id as thread_id,
    t.trx_query
from performance_schema.data_locks dl
join information_schema.innodb_trx t 
    on dl.engine_transaction_id = t.trx_id
order by dl.engine_transaction_id, dl.object_name;

-- 谁阻塞了谁？
select 
    w.trx_id as waiting_trx_id,
    w.trx_mysql_thread_id as waiting_thread,
    w.trx_query as waiting_query,
    b.trx_id as blocking_trx_id,
    b.trx_mysql_thread_id as blocking_thread,
    b.trx_query as blocking_query,
    timestampdiff(second, w.trx_wait_started, now()) as wait_seconds
from information_schema.innodb_trx w
join performance_schema.data_lock_waits lw 
    on w.trx_id = lw.requesting_engine_transaction_id
join information_schema.innodb_trx b 
    on b.trx_id = lw.blocking_engine_transaction_id
order by wait_seconds desc;

-- 查看最近一次死锁信息
show engine innodb status\g
-- 关注：
-- - latest detected deadlock部分
-- - transaction部分显示持有的锁
-- - waiting for部分显示等待的锁

-- 开启死锁日志持久化
set global innodb_print_all_deadlocks = on;
-- 死锁信息会记录到error log，便于事后分析

-- 查找持有锁时间最长的事务（危险！）
select 
    trx_id,
    trx_mysql_thread_id,
    trx_state,
    timestampdiff(second, trx_started, now()) as trx_seconds,
    trx_tables_locked,
    trx_rows_locked,
    left(trx_query, 100) as query_preview
from information_schema.innodb_trx
order by trx_seconds desc
limit 5;

-- 终止危险事务
kill <trx_mysql_thread_id>;

-- 1. 优先使用行级锁（确保innodb引擎和索引）
create table transactions (
    id int primary key auto_increment,
    user_id int,
    amount decimal(10,2),
    index idx_user_id (user_id)  -- 关键：加索引！
) engine=innodb;

-- 2. 小事务原则：快速提交，减少锁持有时间
begin;
update accounts set balance = balance - 100 where id = 1;
-- 不要在这里做复杂计算或调用外部api
commit;

-- 3. 按固定顺序访问资源（避免死锁）
-- 所有事务都按id从小到大更新

-- 4. 使用乐观锁处理低冲突场景
update products set stock = stock - 1, version = version + 1 
where id = 1 and version = 5;

-- 1. 不要在长事务中持有行锁
begin;
select * from accounts where id = 1 for update;
-- 等待用户输入...（错误！）
commit;

-- 2. 避免无索引的查询（会锁全表）
select * from accounts where create_time > '2023-01-01' for update;
-- 如果create_time无索引，将锁定全表！

-- 3. 谨慎使用显式表锁（除非myisam）
lock tables accounts write;
-- 这会阻塞所有其他会话的访问，即使是简单的select

-- 4. 避免在高并发时执行全局锁
flush tables with read lock; -- 生产环境慎用！

-- 锁等待超时时间（默认50秒）
show variables like 'innodb_lock_wait_timeout';

-- 是否开启死锁检测（默认on，不建议关闭）
show variables like 'innodb_deadlock_detect';

-- 事务隔离级别
show variables like 'transaction_isolation';