MySQL总结-锁

目录

目标

锁的实现方式

价值与缺点

锁种类

悲观锁与乐观锁

隐式加锁与显示加锁

阻塞与死锁

---

目标

对mysql的学习总结，我会从执行引擎、索引、sql、锁、MVCC、事务等几个部分进行阐述。本节阐述锁，包括锁的种类、使用建议。

锁的实现方式

针对InnerDB，在聚族索引的叶子节点上加记录锁，即为索引锁；如果update涉及到二级索引，则二级索引也要加锁。

价值与缺点

锁的作用就是保持数据的一致性，但是由于锁等待，必然会降低并发能力／吞吐量，增加rt等。

事务A在记录R上加锁，事务B也想操作记录R，只有A和B的锁兼容时，B才可操作记录R，否则需要等待A释放锁。

锁种类

table-level

intention意向锁

    在binlog多条记录上加锁
    意向共享锁 IS lock
    意向排他锁 IX lock

auto-inc锁

    如果一个表有auto-increment列，则一个事务插入数据时，其他事务必须等待。
    思考：对于插入数据操作很频繁的表，不要设置auto-increment的列，否则会严重影响插入的性能。

 

row-level

share共享和exclusive排他锁

    在binlog单行上加锁
    S Lock共享锁
    X Lock排他锁

 

index record-level

record锁

在索引记录上加锁，阻止其他事务对这些记录进行DML操作

gap间隙锁

索引记录之间的范围 或 第一个索引记录之前 或 最后一个索引记录之后，对这样的gap加锁，“防止其他事务在gap间插入数据”。

哪些sql会引起间隙锁

select …… where …… for update
update …… where ……
delete …… where ……

where条件影响是否使用间隙锁

命中"唯一索引的全部列"，即仅匹配到一个index record，不会使用gap lock
命中唯一索引部分列，无论匹配到index record的数量，都会使用gap lock
即使不用索引或没有命中唯一索引，仍然会使用gap lock

不同事务隔离级别的差异

重复读
    锁定gap，防止在gap之间插入数据

读提交
    仅用于外键约束检查和重复key检查

如何让其失效

    读提交事务隔离级别
    innodb_locks_unsafe_for_binlog=true

事务间在相同gap上分别加gap lock是不会冲突的，因为加gap lock都是为了防止其他事务在gap间插入数据。

next-key锁

insert意向锁

通过select for update方式加的gap lock，允许多个事务在gap lock区间内插入数据，而不需要等待gap lock被释放，但是要求彼此插入数据的位置不重复，也就不能对应同一个索引记录位置。

适用场景
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
该事务在id>100的范围上加了gap lock

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
另一个事务插入id=101的记录

哪些sql使用当前锁

sql

insert into table value *
update table set xxx where *
delete table where *
select for update
select * from table lock in share mode

可能使用的锁

record lock
gap lock
insert intention lock
inc lock insert时有自动递增的字段

原理

1:当前读数据记录
2:获取数据记录并加锁
3:修改数据
4:提交并释放锁

 

悲观锁与乐观锁

悲观锁

具有排他性、必须等待 等特性的锁，都可认为是悲观锁。
认为获取锁很可能失败，变现为必须先获取锁，然后再执行数据修改，依赖数据库支持。

乐观锁

认为获取锁很可能成功，表现为先执行数据修改，然后获取锁进行操作，一般是逻辑性锁，不需要数据库支持。
如更新时对比版本号、时间戳等。

 

隐式加锁与显示加锁

为了较少锁数量，节省cpu资源，采用隐式和显式加锁的方式

隐示锁

事务A要对记录R加锁，做法为：没有真正的创建锁，而是将事务ID存在索引叶子节点中。

显式锁

事务B要对记录R加锁，发现已经记录了其它事务ID，做法为：使用已记录的事务对记录R加锁，事务B等待，即隐式锁变为显式锁。

 

阻塞与死锁

阻塞

概念

        事务在申请加锁时，必须等待其它事务释放锁，表现为等待，导致sql rt增加。

排查方式1

        查看事务范围内的操作是否必须在事务中执行，要求事务范围尽可能短小。
        反例：事务中存在IO等待或网络等待或性能差的select语句等。

排查方式2

        DML语句走聚族索引还是二级索引，若时二级索引，会导致二级索引和聚族索引都添加行锁。
        使用二级索引是否符合ICP要求，因为使用ICP，会在向mysql server返回记录前，释放被ICP filter过滤掉的记录锁。

死锁

概念

        两个或多个事务之间发生互相阻塞的现象。

mysql会自动kill掉较小的事务，使较大的事务可以继续运行。

死锁场景

        多个表的情况：不同事务以相反的顺序， 对多个表进行加锁

        同一个表同一个索引的情况：不同事务以相反的顺序，对多条记录加锁

        同一个表多个索引的情况：执行DML的不同事务以相反的顺序，对多个索引加锁。
            如事务A先锁聚族索引，然后更新二级索引，需要加锁二级索引；事务B先锁二级索引，然后锁聚族索引。

        同一个表，多个事务的DML命中不同的二级索引，导致以不同的顺序通过聚族索引对多条记录加锁

总结

原因

包括加锁顺序和等待时间。

解决入手

业务代码逻辑和索引问题。
索引太多，发生问题概率越大。
一定注意事务范围应该尽可能小，减少加锁时间。