锁
锁就是协调多个用户或者客户端并发访问某一资源的机制，保证数据并发访问时的一致性和有效性。
全局锁
MySQL 全局锁会关闭所有打开的表，并使用全局读锁锁定所有表。
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
当执行 FTWRL 后，所有的表都变成只读状态，数据更新或者字段更新将会被阻塞。
场景
一般用在整个库（包含非事务引擎表）做备份（mysqldump 或者 xtrabackup）时。
mysqldump 包含一个参数 --single-transaction，可以在一个事务中创建一致性快照，然后进行所有表的备份。因此增加这个参数的情况下，备份期间可以进行数据修改。但是需要所有表都是事务引擎表。所以建议使用InnoDB 存储引擎。
表级锁
表级锁有两种：表锁和元数据锁。
表锁
场景
- 事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁，不仅事务执行效率低，而且可能造成其它事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务执行速度；
- 事务涉及多个表，比较复杂，可能会引起死锁，导致大量事务回滚，可以考虑表锁避免死锁。
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锁
死锁
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
解决方法
- 检测到死锁的循环依赖，立即返回一个错误，将参数 innodb_deadlock_detect设置为 on 表示开启这个逻辑；
- 等查询的时间达到锁等待超时的设定后放弃锁请求。这个超时时间由 innodb_lock_wait_timeout 来控制。默认是50 秒。
方案1有额外的CPU检测开销，确保无死锁时建议关闭检测。
降低死锁概率
- 更新 SQL 的 where 条件尽量用索引；
- 基于 primary 或 unique key 更新数据；
- 减少范围更新，尤其非主键、非唯一索引上的范围更新；
- 加锁顺序一致，尽可能一次性锁定所有需要行；
- 将 RR 隔离级别调整为 RC 隔离级别。
分析死锁
show engine innodb status\G; //查看最后一次死锁信息
另外设置 innodb_print_all_deadlocks = on 可以在 err log 中记录全部死锁信息。
posted on 2021-03-14 19:08 hainingwyx 阅读(59) 评论(0) 编辑 收藏 举报
