MySQL的锁

1、MySQL的锁的基本介绍

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

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

1.1、数据库锁的分类

MySQL 的锁按照范围可以分为全局锁（对整个数据库实例进行加锁）、表锁、行锁，其中行锁是由数据库引擎实现的，并不是所有的引擎都提供行锁，MyISAM 就不支持行锁。

从对数据操作的类型：

共享锁（读锁）：针对同一行数据，多个读操作可以同时进行而不会互相影响，但会阻塞写操作。
排它锁（写锁）：当前写操作没有完成前，它会阻断其他会话的写和读。

共享锁会阻塞涉及到的数据的写，但是不会堵塞读；而排它锁则会把涉及到的数据的读和写都堵塞。也就是共享锁和排它锁都会阻塞涉及到的数据的写操作。

从对数据操作的粒度分：

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

2、表级锁（共享锁、排它锁）

2.1、共享锁（读锁）

假设有会话 A 和会话 B，会话 A 对 mylock 表加了读锁，则：

会话A ：可以查该表的数据，但是无法增删改该表的数据，并且也无法查询其他表的数据，即使其他表未被锁定也无法查询，直到对该锁表进行释放锁。
会话B ：可以查询该锁表的数据，也可以查询、增删改其他表的数据。但是在对已锁的数据进行增删改时，会一直阻塞，导致SQL无法执行结束，直到该表锁被释放后，SQL会自动执行结束。

	已锁定表	其他表
会话A（加锁的会话）	可查；但无法增删改	无法增删改查（会报错）
其他会话	可查；但无法增删改（会一直阻塞）	可增删改查

也就是加共享锁的会话可以查锁表数据，但无法增删改，也无法查询其他表。其他的会话可以查询任何表的数据，但对锁表进行增删改时会阻塞，直到锁表的锁被释放。

总结：共享锁会阻塞写，不会阻塞读。

2.2、排它锁（写锁）

假设有会话 A 和会话 B，会话 A 对 mylock 表加了写锁，则：

会话A ：可以查该表的数据，也可以对该锁定表进行增删改，但是无法查询其他表的数据，即使其他表未被锁定也无法查询，直到对已锁定表进行释放锁。
会话B ：对已锁定表进行增删改查都会一直阻塞，直到该表锁被释放。可以对其他表进行增删改查。

	已锁定表	其他表
会话A（加锁的会话）	可增删改查	无法增删改查（会报错）
其他会话	无法增删改查（会一直阻塞）	可增删改查

也就是加排它锁的会话可以增删改查锁定表的数据，但无法查询其他表。其他的会话对锁定表进行增删改查时会一直阻塞，直到锁表的锁被释放，但可以增删改查其他表的数据。

总结：写锁会阻塞其他会话的读和写。

3、表级锁之MyISAM 引擎表的读写操作和锁

MyISAM 存储引擎在执行查询语句（select）时，会自动给涉及的所有表都加上共享锁，在执行增删改操作前，会自动给涉及到的所有表都加上排它锁。

所以对 MyISAM 表进行操作，会有以下情况：

对 MyISAM 表进行读操作时（会自动加共享锁）：不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
对 MyISAM 表进行写操作时〈会自动加排它锁）：会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程对该表的读写操作。

简而言之，就是读锁会阻塞涉及到的表的写，但是不会堵塞读；而写锁则会把涉及到的表的读和写都堵塞。

MyISAM 的读写锁调度是写优先，这也是 MyISAM 不适合做写为主的表的引擎的原因。因为写操作会自动加写锁，加锁后其他线程就没办法做任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

4、表级锁的相关SQL语句

4.1、查看被锁定的表

可以通过以下命令来查看哪些表被锁定：

show open tables;

结果示例如下：

结果说明如下：

Database：含有该表的数据库
Table：表名称
In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，即当前没有锁表。如果某个表被锁表，则该表的 In_use 字段不会是 0
Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作

4.2、给表加表级锁

可以使用以下命令给表加锁：

lock table 表名 read(write), 表名2 read(write), 其他;

-- 示例
lock table lock_table_test read, lock_table_test2 write;

示例：

先创建一个表 mylock：

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

然后给该表加读锁：

lock table mylock read;

使用 show open tables; 来查看表锁情况：

可以看到，mydbtest.mylock 表已被锁定。

4.3、释放表锁

释放所有表的锁：

unlock tables;

4.4、分析表锁

我们可以通过以下命令行来分析表锁的情况：

show status like 'table_locks%';

结果示例：

可以通过检查 table_locks_waited（主要分析该值）和 table_locks_immediate 状态变量来分析系统上的表锁定，说明如下：

table_locks_waited：出现表级锁定争用而发生等待的次数(也可以说是不能立即获取锁的次数，每触发一次需等待锁的SQL时该值就加1)，此值高则说明存在着较严重的表级锁争用情况。
table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

5、行级锁

MyISAM 引擎支持表级锁，但不支持行级锁。InnoDB 引擎支持行级锁和表级锁。

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行进行加锁来实现的。 InnoDB这种行锁特点意味着：只有通过索引条件来查询修改数据（select xxx where 索引字段），InnoDB才使用行级锁，否则，InnoDB将使用表锁！

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

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

