MySQL

MySQL 08 - 锁

简介:锁

1. 锁的简介

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。

从对数据操作粒度来分,锁可以分为表级锁和行级锁,而从对数据操作的类型(读/写)分,可以分为读锁和写锁;读锁是共享锁,针对加了读锁的同一份数据,多个读操作可以同时进行而不会互相影响;写锁是排它锁,针对加了写锁的同一份数据,当前写操作没有完成前,它会阻断其他写和读操作。

2. 表级锁

表级锁偏向MyISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

首先我们准备一些测试数据,SQL语句如下:

  • use mysql_test;
  • create table test1(
  • id int not null primary key auto_increment,
  • name varchar(20)
  • ) engine MyISAM;
  • insert into test1(name) values('a');
  • insert into test1(name) values('b');
  • insert into test1(name) values('c');
  • insert into test1(name) values('d');
  • insert into test1(name) values('e');

可以发现,上述语句手动指定了test1表的存储引擎为MyISAM,该表的数据如下:

  • mysql> select * from test1;
  • +----+------+
  • | id | name |
  • +----+------+
  • | 1 | a |
  • | 2 | b |
  • | 3 | c |
  • | 4 | d |
  • | 5 | e |
  • +----+------+
  • 5 rows in set (0.00 sec)

此时加上之前测试索引使用的表,mysql_test库中存在两张表:

  • mysql> show tables;
  • +----------------------+
  • | Tables_in_mysql_test |
  • +----------------------+
  • | test |
  • | test1 |
  • +----------------------+
  • 2 rows in set (0.00 sec)

通过lock table 表1 read[write], 表2 read[write], ...可以手动增加表级锁,如给test1表加读锁:

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

可以通过show open tables查看加过锁的表:

  • mysql> show open tables from mysql_test;
  • +------------+-------+--------+-------------+
  • | Database | Table | In_use | Name_locked |
  • +------------+-------+--------+-------------+
  • | mysql_test | test1 | 1 | 0 |
  • +------------+-------+--------+-------------+
  • 1 row in set (0.00 sec)

结果中出现的In_use为1时则表示该表加了锁。

通过unlock tables可以释放表级锁:

  • mysql> unlock tables;
  • Query OK, 0 rows affected (0.00 sec)
  • mysql> show open tables from mysql_test;
  • +------------+-------+--------+-------------+
  • | Database | Table | In_use | Name_locked |
  • +------------+-------+--------+-------------+
  • | mysql_test | test1 | 0 | 0 |
  • +------------+-------+--------+-------------+
  • 1 row in set (0.00 sec)

下面以一些案例来演示表级锁的读锁和写锁。

2.1. 读锁演示

锁的效果始终是对不同的会话而言的,因此在案例中我们将开启两个会话进行测试。

首先在会话一中,给test1表加上读锁:

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

此时使用新的会话二连接数据库是能够连接上的。

接下来在会话一中尝试查询test1表:

  • mysql> select * from test1;
  • +----+------+
  • | id | name |
  • +----+------+
  • | 1 | a |
  • | 2 | b |
  • | 3 | c |
  • | 4 | d |
  • | 5 | e |
  • +----+------+
  • 5 rows in set (0.00 sec)

发现是可以立即查询到数据的。在会话二中尝试查询:

  • mysql> select * from test1;
  • +----+------+
  • | id | name |
  • +----+------+
  • | 1 | a |
  • | 2 | b |
  • | 3 | c |
  • | 4 | d |
  • | 5 | e |
  • +----+------+
  • 5 rows in set (0.00 sec)

也是可以立即查询出数据的。

当我们在会话一中尝试查询其他表的数据,比如test:

  • mysql> select * from test;
  • ERROR 1100 (HY000): Table 'test' was not locked with LOCK TABLES

会发现有报错,报错信息表示原因是test表没有上锁。

