MySQL的锁

数据库的锁概述

数据库的锁，是保证数据库在并发的情况下数据库中的记录能被多线程有序的使用，即要保证数据的一致性。而对于锁对于不同的存储引擎而言是有不同的实现方式的。而总体来说MySQL支持三种类型的锁：表锁、行锁、页锁。
1）表锁：表锁时MYSQL存储引擎中锁粒度最大的锁机制，同时也是加锁和释放锁开销最小、不产生死锁的机制。但同时因为锁的粒度太大对并发度很不友好。实现的存储引擎主要是MyISAM、Memory
2）行级锁：相比表锁，锁粒度小很多，但加锁和释放锁的开销变大了，会产生死锁、但对并发很友好。主要实现引擎InnoDB
3）页锁：性能介于表锁和行锁之间，锁的粒度也介于行锁和表锁之间，也会发生死锁。
说明：对于锁粒度小的锁机制，在相同的保护资源下，锁粒度下的机制要消耗的内存比锁粒度大的锁机制消耗的内存更多，同时它们采用的算法也更复杂，但却对并发度很友好。

表级锁

由于MyISAM采用的是表级锁因此下面就按MyISAM的表级锁来讲解。
1）表锁的模式
1、表级锁有两种模式：共享锁（读锁）、排他锁（写锁）
2、读锁不会阻塞其他进程对相同表的阻塞；写锁会阻塞其他进程对相同表的阻塞。
2）如何加表锁
1、MyISAM会自动在执行查询语句前自动为涉及的所有表加共享模式的表锁；在执行更改（update 、insert、delete）之前自动为涉及的所有表加排他锁。
2、人为显示加锁：

lock table 表名 read/write，表名 read/write;

3）表锁的优化
对于表锁，由于无法改变存储引擎的锁机制，那么为了提高并发度，唯一能做的就是尽量减少持有锁的时间，即减少SQL语句运行的时间，比如减少大的复制的查询，将复杂的查询分拆小的查询；建立高效的索引，让查询数据更快

4）查看表锁的内部竞争情况

show status like 'table%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 100     |
| Table_locks_waited         | 11      |
+----------------------------+---------+

Table_locks_immediate:表示能立即获取表锁的次数
Table_locks_waited：表示出现进程等待获取表锁的次数

注意：如果Table_locks_waited的值很高的话，说明系统中表级锁的竞争很严重

行级锁定

行级锁是存储引擎自己实现的，所以不同的存储引擎可能会有不同的实现，本文以Innodb存储引擎来讲解
1）InnoDB行级锁的模式有四种
1、共享模式
2、排他模式
3、意向共享模式
4、意向排他模式
说明：之所以有意向共享或者排他锁是因为InnoDB存储引擎要实现表级锁。即要实现表级锁和行级锁的共存。当一个事务要给所要的资源加锁时，如果当前资源正被一个共享锁锁定，那么事务可以再加一个共享锁，但不能加排他锁。但如果自己需要的资源被排他锁锁定，那么慢事务只能等待锁定该资源的排他锁的释放。而意向锁正是解决当某个事务需要的资源被其他排他锁锁定是时候，如果事务要加的是共享锁那么可以在该资源的表上加一个意向共享锁；若要加的是排他锁，可以先在当前资源的表上加意向排他锁。值得注意的是意向共享锁在一个表上可以有多个，而意向排他锁只能有一个。
总结：如果当前事务请求的锁的模式和当前资源的锁兼容那么当前事务就可以获得对应的锁，否则就会被阻塞待当前资源锁被释放

2）加锁
1、意向锁时存储引擎自动加不用用户干预。对于更新语句，存储引擎会自动给涉及的行加排他锁；对于查询语句，存储引擎并不会加任何锁。
2、人为加锁

select * from test_innodb_lock where .....lock in share mode;加共享锁
select * from test_innodb_lock where ....for update;加排他锁

3、给InnoDB人为加表锁
（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当

autocommit=0
InnoDB_table_locks=1（默认设置）时

InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：

3）InnoDB行锁的实现
行锁是通过给索引上的索引项加锁实现的，只有通过索引条件检索数据，Innodb才会采用行锁，否则是采用表锁。
（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

4）、间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
意义：防止虚读

5）死锁

MyISAM表锁是无死锁的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
　　在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。
　　那InnoDB是以什么来为标准判定事务的大小的呢？实际上在InnoDB发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。
　　但是有一点需要注意的就是，当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。
　　需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
如何避免：
1、即在一个事务中应该一次性获取所需要的锁的级别不应该逐级获取锁
2、让程序尽量有序的访问表

6）优化行锁
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |
+-------------------------------+-------+

   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（等待总时长）这三项。
  尤其是当等待次数很高，而且每次等待时长也不小的时候，
  我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
　　锁冲突的表、数据行等，并分析锁争用的原因。具体方法如下：
　　
创建监视器表，告知存储引擎我们在监视他

mysql> create table InnoDB_monitor(a INT) engine=InnoDB;
```　
查看存储引擎状态

mysql> show engine InnoDB status;

关闭监视器

mysql> drop table InnoDB_monitor;

　　设置监视器后，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。可能会有读者朋友问为什么要先创建一个叫InnoDB_monitor的表呢？因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了，然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器