示例：

下面建一张表，并且建立索引，因为只有通过索引来检索数据，InnoDB 才会使用行级表。

CREATE TABLE test_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_lock VALUES(1,'b2');
INSERT INTO test_lock VALUES(3,'3');
INSERT INTO test_lock VALUES(4, '4000');
INSERT INTO test_lock VALUES(5,'5000');
INSERT INTO test_lock VALUES(6, '6000');
INSERT INTO test_lock VALUES(7,'7000');
INSERT INTO test_lock VALUES(8, '8000');
INSERT INTO test_lock VALUES(9,'9000');
INSERT INTO test_lock VALUES(1,'b1');

-- 建立索引列
CREATE INDEX test_lock_a_ind ON test_lock(a);
CREATE INDEX test_lock_b_ind ON test_lock(b);

建立两个会话，并且设置不自动提交事务（这样才能模拟出在一个事务内的情况，否则事务立马提交就看不出效果了），先在 A 会话中对某一行数据进行修改但不提交，然后在 B 会话中也对同一行数据进行修改，如下：

此时，会话 B 的修改会一直阻塞，直到 A 提交事务 commit，会话 B 的进程才能执行成功，否则会一直阻塞直到超时报错，这就是加了行级锁。

当然，如果会话 B 修改其他行的数据则是不会受到影响的，因为行级锁只会锁定指定行的数据。而且如果只是查询则没问题，比如A只是查询，则不会锁行，或者A修改B查询，则B的查询不会受到影响。

5.1、索引失效行锁变表锁

InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件来查询修改数据（select xxx where 索引字段），InnoDB才使用行级锁，否则，InnoDB将使用表锁！行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

比如，上面的例子当中，如果是修改 a 的值，并且条件不加引号，如条件语句为 where b = 1000，由于 b 是 varchar 类型，而此时不使用引号引起来会导致 mysql 底层自动进行类型转换，相当于发生了计算，由此索引会失效。当没有使用到索引时，此时 InnoDB 表不会使用行级锁，而是使用表锁。

如下：

上面 A 会话中由于 SQL 语句中的条件发生了类型转换，导致索引失效，行锁变表锁，所以 B 会话的修改会一直阻塞，直到 A 会话提交事务 B 的修改才能执行结束。

5.3、间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件来修改数据，InnoDB 会给符合范围条件的所有数据记录的索引项加锁，那些值在条件范围内但并不存在在表里的数据，叫做“间隙（GAP）”，InnoDB 也会对这些 “间隙” 加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。可以理解是一个范围锁。

比如，A会话对某个范围条件的数据进行修改，但 A 还未提交事务，此时 B 会话想新增一条数据，但刚好这条数据符合 A 修改语句的条件范围，则 B 的新增会一直阻塞，因此此时 InnoDB 对这些范围数据加了间隙锁。

如下：

5.4、分析行锁

可通过以下命令查看数据库中行锁的情况：

show status like 'innodb_row_lock%';

说明如下：

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

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

6、MySQL 默认事务的锁

数据库使用锁是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。为了提供更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特性：MVCC来实现的。

InnoDB有三种行锁的算法：

Record Lock：单个行记录上的锁。
Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

如果采用MySQL默认的事务隔离级别（可重复读取 Repeatable Read）：防止（避免）不可重复读取和脏读，但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

MySQL 默认事务下各种SQL语句的锁如下：

6.1、SELECT ... FROM

1）SELECT ... FROM ...

该语句是一个快照读，通过读取数据库的一个快照，不会加任何锁，除非将隔离级别设置成了 SERIALIZABLE 。

2）SELECT ... FROM ... LOCK IN SHARE MODE

如果使用到索引并且条件是范围，则在所有索引扫描范围的索引记录上加上共享的间隙锁（next key锁）。如果是唯一索引，只需要在相应记录上加index record lock。这些被共享锁锁住的行无法进行update/delete。
允许其它事务对这些记录再加SHARE锁
如果没有使用到索引，将锁住全表（表级的排他锁），无法进行insert/update/delete。

3）SELECT ... FROM ... FOR UPDATE

如果使用到索引并且条件是范围，则在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
如果没有利用到索引，将锁住全表（表级的排他锁），其它事务无法进行insert/update/delete操作。

6.2、UPDATE ... WHERE ...

在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
如果没有利用到索引，将锁住全表（表级的排他锁）,其它事务无法进行其他的insert/update/delete操作。；

6.3、DELETE FROM ... WHERE ...

语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
如果没有利用到索引，将锁住全表（表级的排他锁）,其它事务无法进行其它的insert/update/delete操作。

6.4、INSERT

在插入的记录上加一把排他锁，这个锁是一个index-record lock，并不是next-key 锁，因此就没有gap 锁，他将不会阻止其他会话在该条记录之前的gap插入记录。

总结：

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

可参考：https://www.cnblogs.com/frankyou/p/9603784.html

posted @ 2022-02-28 23:48 wenxuehai 阅读(604) 评论(0) 编辑收藏举报

刷新页面返回顶部

提升自己，才有选择的能力和勇气