深入MySQL锁

必备技能 阅读量

高并发场景下锁机制是怎么保证数据的一致性的和事务隔离性的?

锁种类

  • 按照锁的粒度划分：表锁、页锁、行锁；
  • 按照使用的方式划分：共享锁和排它锁；
  • 按照思想划分：乐观锁和悲观锁。

按锁的粒度划分

  • 「表锁」是粒度最大的锁，开销小，加锁快，不会出现死锁，但是由于粒度太大，因此造成锁的冲突几率大，并发性能低。

Mysql中「MyISAM储存引擎就支持表锁」，MyISAM的表锁模式有两种：「表共享读锁」和「表独占写锁」。

当一个线程获取到MyISAM表的读锁的时候，会阻塞其他用户对该表的写操作，但是不会阻塞其它用户对该用户的读操作。

相反的，当一个线程获取到MyISAM表的写锁的时候，就会阻塞其它用户的读写操作对其它的线程具有排它性。

  • 「页锁」的粒度是介于行锁和表锁之间的一种锁，因为页锁是在BDB中支持的一种锁机制，也很少被人提及和使用，所以这里制作概述，不做详解。

  • 「行锁」是粒度最小的锁机制，行锁的加锁开销性能大，加锁慢，并且会出现死锁，但是行锁的锁冲突的几率低，并发性能高。

行锁是InnoDB默认的支持的锁机制，MyISAM不支持行锁，这个也是InnoDB和MyISAM的区别之一。

按锁的使用方式划分

行锁在使用的方式上可以划分为：「共享读锁（S锁）「和」排它写锁（X锁）」。

当一个事务对Mysql中的一条数据行加上了S锁，当前事务不能修改该行数据只能执行度操作，其他事务只能对该行数据加S锁不能加X锁。

若是一个事务对一行数据加了X锁，该事物能够对该行数据执行读和写操作，其它事务不能对该行数据加任何的锁，既不能读也不能写。

按锁的思想划分

「悲观锁和乐观锁是在很多框架都存在的一种思想，不要狭义地认为它们是某一种框架的锁机制」。

数据库管理系统中为了控制并发，保证在多个事务执行时的数据一致性以及事务的隔离性，使用悲观锁和乐观锁来解决并发场景下的问题。

Mysql中「悲观锁的实现是基于Mysql自身的锁机制实现，而乐观锁需要程序员自己去实现的锁机制」，最常见的乐观锁实现就锁机制是「使用版本号实现」。

MyISAM

MyISAM中默认支持的表级锁有两种：「共享读锁」 和 「独占写锁」。表级锁在MyISAM和InnoDB的存储引擎中都支持，但是InnoDB默认支持的是行锁。

Mysql中平时读写操作都是隐式的进行加锁和解锁操作，Mysql已经自动帮我们实现加锁和解锁操作了，若是想要测试锁机制，我们就要显示的自己控制锁机制。

Mysql中可以通过以下sql来显示的在事务中显式的进行加锁和解锁操作：

1
2
3
4
5
6
7
8
// 显式的添加表级读锁
LOCK TABLE 表名 READ

// 显示的添加表级写锁
LOCK TABLE 表名 WRITE

// 显式的解锁（当一个事务commit的时候也会自动解锁）
unlock tables;

下面我们就来测试一下MyISAM中的表级锁机制，首先创建一个测试表employee ，这里要指定存储引擎为MyISAM，并插入两条测试数据：

1
2
3
4
5
6
7
8
9
10
-- 创建一个测试表
CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(40),
    money INT
)ENGINE MyISAM;

-- 插入两条测试数据
INSERT INTO employee(name, money) VALUES('caoxl', 8888);
INSERT INTO employee(name, money) VALUES('lc', 9999);

查看一下，表结果如下图所示：

1
2
3
4
5
6
7
8
MariaDB [test]> select * from employee;
+----+-------+-------+
| id | name  | money |
+----+-------+-------+
|  1 | caoxl |  8888 |
|  2 | lc    |  9999 |
+----+-------+-------+
2 rows in set (0.01 sec)

MyISAM表级写锁

    1. 与此同时再开启一个session窗口，然后在第一个窗口执行下面的sql，在session1中给表添加写锁：
1
2
MariaDB [test]> LOCK TABLE employee WRITE;
Query OK, 0 rows affected (0.06 sec)
    1. 可以在session2中进行查询或者插入、更新该表数据，可以发现都会处于等待状态，也就是session1锁住了整个表，导致session2只能等待：
1
2
MariaDB [test]> select * from employee;

    1. 在session1中进行查询、插入、更新数据，都可以执行成功：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 插入
