MySQL中锁的分类

从本质上讲，锁是一种协调多个进程或多个线程对某一资源的访问 的机制，MySQL使用锁和MVCC机制实现了事务隔离级别。

锁的分类

悲观锁和乐观锁

悲观锁

顾名思义，悲观锁对于数据库中数据的读写持悲观态度，即在整个数据处理的过程中，它会将相应的数据锁定。在数据库中，悲观锁的实现需要依赖数据库提供的锁机制，以保证对数据库加锁后，其他应用系统无法修改数据库中的数据。

注意：

在悲观锁机制下，读取数据库中的数据时需要加锁，此时不能对这些数据进行修改操作。修改数据库中的数据时也需要加锁，此时不能对这些数据进行读取操作。

乐观锁

悲观锁会极大地降低数据库的性能，特别是对长事务而言，性能的损耗往往是无法承受的。乐观锁则在一定程度上解决了这个问题。

注意：

实现乐观锁的一种常用做法是为数据增加一个版本标识，如果是通过数据库实现，往往会在数据表中增加一个类似version的版本号字段。

读锁和写锁

读锁

读锁又称为共享锁，共享锁就是多个事务对于同一数据可以共享一 把锁，都能访问到数据，但是只能读不能修改。

写锁

写锁又称为排他锁，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁， 包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

注意：

需要注意的是，对同一份数据，如果加了读锁，则可以继续为 其加读锁，且多个读锁之间互不影响，但此时不能为数据增加 写锁。一旦加了写锁，则不能再增加写锁和读锁。因为读锁具有共享性，而写锁具有排他性。

表锁、行锁和页面锁

表锁

表锁也称为表级锁，就是在整个数据表上对数据进行加锁和释放锁。典型特点是开销比较小，加锁速度快，一般不会出现死锁，锁定的粒度比较大，发生锁冲突的概率最高，并发度最低。

手动增加表锁

mysql> lock table userinfo read;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table userinfo write;
Query OK, 0 rows affected (0.00 sec)

查看数据表上增加的锁

show open tables;

删除表锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

行锁

行锁也称为行级锁，就是在数据行上对数据进行加锁和释放锁。典 型特点是开销比较大，加锁速度慢，可能会出现死锁，锁定的粒度最小，发生锁冲突的概率最小，并发度最高。

页面锁

页面锁也称为页级锁，就是在页面级别对数据进行加锁和释放锁。 对数据的加锁开销介于表锁和行锁之间，可能会出现死锁，锁定的粒度大小介于表锁和行锁之间，并发度一般。

间隙锁和临键锁

间隙锁

在MySQL中使用范围查询时，如果请求共享锁或排他锁，InnoDB 会给符合条件的已有数据的索引项加锁。如果键值在条件范围内， 而这个范围内并不存在记录，则认为此时出现了“间隙（也就是 GAP）”。InnoDB存储引擎会对这个“间隙”加锁，而这种加锁机制就是间隙锁（GAP Lock）。

例如，userinfo数据表中存在如下数据。

解释：

此时，userinfo数据表中的间隙包括id为(3,15]、(15,20]、 (20，正无穷]的三个区间。如果执行如下命令，将符合条件的用 户的账户余额增加100元。 update userinfo set balance = balance + 100 where id > 5 and id <16; 则其他事务无法在(3,20]这个区间内插入或者修改任何数据。 这里需要注意的是，间隙锁只有在可重复读事务隔离级别下才 会生效。

临键锁

临键锁（Next-Key Lock）是行锁和间隙锁的组合，例如上面例子中 的区间(3,20]就可以称为临键锁。

MySQL中的死锁问题

什么是死锁

死锁是并发系统中常见的问题，同样也会出现在数据库MySQL的并 发读写请求场景中。当两个及以上的事务，双方都在等待对方释放 已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出 现“死锁”。

Deadlock found when trying to get lock...

举例来说 A 事务持有 X1 锁 ，申请 X2 锁，B事务持有 X2 锁，申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待，就造成了死锁。

第一步

打开终端A，登录MySQL，将事务隔离级别设置为可重复读，开启 事务后为userinfo数据表中id为1的数据添加排他锁，如下所示。

mysql> set session transaction isolation level
repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | Java  |  100.00 |
|  2 | MySQL |  200.00 |
+----+-------+---------+
2 rows in set (0.00 sec)

第二步

打开终端B，登录MySQL，将事务隔离级别设置为可重复读，开启事务后为userfinfo数据表中id为2的数据添加排他锁，如下所示。

