kancloud mysql内核_锁 · Mysql · 看云

[TOC]

### 什么是锁

锁是数据库系统区别于文件系统的关键特性。锁机制用于管理对共享资源的并发访问。

### lock 与 latch

latch 一般称为闩锁(轻量级的锁)，因为其要求锁定的世界必须非常短。若持续的时间长，则应用性能会非常差。分为mutex(互斥量)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

lock 的对象是事务,用来锁定的是数据库中的对象,如表、页、行。并且一般的对象尽在事务commit 或者rollback后释放。有死锁机制的。


对于latch 可以通过`show engine innodb mutex`来进行查看



### Innodb 存储引擎中的锁

(1)共享锁(s),允许事务读一行数据。

(2)排它锁(x)事务删除或者更新一行数据。


意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁。

在innodb 1.0版本之前，用户只能通过命令show full processlist，show engine innodb status等来查看当前数据库中锁的请求，然后在判断事务锁的情况。 1.0版本之后 在INFORMATION_SCHEMA 架构下添加了表INNODB_TRX、INNODB_LOCK、INNODB_LOCK_WAITS。这三张表分析锁问题。


如上图可以通过下面的命令,获取锁的信息:

`select * from information_schema.innodb_trx\G;`


输入`select * from information_schema.innodb_locks`;

可以获取如上图的相关信息

InnoDB存储引擎对于select语句支持两种一致性的锁定读(locking read)操作:

(1)`select ... for update`(排它锁 X)其他事务不能对已锁定的行加上任何锁。

(2)`select ... lock in share mode`(共享锁) 其他事务可以向被锁定的行加s锁，但是如果加x锁就会别阻塞。

#### 自增长及锁

mysql5.1.22版本开始 innodb存储引擎提供了`innodb_autoinc_lock_mode`来控制自增长的模式 默认值为1。


innodb：必须是索引的第一列 myisam不需要。

#### 外键和锁

innodb 使用外键如果没有加索引 搜索引擎会自动将其添加索引。因为这样就可以避免表锁。

### 锁的算法

#### 行锁的3中算法

(1)Record Lock：单行记录上的锁 锁住的是索引记录 如果没有任何一个索引 那么使用隐式的主键来进行锁定。

(2)Gap Lock：间隙锁，锁定一个范围，但不包含记录本身

(3)Next-key Lock ： Gap Lock + Record Lock,锁定一个范围,并锁定记录本身。


~~~~

create table z (a int,b int ,primary key(a),key(b));

insert into z select 1,1;

insert into z select 3,1;

insert into z select 5,3;

insert into z select 7,6;

insert into z select 10,8;

select * from z where b=3 for update;

~~~~

很明显,这时sql语句通过索引列b进行查询, 会使用传统的Next-key Lock 技术加锁,并且由于两个索引,其需要分别进行锁定。对于聚集索引，其仅对列a等于5的索引加上Record Lock。 而对于辅助索引，其加上的是Next-Key Lock，锁定的范围是(1,3)，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键位加上gap lock，即还有一个辅助索引范围为(3,6)的锁，因此，若在新会话B中运行下面的sql语句执行会被堵塞:

`select * from z where a= 5 lock in share mode;`(第一条)

`insert into z select 4,2;`(第二条)

`insert into z select 6,5;`(第三条)

第一条语句不会被执行，因为会话A中执行的sql语句已经吧聚集索引列a=5的值加上X锁，因此执行会阻塞。

第二天语句主键插入没有4，没有问题，但是插入辅助索引值2在锁定的范围(1,3)中，因此执行同样会被阻塞。第三个sql语句，插入的主键没有被锁定，5也不在(1,3)之间。但插入的值5在另一个锁定的范围(3,6)中，故同样需要等待。

下面的sql语句不会被阻塞，可以立即执行：

`insert into z select 8,6;`

`insert into z select 2,0;`

`insert into z select 6,7;`

若此时没有Gap Lock 锁定(3,6),那么用户可以插入索引b列为3的记录,这会导致会话A中的用户再次执行插叙时会返回不同的记录,即导致 phantom problem(幻读)问题的产生。

可以通过一些的方式显式地关闭Gap Lock；

(1)将事务的隔离级别设置为READ COMMITTED

(2)将参数innodb_locks_unsafe_for_binlog设置为1

在上述的配置下,除了外键约束和唯一性检查依然需要的Gap Lock,其余情况仅使用Record Lock进行锁定。 上述设置破坏了事务的隔离性，并且对于replication(复制)，可能会导致主从数据的不一致。 而且从性能上来看 read committed 不会优于事务隔离级别read repeatable。

对于inset的操作， 如果被锁定则不允许查询。 会话A中表示已经锁定了 所以其他会话插入同样会导致阻塞。

#### 解决 phantom problem(幻读)