MariaDB [test]> INSERT INTO employee(name, money) VALUES('caolx', 1111);
Query OK, 1 row affected (0.02 sec)
-- 更新
MariaDB [test]> UPDATE employee set name='caoxllc' where id=1; 
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 查询
MariaDB [test]> SELECT * FROM employee;
+----+---------+-------+
| id | name    | money |
+----+---------+-------+
|  1 | caoxllc |  8888 |
|  2 | lc      |  9999 |
|  3 | caolx   |  1111 |
+----+---------+-------+
3 rows in set (0.01 sec)

「总结：」 从上面的测试结果显示 「当一个线程获取到表级写锁后，只能由该线程对表进行读写操作，别的线程必须等待该线程释放锁以后才能操作」。

MyISAM表级共享读锁

    1. 接下来测试一下表级共享读锁，同样还是利用上面的测试数据，第一步还是在session1给表加读锁。
1
2
MariaDB [test]> LOCK TABLE employee read;
Query OK, 0 rows affected (0.11 sec)
    1. 然后在session1中尝试进行插入、更新数据，发现都会报错，只能查询数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查询
MariaDB [test]> select * from employee;
+----+---------+-------+
| id | name    | money |
+----+---------+-------+
|  1 | caoxllc |  8888 |
|  2 | lc      |  9999 |
|  3 | caolx   |  1111 |
+----+---------+-------+
3 rows in set (7 min 12.28 sec)
-- 插入
MariaDB [test]> INSERT INTO employee(name, money) VALUES('lxlc', 2222);
ERROR 1099 (HY000): Table 'employee' was locked with a READ lock and can't be updated
-- 更新
MariaDB [test]> UPDATE employee set name='lxlc' where id=1;
ERROR 1099 (HY000): Table 'employee' was locked with a READ lock and can't be updated
    1. 最后在session2中尝试进行插入、更新数据，程序都会进入等待状态，只能查询数据，直到session1解锁表session2才能插入、更新数据。
1
MariaDB [test]> INSERT INTO employee(name, money) VALUES('lxlc', 2222);

「总结：」 从上面的测试结果显示 「当一个线程获取到表级读锁后，该线程只能读取数据不能修改数据，其它线程也只能加读锁，不能加写锁」。

MyISAM表级锁竞争情况

MyISAM存储引擎中，可以通过查询变量来查看并发场景锁的争夺情况，具体执行下面的sql语句：

1
2
3
4
5
6
7
8
MariaDB [test]> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 37    |
| Table_locks_waited    | 2     |
+-----------------------+-------+
2 rows in set (0.27 sec)

主要是查看table_locks_waited和table_locks_immediate的值的大小分析锁的竞争情况。

  • Table_locks_immediate: 表示能够立即获得表级锁的锁请求次数；
  • Table_locks_waited: 表示不能立即获取表级锁而需要等待的锁请求次数分析，「值越大竞争就越严重」。

并发插入

通过上面的操作演示，详细的说明了表级共享锁和表级写锁的特点。但是在平时的执行sql的时候，这些 「解锁和释放锁都是Mysql底层隐式的执行的」。

上面的演示只是为了证明显式的执行事务的过程共享锁和表级写锁的加锁和解锁的特点，实际并不会这么做的。

在我们平时执行select语句的时候就会隐式的加读锁，执行增、删、改的操作时就会隐式的执行加写锁。
MyISAM存储引擎中，虽然读写操作是串行化的，但是它也支持并发插入，这个需要设置内部变量concurrent_insert的值。

它的值有三个值0、1、2。可以通过以下的sql查看concurrent_insert的默认值为 「AUTO(或者1)」。

1
2
3
4
5
6
7
MariaDB [test]> show variables like "%concurrent_insert";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+
1 row in set (0.25 sec)