mysql> set session transaction isolation level
repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo where id = 2;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  2 | MySQL |  200.00 |
+----+-------+---------+
1 row in set (0.00 sec)

第三步

在终端A为userinfo数据表中id为2的数据添加排他锁，如下所示。

mysql> select * from userinfo where id =2 for
update;

注意： 此时，线程会一直卡住，因为在等待终端B中id为2的数据释放排他锁。

第四步

在终端B中为userinfo数据表中id为1的数据添加排他锁，如下所示。

mysql> select * from userinfo where id =1 for
update;
ERROR 1213 (40001): Deadlock found when trying
to get lock; try restarting transaction

通过如下命令可以查看死锁的日志信息。

show engine innodb status\G

注意：

通过命令行查看LATEST DETECTED DEADLOCK选项相关的信 息，可以发现死锁的相关信息，或者通过配置 innodb_print_all_deadlocks（MySQL 5.6.2版本开始提供）参数为ON，将死锁相关信息打印到MySQL错误日志中。

如何避免死锁

MySQL事务的实现原理_什么是redo log

MySQL的事务实现离不开Redo Log和Undo Log。从某种程度上 说，事务的隔离性是由锁和MVCC机制实现的，原子性和持久性是 由Redo Log实现的，一致性是由Undo Log实现的。

什么是redo log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由 两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件 （redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会 把所有修改信息都会存到该日志中。

注意：

先写日志，再写磁盘的技术就是 MySQL 里经常说到的 WAL(Write-Ahead Logging) 技术。

Redo Log刷盘规则

在计算机操作系统中，用户空间( user space )下的缓冲区数据一般情况 下是无法直接写入磁盘的，中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。因此， redo log buffer 写入 redo log file 实际上是先 写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file 中。

mysql 支持三种将 redo log buffer 写入 redo log file 的时机。 innodb_flush_log_at_trx_commit。

Redo Log刷盘最佳实践

不同的Redo Log刷盘规则，对MySQL数据库性能的影响也不同。

创建测试数据库

create database if not exists test;
create table flush_disk_test(
 id int not null auto_increment,
 name varchar(20),
primary key(id)
)engine=InnoDB;

编写存储过程

为了测试方便，这里创建一个名为insert_data的存储过程，接收一 个int类型的参数。这个参数表示向flush_disk_test数据表中插入的记录行数。

drop procedure if exists insert_data;
-- 该段命令是否已经结束了，mysql是否可以执行了。
delimiter $$
create procedure insert_data(i int)
begin
-- 声明变量 s
declare s int default 1;
-- 声明变量 c
declare c varchar(50) default 'binghe';
-- while循环
while s<=i do
-- 开启事务
start transaction;
-- 添加数据
insert into flush_disk_test (name) values(c);
-- 提交事务
commit;
-- s变量累加
set s=s+1;
-- 循环结束
end while;
end$$
-- 该段命令是否已经结束了，mysql是否可以执行了。
delimiter ;

查看刷盘规则

show variables like
'innodb_flush_log_at_trx_commit';

第一步

将innodb_flush_log_at_trx_commit变量的值设置为0。

set global innodb_flush_log_at_trx_commit=0;

调用insert_data向flush_disk_test数据表中插入10万条数据，如下 所示。

mysql> call insert_data (100000);
Query OK, 0 rows affected (2.18 sec)

注意： 可以看到，当innodb_flush_log_at_trx_commit变量的值设置为0时，向表中插入10万条数据耗时2.18s。

第二步

将innodb_flush_log_at_trx_commit变量的值设置为1。

set global innodb_flush_log_at_trx_commit=1;

调用insert_data向flush_disk_test数据表中插入10万条数据，如下所示。

mysql> call insert_data (100000);
Query OK, 0 rows affected (16.18 sec)

注意：

可以看到，当innodb_flush_log_at_trx_commit变量的值设置为1时，向表中插入10万条数据耗时16.18s。

第三步

将innodb_flush_log_at_trx_commit变量的值设置为2。

set global innodb_flush_log_at_trx_commit=2;

调用insert_data向flush_disk_test数据表中插入10万条数据，如下所示。

mysql> call insert_data (100000);
Query OK, 0 rows affected (3.05 sec)

注意：

可以看到，当innodb_flush_log_at_trx_commit变量的值设置为2时，向表中插入10万条数据耗时3.05s。

结论