锁

目录

• 全局锁
• 表级锁
  • 表锁
  • 元数据锁
• 行锁
  • 两阶段锁
  • 行锁
  • 行锁算法
  • 事务隔离级别
    • 非索引字段查询（RC）
    • 唯一索引查询（RC）
    • 非唯一索引查询（RC）
    • 非索引字段查询（RR）
    • 唯一索引查询（RR）
    • 非唯一索引查询(RR)
• 死锁

锁就是协调多个用户或者客户端并发访问某一资源的机制，保证数据并发访问时的一致性和有效性。

全局锁

MySQL 全局锁会关闭所有打开的表，并使用全局读锁锁定所有表。

FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;

当执行 FTWRL 后，所有的表都变成只读状态，数据更新或者字段更新将会被阻塞。

场景

一般用在整个库（包含非事务引擎表）做备份（mysqldump 或者 xtrabackup）时。

mysqldump 包含一个参数 --single-transaction，可以在一个事务中创建一致性快照，然后进行所有表的备份。因此增加这个参数的情况下，备份期间可以进行数据修改。但是需要所有表都是事务引擎表。所以建议使用InnoDB 存储引擎。

表级锁

表级锁有两种：表锁和元数据锁。

表锁

场景

1. 事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁，不仅事务执行效率低，而且可能造成其它事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务执行速度；
2. 事务涉及多个表，比较复杂，可能会引起死锁，导致大量事务回滚，可以考虑表锁避免死锁。

lock tables t14 read;
lock tables t14 write;

表读锁本线程和其它线程可以读，本线程写会报错，其它线程写会等待。

元数据锁

MDL 锁的出现解决了同一张表上事务和 DDL 并行执行时可能导致数据不一致的问题。

对开发而言尽量避免慢查询，事务要及时提交，避免大事务

对于 DBA 来说，也应该尽量避免在业务高峰执行 DDL 操作。

行锁

InnoDB后来居上：

• InnoDB 支持事务：适合在并发条件下要求数据一致的场景。
• InnoDB 支持行锁：有效降低由于删除或者更新导致的锁定。

两阶段锁

锁操作分为两个阶段，加锁阶段和解锁阶段，并且保证加锁阶段和解锁阶段不相交。

行锁

两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁；
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁。

共享锁（S）：select * from table_name where … lock in share mode;
排他锁（X）：select * from table_name where … for update(当前读)。

行锁算法

Record Lock：单个记录上的索引加锁。
Gap Lock：间隙锁，对索引项之间的间隙加锁，但不包括记录本身。
Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

事务隔离级别

Read uncommitted（读未提交）: 在该隔离级别，所有事务都可以看到其它未提交事务的执行结果。可能会出现
脏读。
Read Committed（读已提交，简称： RC）：一个事务只能看见已经提交事务所做的改变。因为同一事务的其它
实例在该实例处理期间可能会有新的 commit，所以可能出现幻读。
Repeatable Read（可重复读，简称：RR）：这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在
并发读取数据时，会看到同样的数据行。消除了脏读、不可重复读，默认也不会出现幻读。
Serializable（串行）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

脏读：读取未提交的事务。
幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。

非索引字段查询（RC）

如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由 server 层进行过滤。

唯一索引查询（RC）

如果查询的条件是唯一索引，那么 SQL 需要在满足条件的唯一索引上加锁，并且会在对应的聚簇索引上加锁。

非唯一索引查询（RC）

如果查询的条件是非唯一索引，那么 SQL 需要在满足条件的非唯一索引上都加上锁，并且会在它们对应的聚簇索引上加锁。

非索引字段查询（RR）

RR 隔离级别下，非索引字段做条件的当前读不但会把每条记录都加上 X 锁，还会把每个 GAP 加上GAP 锁。（条件字段加索引的重要性！）

唯一索引查询（RR）

如果能确保索引字段唯一，那其实一个等值查询，最多就返回一条记录，而且相同索引记录的值，一定不会再新增，因此不会出现 GAP 锁。

以唯一索引为条件的当前读，不会有 GAP 锁。

非唯一索引查询(RR)

新增GAP锁+对应数据的X锁

死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

解决方法

1. 检测到死锁的循环依赖，立即返回一个错误，将参数 innodb_deadlock_detect设置为 on 表示开启这个逻辑；
2. 等查询的时间达到锁等待超时的设定后放弃锁请求。这个超时时间由 innodb_lock_wait_timeout 来控制。默认是50 秒。

方案1有额外的CPU检测开销，确保无死锁时建议关闭检测。

降低死锁概率

1. 更新 SQL 的 where 条件尽量用索引；
2. 基于 primary 或 unique key 更新数据；
3. 减少范围更新，尤其非主键、非唯一索引上的范围更新；
4. 加锁顺序一致，尽可能一次性锁定所有需要行；
5. 将 RR 隔离级别调整为 RC 隔离级别。

分析死锁

show engine innodb statusG; //查看最后一次死锁信息

另外设置 innodb_print_all_deadlocks = on 可以在 err log 中记录全部死锁信息。