concurrent_insert的值为:

  • NEVER (or 0)表示不支持比并发插入；
  • AUTO(或者1）表示在MyISAM表中没有被删除的行，运行另一个线程从表尾插入数据；
  • ALWAYS (or 2)表示不管是否有删除的行，都允许在表尾插入数据。

锁调度

MyISAM存储引擎中，「假如同时一个读请求，一个写请求过来的话，它会优先处理写请求」，因为MyISAM存储引擎中认为写请求比读请求重要。

这样就会导致，「假如大量的读写请求过来，就会导致读请求长时间的等待，或者”线程饿死”，因此MyISAM不适合运用于大量读写操作的场景」，这样会导致长时间读取不到用户数据，用户体验感极差。

当然可以通过设置low-priority-updates参数，设置请求链接的优先级，使得Mysql优先处理读请求。

InnoDB

InnoDB和MyISAM不同的是，InnoDB支持「行锁」和「事务」

InnoDB中除了有「表锁」和「行级锁」的概念，还有Gap Lock（间隙锁）、Next-key Lock锁，「间隙锁主要用于范围查询的时候，锁住查询的范围，并且间隙锁也是解决幻读的方案」。

InnoDB中的行级锁是 「对索引加的锁，在不通过索引查询数据的时候，InnoDB就会使用表锁」。

「但是通过索引查询的时候是否使用索引，还要看Mysql的执行计划」，Mysql的优化器会判断是一条sql执行的最佳策略。

若是Mysql觉得执行索引查询还不如全表扫描速度快，那么Mysql就会使用全表扫描来查询，这是即使sql语句中使用了索引，最后还是执行为全表扫描，加的是表锁。

InnoDB行锁和表锁

InnoDB的行锁也是分为行级 「共享读锁（S锁）「和」排它写锁（X锁）」，原理特点和MyISAM的表级锁两种模式是一样的。

若想显式的给表加行级读锁和写锁，可以执行下面的sql语句：

1
2
3
4
5
// 给查询sql显示添加读锁
select ... lock in share mode;

// 给查询sql显示添加写锁
select ... for update；
    1. 下面我们直接进入锁机制的测试阶段，还是创建一个测试表，并插入两条数据：
1
2
3
4
5
6
7
8
9
10
11
// 先把原来的MyISAM表给删除了
DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(40),
    money INT
)ENGINE INNODB;

// 插入测试数据
INSERT INTO employee(name, money) VALUES('caoxl', 1000);
INSERT INTO employee(name, money) VALUES('lc', 2000);
    1. 创建的表中可以看出对表中的字段只有id添加了主键索引，接着就是在session1窗口执行begin开启事务，并执行下面的sql语句：
1
2
3
4
5
6
7
8
9
10
11
12
13
select * from employee where name='caoxl' for update;
MariaDB [test]> begin;
Query OK, 0 rows affected (0.01 sec)

-- 使用非索引字段查询，并显式的添加写锁
MariaDB [test]> select * from employee where name='caolx' for update;
+----+-------+-------+
| id | name  | money |
+----+-------+-------+
|  1 | caolx |  1000 |
|  2 | caolx |  2000 |
+----+-------+-------+
2 rows in set (0.00 sec)
    1. 然后在session2中执行update语句，上面查询的式id=1的数据行，下面update的是id=1的数据行，会发现程序也会进入等待状态：
1
MariaDB [test]> update employee set name='caoxl' where id = 1;

可见若是 「使用非索引查询，直接就是使用的表级锁」，锁住了整个表。

    1. 若是session1使用的是id来查询，如下所示：
1
2
3
4
5
6
7
MariaDB [test]> select * from employee where id='1' for update;  
+----+-------+-------+
| id | name  | money |
+----+-------+-------+
|  1 | caolx |  1000 |
+----+-------+-------+
1 row in set (0.14 sec)
    1. 那么session2是可以成功update其它数据行的，但是这里我建议使用数据量大的表进行测试，因为前面我说过了 「是否执行索引还得看Mysql的执行计划，对于一些小表的操作，可能就直接使用全表扫描」。
    1. 还有一种情况就是：假如我们给name字段也加上了普通索引，那么通过普通索引来查询数据，并且查询到多行数据，拿它是锁这多行数据还是锁整个表呢？

下面我们来测试一下，首先给「name字段添加普通索引」，如下所示：

1
2
3
MariaDB [test]> alter table employee add index index_name(name);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0
    1. 并插入一条新的数据name值与id=2的值相同，并显式的加锁，如下所示：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MariaDB [test]> INSERT INTO employee(name, money) VALUES('caoxl', 3000);
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select * from employee;                        
+----+-------+-------+
| id | name  | money |
+----+-------+-------+
|  1 | caolx |  1000 |
|  2 | caoxl |  2000 |
|  3 | caoxl |  3000 |
+----+-------+-------+
3 rows in set (0.00 sec)

MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from employee where name='caoxl' for update;
+----+-------+-------+
| id | name  | money |
+----+-------+-------+
|  2 | caoxl |  2000 |
|  3 | caoxl |  3000 |
+----+-------+-------+
2 rows in set (0.03 sec)
    1. 当update其它数据行name值不是caoxl的也会进入等待状态，并且通过explain来查看是否name=’caoxl’有执行索引，可以看到sql语句是有执行索引条件的。
1
2
3
4
5
6
7
MariaDB [test]> MariaDB [test]> explain update employee set name='lc' where id = 1;
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | employee | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