而在会话二中查询其他表的数据是可以立即查询到的:

  • mysql> select * from test;
  • +----+------+------+------+------+------+
  • | id | c1 | c2 | c3 | c4 | c5 |
  • +----+------+------+------+------+------+
  • | 1 | a1 | a2 | a3 | a4 | a5 |
  • | 2 | b1 | b2 | b3 | b4 | b5 |
  • | 3 | c1 | c2 | c3 | c4 | c5 |
  • | 4 | d1 | d2 | d3 | d4 | d5 |
  • | 5 | e1 | e2 | e3 | e4 | e5 |
  • +----+------+------+------+------+------+
  • 5 rows in set (0.00 sec)

在会话一中,尝试修改test1表的数据也会报错:

  • mysql> update test1 set name = 'a1' where id = 1;
  • ERROR 1099 (HY000): Table 'test1' was locked with a READ lock and can't be updated

报错显示test1表正被加了读锁,无法更新。

而在会话二中尝试修改test1表的数据也会阻塞:

  • mysql> update test1 set name = 'a1' where id = 1;

该更新语句会一直阻塞,直到在会话一中使用unlock tables释放锁才会结束阻塞进行更新。首先在会话一中释放锁:

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

会话二会立即结束阻塞并更新数据:

  • mysql> update test1 set name = 'a1' where id = 1;
  • Query OK, 1 row affected (15.21 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0

从执行时间上可以得知此次阻塞时长为15.21秒。

从上面的测试情况可知:对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

2.2. 写锁演示

对于写锁的测试也通过两个会话来演示。将上面测试读锁时给test1表加的锁释放之后,为test1添加写锁:

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

此时使用新的会话二连接数据库是能够连接上的。

然后在会话一上尝试查询和修改操作:

  • mysql> select * from test1;
  • +----+------+
  • | id | name |
  • +----+------+
  • | 1 | a1 |
  • | 2 | b |
  • | 3 | c |
  • | 4 | d |
  • | 5 | e |
  • +----+------+
  • 5 rows in set (0.00 sec)
  • mysql> update test1 set name = 'b1' where id = 2;
  • Query OK, 1 row affected (0.00 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0
  • mysql> insert into test1(id, name) values(6, 'f');
  • Query OK, 1 row affected (0.00 sec)
  • mysql> select * from test1;
  • +----+------+
  • | id | name |
  • +----+------+
  • | 1 | a1 |
  • | 2 | b1 |
  • | 3 | c |
  • | 4 | d |
  • | 5 | e |
  • | 6 | f |
  • +----+------+
  • 6 rows in set (0.00 sec)

可以发现,这些操作都是可以立即成功的。但依旧无法操作其他的表:

  • mysql> select * from test;
  • ERROR 1100 (HY000): Table 'test' was not locked with LOCK TABLES
  • mysql> insert into test(id, c1, c2, c3, c4, c5) values(6, 'f1', 'f2', 'f3', 'f4', 'f5');
  • ERROR 1100 (HY000): Table 'test' was not locked with LOCK TABLES

而在会话二上尝试查询会被阻塞:

  • mysql> select * from test1;
  • ...
  • mysql> select * from test1;
  • +----+------+
  • | id | name |
  • +----+------+
  • | 1 | a1 |
  • | 2 | b1 |
  • | 3 | c |
  • | 4 | d |
  • | 5 | e |
  • | 6 | f |
  • +----+------+
  • 6 rows in set (23.31 sec)

但在test1表加了写锁的情况下,在会话二上查询和修改其他的表都是可以的:

  • mysql> update test set c1 = 'e1_1' where id = 5;
  • Query OK, 1 row affected (0.00 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0
  • mysql> insert into test(id, c1, c2, c3, c4, c5) values(6, 'f1', 'f2', 'f3', 'f4', 'f5');
  • Query OK, 1 row affected (0.00 sec)
  • mysql> select * from test;
  • +----+------+------+------+------+------+
  • | id | c1 | c2 | c3 | c4 | c5 |
  • +----+------+------+------+------+------+
  • | 1 | a1 | a2 | a3 | a4 | a5 |
  • | 2 | b1 | b2 | b3 | b4 | b5 |
  • | 3 | c1 | c2 | c3 | c4 | c5 |
  • | 4 | d1 | d2 | d3 | d4 | d5 |
  • | 5 | e1_1 | e2 | e3 | e4 | e5 |
  • | 6 | f1 | f2 | f3 | f4 | f5 |
  • +----+------+------+------+------+------+
  • 6 rows in set (0.00 sec)

从上面的测试情况可知:对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

2.3. 结论

MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)

MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。

锁类型 读锁表 写锁表 读其他表? 写其他表? 他人读锁表? 他人写锁表? 他人读其他表? 他人写其他表?
读锁 阻塞
写锁 阻塞 阻塞

结合上表,对MyISAM表进行操作,会有以下情况:

  1. 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
  2. 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

简而言之,就是读锁会阻塞写,但是不会堵塞读,而写锁则会把读和写都堵塞。

3. 行级锁

偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。

注:关于事务相关的内容可以参考事务和数据库连接池一文。

我们依旧是准备一些测试数据,语句如下:

  • create table test2(
  • c1 int(11),
  • c2 varchar(16)
  • ) engine=InnoDB;
  • insert into test2 values(1, 'b2');
  • insert into test2 values(3, '3');
  • insert into test2 values(4, '4000');
  • insert into test2 values(5, '5000');
  • insert into test2 values(6, '6000');
  • insert into test2 values(7, '7000');
  • insert into test2 values(8, '8000');
  • insert into test2 values(9, '9000');
  • create index idx_c1 on test2(c1);
  • create index idx_c2 on test2(c2);

运行之后,test2表的数据如下:

  • mysql> select * from test2;
  • +------+------+
  • | c1 | c2 |
  • +------+------+
  • | 1 | b2 |
  • | 3 | 3 |
  • | 4 | 4000 |
  • | 5 | 5000 |
  • | 6 | 6000 |
  • | 7 | 7000 |
  • | 8 | 8000 |
  • | 9 | 9000 |
  • +------+------+
  • 8 rows in set (0.00 sec)

3.1. 行级锁演示

接下来将通过一些案例来演示行级锁。

首先我们打开两个会话,并且在两个会话中执行下面的命令将自动提交关闭:

  • mysql> set autocommit=0;
  • Query OK, 0 rows affected (0.00 sec)

然后在会话一中执行更新操作:

  • mysql> update test2 set c2 = 'b1' where c1 = 1;
  • Query OK, 1 row affected (0.00 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0
  • mysql> select * from test2;
  • +------+------+
  • | c1 | c2 |
  • +------+------+
  • | 1 | b1 |
  • | 3 | 3 |
  • | 4 | 4000 |
  • | 5 | 5000 |
  • | 6 | 6000 |
  • | 7 | 7000 |
  • | 8 | 8000 |
  • | 9 | 9000 |
  • +------+------+
  • 8 rows in set (0.00 sec)

从结果可知是可以修改并且可以立即查询到的;此时由于会话一还未提交,因此c1为1的这一行是被锁定的。如果此时我们在会话二中同时也修改该行数据,会发生阻塞:

  • mysql> update test2 set c2 = 'b3' where c1 = 1;
  • ...

只有当会话一中执行commit操作,释放该行的锁:

  • mysql> commit;
  • Query OK, 0 rows affected (0.00 sec)

会话二才会结束阻塞更新数据:

  • mysql> update test2 set c2 = 'b3' where c1 = 1;
  • Query OK, 1 row affected (16.23 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0

并且虽然数据更新了,但是由于此时会话二还没有提交,因此会话一中不能查看到最新的数据,且也无法更新该行数据,此时该行已被会话二锁定:

  • mysql> select * from test2;
  • +------+------+
  • | c1 | c2 |
  • +------+------+
  • | 1 | b1 |
  • | 3 | 3 |
  • | 4 | 4000 |
  • | 5 | 5000 |
  • | 6 | 6000 |
  • | 7 | 7000 |
  • | 8 | 8000 |
  • | 9 | 9000 |
  • +------+------+
  • 8 rows in set (0.00 sec)
  • mysql> update test2 set c2 = 'b4' where c1 = 1;
  • ...

但会话一中,可以更新除该锁定的行以外的其他行数据:

  • mysql> update test2 set c2 = '1' where c1 = 3;
  • Query OK, 1 row affected (0.00 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0
  • mysql> select * from test2;
  • +------+------+
  • | c1 | c2 |
  • +------+------+
  • | 1 | b1 |
  • | 3 | 1 |
  • | 4 | 4000 |
  • | 5 | 5000 |
  • | 6 | 6000 |
  • | 7 | 7000 |
  • | 8 | 8000 |
  • | 9 | 9000 |
  • +------+------+
  • 8 rows in set (0.00 sec)

以上即是InnoDB存储引擎的行级锁。

3.2. 给某行数据加锁

我们也可以手动给某一行数据加锁,而加的锁也分为(读锁)共享锁和(写锁)排它锁。

  • 共享锁(Share Lock)。共享锁又称读锁,是读取操作创建的锁。其他用户可以并发读取数据,但任何事务都不能对数据进行修改(获取数据上的排他锁),直到已释放所有共享锁。如果事务T对数据行A加上共享锁后,则其他事务只能对数据行A再加共享锁,不能加排他锁。获取共享锁的事务只能读数据,不能修改数据。

在查询语句后面增加lock in share mode,类似select ... lock in share mode,MySQL会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请共享锁,否则会被阻塞。其他线程也可以读取使用了共享锁的行,而且这些线程读取的是同一个版本的数据。

  • 排他锁(Exclusive Lock)。共享锁又称写锁,如果事务T对数据行A加上排他锁后,则其他事务不能再对数据行A加任何类型的锁。获取排他锁的事务既能读数据,又能修改数据。

在查询语句后面增加for update,类似select ... for update,MySQL会对查询结果中的每行都加排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。

3.3. 索引失效导致行锁升级为表锁

在查询操作中,如果导致了索引失效,会让行锁升级为表锁。

在上面的操作中,如果两个会话更新的数据行不一样,各自不会有影响;但如果某一个会话在更新某行数据时,导致索引失效,会让生成的行锁升级为表锁。

如,在我们的表中c2列的数据类型是varchar类型,当我们在两个会话中以正常方式更新不同的行时,是不会导致某一个会话阻塞的:

会话一中:

  • mysql> update test2 set c2 = '5555' where c2 = '5000';
  • Query OK, 1 row affected (0.00 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0

会话二中:

  • mysql> update test2 set c2 = '6666' where c2 = '6000';
  • Query OK, 1 row affected (0.00 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0

但如果会话一在修改行数据时,导致了索引失效,如下面的例子中,条件判断里varchar类型字段没有使用单引号:

  • mysql> update test2 set c2 = '5000' where c2 = 5555;
  • Query OK, 1 row affected (0.00 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0

会话一可以更新成功,但是在会话二中更新其他的行也会出现阻塞操作:

  • mysql> update test2 set c2 = '6000' where c2 = '6666';
  • ...
  • Query OK, 1 row affected (43.13 sec)
  • Rows matched: 1 Changed: 1 Warnings: 0

这就是因为索引失效带来的行级锁升级为表级锁。

3.4. 间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(GAP Lock)。间隙锁会造成在锁定的时候无法插入锁定键值范围内的任何数据,在某些场景下这可能会对性能造成很大的危害。

如我们的测试表test2中,此时没有c1列为2的行:

  • mysql> select * from test2;
  • +------+------+
  • | c1 | c2 |
  • +------+------+
  • | 1 | b3 |
  • | 3 | 1 |
  • | 4 | 4000 |
  • | 5 | 5000 |
  • | 6 | 6000 |
  • | 7 | 7000 |
  • | 8 | 8000 |
  • | 9 | 9000 |
  • +------+------+
  • 8 rows in set (0.00 sec)

我们在会话一中使用范围条件修改c1为1 ~ 5的行:

  • mysql> update test2 set c2 = '1000' where c1 >= 1 and c1 <= 5;
  • Query OK, 4 rows affected (0.00 sec)
  • Rows matched: 4 Changed: 4 Warnings: 0

此时会话一是可以修改成功的,在会话一没提交之前,我们尝试在会话二中插入一条c1列为2的数据行,此时虽然c1列为2的数据并不存在,也会一直阻塞直到会话一提交操作:

  • mysql> insert into test2(c1, c2) values(2, '2000');
  • ...
  • Query OK, 1 row affected (31.41 sec)

3.5. 行锁分析

InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是,InnoDB的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不能比MyISAM高,甚至可能会更差。

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

  • mysql> show status like 'innodb_row_lock%';
  • +-------------------------------+--------+
  • | Variable_name | Value |
  • +-------------------------------+--------+
  • | Innodb_row_lock_current_waits | 0 |
  • | Innodb_row_lock_time | 188162 |
  • | Innodb_row_lock_time_avg | 20906 |
  • | Innodb_row_lock_time_max | 43128 |
  • | Innodb_row_lock_waits | 9 |
  • +-------------------------------+--------+
  • 5 rows in set (0.00 sec)

各个状态量的说明如下:

  • Innodb_row_lock_current_waits:当前正在等待锁定的数量。
  • Innodb_row_lock_time:从系统启动到现在锁定总时间长度。
  • Innodb_row_lock_time_avg:每次等待所花平均时间。
  • Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间。
  • Innodb_row_lock_waits:系统启动后到现在总共等待的次数。

对于这五个状态变量,比较重要的主要是以下三项:

  • Innodb_row_lock_time_avg(等待平均时长)
  • Innodb_row_lock_waits(等待总次数)
  • Innodb_row_lock_time(等待总时长)

尤其是当等待次数很高,而且每次等待时长也不短的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。

还可以可以通过select * from information_schema.innodb_trx\G来查询正在被锁阻塞的SQL语句。

  • mysql> select * from information_schema.innodb_trx\G
  • *************************** 1. row ***************************
  • trx_id: 18587
  • trx_state: RUNNING
  • trx_started: 2018-10-13 22:48:53
  • trx_requested_lock_id: NULL
  • trx_wait_started: NULL
  • trx_weight: 5
  • trx_mysql_thread_id: 13
  • trx_query: select * from information_schema.innodb_trx
  • trx_operation_state: NULL
  • trx_tables_in_use: 0
  • trx_tables_locked: 1
  • trx_lock_structs: 4
  • trx_lock_memory_bytes: 1136
  • trx_rows_locked: 3
  • trx_rows_modified: 1
  • trx_concurrency_tickets: 0
  • trx_isolation_level: REPEATABLE READ
  • trx_unique_checks: 1
  • trx_foreign_key_checks: 1
  • trx_last_foreign_key_error: NULL
  • trx_adaptive_hash_latched: 0
  • trx_adaptive_hash_timeout: 0
  • trx_is_read_only: 0
  • trx_autocommit_non_locking: 0
  • 1 row in set (0.00 sec)

4. 页锁

页锁的开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

5. 优化建议

  • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁。
  • 尽可能较少检索条件,避免间隙锁。
  • 尽量控制事务大小,减少锁定资源量和时间长度。
  • 锁住某行后,尽量不要去调别的行或表,赶紧处理被锁住的行然后释放掉锁。
  • 涉及相同表的事务,对于调用表的顺序尽量保持一致。
  • 在业务环境允许的情况下,尽可能低级别事务隔离。