MySQL之锁

什么是锁

在计算机科学中，锁是在执行多线程是强制限制资源访问的同步机制，即用于在并发控制中保证对互斥要求的满足。

MySQL的锁

MySQL 个存储引擎从锁定范围上有三种级别的锁：

1. 表级锁定 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2. 行级锁定 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3. 页级锁定 页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

InnoDB

InnoDB 默认使用行级锁，InnoDB 的行锁是通过锁定索引实现的，只有通过索引条件检索数据才会加行锁或则使用表锁。
InnoDB 行锁分为共享锁和排他锁 共享锁：同一个资源可以加多个共享锁，但是不能加排他锁 排他锁：排他锁不能和任何其他锁共存
InnoDB 使用意向锁实现表锁的概念分为意向共享锁和意向意向排他锁，当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务需要在锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。 InnoDB 锁如何添加

1. 意向锁是InnoDB自动添加的，不需要人为添加
2. 对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁
3. 事务可以通过以下语句显示给记录集加共享锁或排他锁：

   共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
   排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

间隙锁

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

死锁

InnoDB 的事务的锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
InnoDB 的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在， InnoDB 发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。

检查行锁的争用情况

通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 50637 |
| Innodb_row_lock_time_avg      | 50637 |
| Innodb_row_lock_time_max      | 50637 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

InnoDB_row_lock_current_waits：当前正在等待锁定的数量。
InnoDB_row_lock_time：从系统启动到现在锁定总时间长度。
InnoDB_row_lock_time_avg：每次等待所花平均时间。
InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间。
InnoDB_row_lock_waits：系统启动后到现在总共等待的次数。

检查死锁、解锁

查询是否锁表

mysql> show OPEN TABLES where In_use > 0;
Empty set (0.02 sec)

查询进程

mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time   | State                  | Info             |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 430476 | Waiting on empty queue | NULL             |
| 13 | root            | localhost | NULL | Query   |      0 | starting               | show processlist |
| 14 | root            | localhost | NULL | Sleep   |   4062 |                        | NULL             |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)

杀死进程

mysql> kill 14;
Query OK, 0 rows affected (0.00 sec)

查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;