结论

  • 执行非索引条件查询执行的是表锁。
  • 执行索引查询是否是加行锁，还得看Mysql的执行计划，可以通过explain关键字来查看。
  • 用普通键索引的查询，遇到索引值相同的，也会对其他的操作数据行的产生影响。

InnoDB间隙锁

当我们使用范围条件查询而不是等值条件查询的时候，InnoDB就会给符合条件的范围索引加锁，在条件范围内并不存的记录就叫做”间隙（GAP）”

大家大概都知道在事务的四大隔离级别中，不可重复读会产生幻读的现象，只能通过提高隔离级别到串行化来解决幻读现象。

但是Mysql中的不可重复是已经解决了幻读问题，它通过引入间隙锁的实现来解决幻读，通过给符合条件的间隙加锁，防止再次查询的时候出现新数据产生幻读的问题。

例如我们执行下面的sql语句，就会对id大于100的记录加锁，在id>100的记录中肯定是有不存在的间隙：

1
Select * from  employee where id > 100 for update;
    1. 接着来测试间隙锁，新增一个字段num，并将num添加为普通索引、修改之前的数据使得num之间的值存在间隙，操作如下sql所示：
1
2
3
4
5
alter table employee add num int not null default 0;
update employee set num = 1 where id = 1;
update employee set num = 1 where id = 2;
update employee set num = 3 where id = 3;
insert into employee values(4,'kris',4000,5);
1
2
3
4
5
6
7
8
9
10
MariaDB [test]> select * from employee;                        
+----+-------+-------+-----+
| id | name  | money | num |
+----+-------+-------+-----+
|  1 | caolx |  1000 |   1 |
|  2 | caoxl |  2000 |   1 |
|  3 | caoxl |  3000 |   3 |
|  4 | kris  |  4000 |   5 |
+----+-------+-------+-----+
4 rows in set (0.00 sec)
    1. 接着在session1的窗口开启事务，并执行下面操作：
1
2
3
4
5
6
7
MariaDB [test]> select * from employee where num=3 for update;
+----+-------+-------+-----+
| id | name  | money | num |
+----+-------+-------+-----+
|  3 | caoxl |  3000 |   3 |
+----+-------+-------+-----+
1 row in set (0.05 sec)
    1. 同时打开窗口session2，并执行新增语句：
1
2
3
4
insert into employee values(5,'ceshi',5000,2);  // 程序出现等待
insert into employee values(5,'ceshi',5000,4);  // 程序出现等待
insert into employee values(5,'ceshi',5000,6);  // 新增成功
insert into employee values(6,'ceshi',5000,0);  // 新增成功

死锁

死锁在InnoDB中才会出现死锁，MyISAM是不会出现死锁，因为MyISAM支持的是表锁，一次性获取了所有得锁，其它的线程只能排队等候。

而InnoDB默认支持行锁，获取锁是分步的，并不是一次性获取所有得锁，因此在锁竞争的时候就会出现死锁的情况。

虽然InnoDB会出现死锁，但是并不影响InnoDB最受欢成为迎的存储引擎，MyISAM可以理解为串行化操作，读写有序，因此支持的并发性能低下。

死锁的解决方案

首先要解决死锁问题，在程序的设计上，当发现程序有高并发的访问某一个表时，尽量对该表的执行操作串行化，或者锁升级，一次性获取所有的锁资源。

然后也可以设置参数innodb_lock_wait_timeout，超时时间，并且将参数innodb_deadlock_detect打开，当发现死锁的时候，自动回滚其中的某一个事务。

总结

上面详细的介绍了MyISAM和InnoDB两种存储引擎的锁机制的实现，并进行了测试。

MyISAM的表锁分为两种模式：「共享读锁」 和 「排它写锁」。获取的读锁的线程对该数据行只能读，不能修改，其它线程也只能对该数据行加读锁。

获取到写锁的线程对该数据行既能读也能写，对其他线程对该数据行的读写具有排它性。

MyISAM中默认写优先于读操作，因此MyISAM一般不适合运用于大量读写操作的程序中。

InnoDB的行锁虽然会出现死锁的可能，但是InnoDB的支持的并发性能比MyISAM好，行锁的粒度最小，一定的方法和措施可以解决死锁的发生，极大的发挥InnoDB的性能。

InnoDB中引入了间隙锁的概念来决解出现幻读的问题，也引入事务的特性，通过事务的四种隔离级别，来降低锁冲突，提高并发性能。

Powered by Hexo and Hexo-theme-hiker

Copyright © 2017 - 2023 Keep It Simple And Stupid All Rights Reserved.

访客数 : | 访问量 :