Mysql的锁分析

1. 间隙锁（Gap Lock）

间隙锁：锁的就是两条记录之间的间隙，更具体一点来说，锁的应该是两条之间范围的所有存在和不存在的数据记录。如：修改 id>3 and id <8，那么id在 3 和 8 之间 的所有记录和不存在的记录都会加锁，其它事务不能操作这些数据。

开启间隙锁：在验证的时候或者使用间隙锁的时候，一定要确保间隙锁在mysql中是开启的状态，并且确保隔离级别为REPEATABLE-READ，否则不起作用。在my.cnf中[mysqld]添加配置 innodb_locks_unsafe_for_binlog = 1，重启。

查看间隙锁是否开启的命令：

show variables like 'innodb_locks_unsafe_for_binlog'，

并且查看隔离级别是否为REPEATABLE-READ。

查看命令：show global variables like 'transaction_isolation';

举个例子：

表myuser

开启一个事务并且做一次修改操作：

#事务1
begin;
update `myuser` set `name` = '莉莉1' where id = 8;
#先不提交，wait...


#事务2
begin;
INSERT INTO `myuser`(`id`, `name`, `account`) VALUES (7, '赫赫11', 350);
commit;

在开启间隙锁和设置隔离级别为REPEATABLE-READ时，先执行事务1，不提交，再执行事务2，提交，那么事务2会堵塞，等待事务1执行完毕，才能继续执行。

2.  临键锁（Next-key Locks）

临键锁是行锁与间隙锁的组合，如上表记录 id>3 and id<12时，(3,12] 的所有记录。

3. 无索引行锁会升级为表锁

如上表 myuser，account 字段没有加索引，写两个事务脚本：

#事务1
begin;
select * from myuser where account =  300 for update;
#不提交


#事务2
begin;
update myuser set name = '张三1' where account =  1000;
...
...

先执行事务1，不提交，再执行事务2，事务2操作的数据和事务1操作的不是同一条记录，那么执行事务2时一直处于堵塞状态，等事务1提交后，事务2才继续执行。同样事务2脚本换为插入一条新数据时，一样也会堵塞，因为行锁不是对记录加的锁，而是对索引加的锁。

现在我们给这个字段再加个索引，再验证一下。就不会出现堵塞了。所以，加行锁时，一定注意不要在无索引的字段上加行锁。

4. 行锁分析

show status like 'innodb_row_lock%';

对于各个状态说明如下：

Innodb_row_lock_current_waits:当前正在等待锁的数量；

Innodb_row_lock_time:从系统启动到现在锁定总时间长度；

Innodb_row_lock_time_avg：每次等待所花平均时间；

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度；

Innodb_row_lock_waits:系统启动到现在总共等待的次数

对于这5个状态变量，比较重要的是：

Innodb_row_lock_time_avg，

Innodb_row_lock_waits，

Innodb_row_lock_time。

尤其是当等待次数很高，而且每次等待时长也很大的时候，我们就要分析系统中为什么有这么多的等待，然后根据分析结果来制定优化。

5.查看 INFORMATION_SCHEMA 系统库锁相关数据表

#查看事务
select * from information_schema.INNODB_TRX;

#查看锁
select * from information_schema.INNODB_LOCKS;

#查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;

#释放锁，trx_mysql_thread_id可以从INNODB_TRX表中查看。
kill trx_mysql_thread_id;

6. 死锁

举个例子说明：

事务1，事务2。以上表 myuser 为操作表。

以下两个事务中的 select 语句刚好同一时刻执行的话，可能会发生死锁，两个事务，互相等待自己需要的资源，且需要的资源同时又被对方加索。

事务1对 id=1 的记录加锁，且需要修改 id=2 的记录。

事务恰好对 id=2 的记录加锁，且需要修改 id=1 的记录。互相等待被对方加了锁的记录，且都不释放锁。这个时候就可能形成死循环了。

#事务1
begin;

select * from myuser where id = 1 for update;

update myuser set name = '李四1' where id = 2；

commit;

#事务2
begin;

select * from myuser where id = 2 for update;

update myuser set name = '张三1' where id = 1；

commit;

多数情况下，mysql会自动检测到死锁的存在，并自动回滚死锁的事务。有些情况下又不能检测到死锁。这个时候可以通过 5 中的查看锁等待，根据事务的线程id查询到死锁的sql，进行优化，并且通过 “kill trx_mysql_thread_id” 命令手动结束死锁。

InnoDB目前处理死锁的方法是：将持有最少行级排它锁的事务回滚。