MySQL 锁

锁介绍

复习：

• 事务和ACID特性
• 并发事务问题：更新丢失、脏读、不可重复读、幻读
• 隔离级别
• MySQL默认的隔离级别：可重复读
  
  在MySQL中锁是由存储引擎实现的。

行锁

共享锁（shared locks）和排他锁（exclusive locks）

在InnoDB存储引擎中默认使用的就是行锁，分为共享锁和排它锁。

• 共享锁：容许事务加锁去读取某一行数据。
• 排它锁：容许事务持有锁去更新和删除某一行数据。

意向锁 (intention locks)

innodb支持多种粒度的锁，这种锁可以允许行锁和表锁共存。这就是意向锁，意向锁是一种表级锁，表明在事务中后续会对表中的某行数据增加一个锁（共享或排他锁）。
意向锁是 InnoDB 自动加的，不需用户干预。
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
对于普通 SELECT 语句，InnoDB 不会加任何锁；

意向共享锁：

select * from user where id = 1 LOCK IN SHARE MODE;

意向排它锁：

select * from user where id = 1 FOR UPDATE;

意向锁遵循如下规则：

• 事务在获取某一个行的共享锁之前，必须先获取一个意向锁或一个更高级别的锁。
• 事务在获取某一个行的排它锁之前，必须先获取一个意向排它锁。

	排它锁	意向排它锁	共享锁	意向共享锁
排它锁	冲突	冲突	冲突	冲突
意向排它锁	冲突	兼容	冲突	兼容
共享锁	冲突	冲突	兼容	兼容
意向共享锁	冲突	兼容	兼容	兼容

一个事务中的锁是否被授予，取决于当前锁是否与现存锁兼容，如果兼容可以授予，如果冲突则不能授予。如果冲突事务会一直等待，直到现存的冲突锁被释放。MySQL如果发现授予当前申请的锁会导致死锁，会直接抛出错误。

意向锁不会阻塞任何内容，除非有表锁（lock tables … write）。意向锁的主要用途是表明有人正在锁定一行数据、某人打算锁定一行数据。

记录锁（record locks）

记录锁是锁定在索引记录上，例如：

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

该语句会阻塞任何其他事务更新、插入、删除表t中c1=10的数据。

间隙锁（gap locks）

间隙锁条件：
1、条件字段必须有索引，没有索引会锁住整个表；
2、数据库的隔离级别必须为RR；

间隙锁是锁索引记录的，锁住的数据是在一个范围内,不论数据库中是否存在该数据。例如：

create table table_test (
 c1 int,
 c2 int
);
alter table table_test add index test_gap_lock2_c2 (c2) ;
SELECT * FROM table_test WHERE c2 BETWEEN 10 and 20 FOR UPDATE;

会阻塞其他事务对表table_test 中c2[10, 20]之间的所有数据。
如果条件是where c2 > 3且c2有索引，那么会锁住（3, 正无穷）的数据。
如果条件是where c2 >= 3且c2有索引，那么会锁住[3, 正无穷）的数据。
如果条件是where c2 < 3且c2有索引，那么会锁住（负无穷，3）的数据。
如果条件是where c2 <= 3且c2有索引，那么会锁住（负无穷, 3]的数据。

注意：如果c1字段的索引失效，会导致行锁升级为表锁；

查询行锁情况

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: 从系统启动后到现在总共等待的次数；

表锁

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度低。
读锁：表共享锁，阻塞写，不会阻塞读。
写锁：表独占锁，读和写都阻塞。

增加表的读锁或写锁：
lock table my_table read , my_table2 write;

查看表上加的锁：
方法1：
show open tables;
in_use字段1表示有锁。

方法2：
show status like ‘table%’

table_locks_immediate: 产生表级锁锁定的次数，表示可以立即获取锁的查询次数，每获得锁值加1.
table_locks_waited: 出现表级锁锁定而等待的次数，不能立即获取锁的次数，每等待一次锁值加1，此值高说明存在着严重的表级锁征用情况。

注意：

1. 加锁后，当前session不能操作其他表，必须先释放锁才能操作其他表，否则抛错。
2. 加锁后，其他session操作加锁的表会导致阻塞。

死锁

解决死锁有2种办法
1、设置等待锁的超时时间，通过配置innodb_lock_wait_timeout参数。
2、死锁检测，让mysql自动回滚死锁的操作，通过配置innodb_deadlock_detect=on。