MySQL笔记 | 3.MySQL中涉及的锁

系列文章目录

提示：所有文章的目录
1.了解SQL的执行过程
2.Docker下搭建MySQL&查看BinLog文件

---

前言

无论何时，只要讨论到多个数据之间同一时间修改数据的问题，都要讨论如何控制并发。这时候锁的作用就给数据很足的安全感，当我们对一条数据，进行修改的时候，我们可以进行锁表，锁行，锁全局来确保数据的问题。

---

提示：以下是本篇文章正文内容

一、涉及到的锁

1. 全局锁

含义：给整个数据库加锁。
实现：实现全局加锁：Flush tables with read lock (FTWRL)

步骤一、请求获得相关类型的 MDL lock
步骤二、清空query cache中的内容
步骤三、FLUSHTABLES,将当前所有打开的table的fd关闭
步骤四、请求获得全局table-level lock
步骤五、上全局COMMIT锁(make_global_read_lock_block_commit)

影响：阻塞，数据的增删改以及表的修改。
场景：全库逻辑备份

问题：是否还有其他方式可以可是使表全局锁？
使用set global readonly=true。
相比与set global readonly=true使用FTWRL更好？因为在发生异常断开的时候，数据库会回到正常更新的状态。而readonly不会。

2. 表级锁

含义：给整张表加锁。有两种，一种是表锁（lock tables），另外一种是元数据锁（MDL）
实现：通过对数据块中相应的数据行加锁，通过索引条件检索数据，就是行级锁，否则就是InnoDB将使用表锁。
MDL的作用：保证读写的正确性

问题：如何安全的给小表加字段？

1. 预防长事务，解决长事务，长事务不提交就会一直占着锁，通过SELECT t.*, to_seconds(now()) - to_seconds(t.trx_started) idle_time FROM information_schema.innodb_trx t;
2. 在 alter table 语句里面设定等待时间，能拿到MDL锁最好。

3. 行级锁

含义：给一行的数据加锁。相同数据，只有一个事务提交，另外一个才能够提交。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
实现：InnoDB行锁是通过给索引上的索引项加锁来实现的

问题：Innodb中什么时候会上行锁？

1. 需要的时候加上，事务结束的时候释放。

二、造成死锁了怎么办？

1. 排查方式

整一套三路长拳排查问题。
-- 查询是否锁表
show OPEN TABLES where In_use > 0;
-- 查看下在锁的事务 
select * from information_schema.INNODB_TRX;
-- ：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查询进程
SHOW PROCESSLIST;
-- 杀死进程id
kill 199216;

2. 出现死锁后有什么策略？

1. 设置超时退出innodb_lock_wait_timeout，默认50s
2. 设置死锁检测，让其中一个事务回滚，设置innodb_deadlock_detect为on

3.问题：如果大量的数据都来执行同一个数据的场景？

在策略二下，是不是会发生一直有事务退出的问题？造成死锁检测的量级巨大，cpu占领巨大，事务执行没几个。
解决？

1. 场景下都是读操作，或者写操作不会互相影响，可以暂时关掉死锁检测。
2. 并发较大的时候，因为复杂度的原因，造成死锁检测的量级提高过多，那可以采用限制并发来减少。