MySQL神器之一锁

最新推荐文章于 2024-08-03 03:59:08 发布

Amazing_deron

最新推荐文章于 2024-08-03 03:59:08 发布

阅读量313

点赞数

分类专栏：数据库文章标签： MySQL 数据库锁

本文链接：https://blog.csdn.net/Deronn/article/details/103298164

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

不少人在开发的时候，应该很少会注意到数据库锁的问题，也很少给程序加锁(除了库存这些对数量准确性要求极高的情况下)。

然而，即使我们不懂这些锁知识，我们的程序还是跑得好好的，因为这些锁数据库隐式帮我们添加了。

不同的数据库存储引擎支持的锁粒度是不一样的：

InnoDB行锁和表锁都支持；(默认的情况下选择行锁)

MyISAM只支持表锁

InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表级锁，也就是说，InnoDB的行锁是基于索引的。

不同粒度的锁的比较：

表级锁：开销小，加锁快，不会出现死锁，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢，会有死锁出现，发生锁冲突的概率低，并发度高；

MyISAM表级锁模式：

表共享读锁：不会阻塞不同用户对同一表的读请求；

表独占写锁：会阻塞其他用户(其他会话)对同一表的读和写请求；

读读不阻塞：当前用户在读数据，其他用户也可以读数据，不会阻塞；
读写阻塞：当前用户在读数据，其他的用户不能修改当前用户正在读取的数据，会阻塞；
写写阻塞：当前用户正在修改数据，其他的用户不能修改当前用户正在修改的数据，会阻塞；

写写阻塞例子：如果会话1对数据表增加了表写锁，这时会话2请求对同一表增加写锁，这时会话2并不会直接返回，而是会一直处于阻塞状态，直到会话1释放了对表的锁，这时会话2就有可能加锁成功，获取到结果。

这也是MyISAM数据库引擎为什么不适合有大量更新和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获取读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，可能会导致写线程"饿死"。

MyISAM加表锁的方法：

MyISAM在执行查询语句(SELECT) 前，会自动给涉及的表加表共享读锁，在执行更新语句(INSERT、UPDATE、DELETE)前，会自动给涉及的表加表独占写锁，这个过程并不需要用户干预，在自动加锁的情况下MyISAM总是一次获得SQL语句所需要的全部锁，这也正是MyISAM不会出现死锁的原因。(最多是获取锁等待超时)

行锁

在实际情况中，我们是很少手动加表锁的，表锁对于我们程序员来说几乎是透明的，即使InnoDB不走索引，加的表锁也是自动的.

InnoDB支持以下两种类型的行锁：

共享锁(S锁)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁；

排它锁(X锁)：获得排它锁后阻止其他事务取得相同数据集的共享锁和排它锁；

为了允许行锁和表锁共存，InnoDB实现多粒度机制，InnoDB还有两种内部使用的意向锁，这两种锁都是表锁：

意向共享锁：事务打算给数据行加行级共享锁，它加行级共享锁之前必须先获取该表的意向共享锁；

意向排他锁：事务打算给数据行加行级排它锁，它加行级排他锁之前必须先获取该表的意向排它锁；

在使用InnoDB的时候，意向锁的添加也是数据库隐式帮我们添加了，不需要程序员操心。

MVCC和事务的隔离级别

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用的结果就是数据库呈现出的不同的隔离级别。

数据库是一个支持高并发的应用，如果不支持高并发，那么数据库的操作就会成为最大的瓶颈。MySQL是一个服务器/客户端架构的软件，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话，不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样子就会导致不同的事务可能同时访问到相同的记录。事务因为有一个特性是隔离性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，所以设计数据库的人提出了各种隔离级别，来最大限度提升数据库的并发处理事务的能力，但这是在牺牲一定的隔离性下来达到的。

MVCC是一种多版本并发控制机制，MySQL的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，她们一般都实现了多版本并发控制协议(MVCC)，不仅是MySQL,包括Oracle,PostgreSQL等其他数据库系统也都实现了MVCC, 但各自的实现机制不尽相同, 因为MVCC没有一个统一的实现标准。
可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现的机制有所不同，但大多数都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现方式有多种，典型的有乐观并发控制和悲观并发控制
MVCC只在读已提交(Read Committed)和可重复读(Repeatable Read)两个隔离级别下工作，其他两个隔离级别和MVCC不兼容，因为读未提交总是读取最新的数据行，而不是符合当前事务版本的数据行。而串行化则会对所有读取的行都加锁。

MVCC实现：

InnoDB的MVCC实现是通过在数据库每行记录下隐式添加了三个字段：

事务id字段(DB_TRX_ID)：用来标示最近一次对本行记录做修改的事务的标识符，即最后一次修改本行记录的事务id
回滚指针字段(DB_ROLL_PTR):指写入回滚段的undo log
DB_ROW_ID字段：这个字段并不是必要的，当我们创建的表中有主键或者非空唯一键时就不会产生这个字段

undo log：

当我们对记录做了变更操作就会产生undo记录，undo记录中存储的是老版本数据，当一个旧的事务需要读取数据的时候，需要顺着undo链找到满足其可见性的记录。对某条记录进行更新改动后，都会将旧值放到undo log中，就算是该记录的一个旧版本，随着更新版本的增多，所有的版本会被DB_ROLL_PTR属性连接成一个链表，我们把这个链表称为版本链，如下面每次更新记录的版本链：

image_1d6vfrv111j4guetptcts1qgp40.png-57.1kB

ReadView:

对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录版本链中最新的记录就好了，对于SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。对于使用READ COMMITED

和REPEATABLE READ的隔离级别来说，就使用到了我们上边所说的版本链了，核心的问题是：需要判断版本链中的哪个版本是当前事务可见的。所以设计InndDB的人设计了一个ReadView的概念，这个ReadView中包含当前系统中还有那些活跃的读写事务，把它们的事务id放在一个列表中，我们把这个列表命名为m_ids，这样在访问某条记录的时候，只需要按照下面的步骤判断某个记录的版本是否可见：

如果被访问版本的trx_id属性值小于m_ids列表中最小的事务id,表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

如果某个版本的数据对当前事务不可见的话，那么就顺着版本链找到下一个版本的数据，继续按照上面的步骤判断可见性，以此类推，一直到版本链中的最后一个版本，如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的一个非常大的区别就是他们生成的ReadView的时机不同。

READ COMMITTED --每次读取数据前都生成一个ReadView

例子：

在系统中有两个id为100,200的事务在执行：

# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...

注意：事务执行的过程中，只有在第一次真正修改记录时(比如insert、delete、update)，才会分配一个单独的事务id,这个id是递增的。

此时，表t中id为1的记录的得到的版本链如下图所示：

image_1d6vgdl0j1c9d16rbelo1deh17324d.png-42.2kB

假设现在有一个READ COMMITED隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'

这个SELECT1的执行过程如下：

在执行SELECT语句时先生成一个ReadView,ReadView的m_ids列表的内容就是[100,200];

然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c的内容是'张飞'，该版本的trx_id值为100，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本；

下一个版本的列c的内容是'关羽'，该版本的trx_id值也为100，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本；

下一个版本的列c的内容是'刘备'，该版本的trx_id值为80，小于m_ids列表中最小的事务id100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c为'刘备'的记录。

之后，我们把事务id为100的事务提交一下，就像这样：

# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;
COMMIT;

然后再到事务id为200的事务中更新一下表t中id为1的记录：

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...
UPDATE t SET c = '赵云' WHERE id = 1;
UPDATE t SET c = '诸葛亮' WHERE id = 1;

此刻，表t中id为1的记录的版本链就长这样：

image_1d6vgrt5jeh2itl5e41ocl944q.png-57.6kB

然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个id为1的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
# SELECT2：Transaction 100提交，Transaction 200未提交

SELECT * FROM t WHERE id = 1; # 得到的列c的值为'张飞'

这个SELECT2会重新生成一个ReadView，然后跟SELECT1的执行过程一致。

由此可见，通过MVCC机制，一个事务写数据，另一个事务读取数据时可以并发执行，不会阻塞。MVCC实际上实现了非阻塞的读操作，无论另一个事务操作相应的行时是在读取还是在写入；可以使读-写、写-读、读-读并发执行，不需要等待锁的释放；而写-写操作是阻塞的，因为都需要锁定对应的行，所以一个写事务的操作必须等待另一个写事务的锁释放。

REPEATABLE READ ---只会在第一次读取数据时生成一个ReadView

从版本链中查找可见版本的过程跟上述过程一致，不同点在于这个事务去查找时只生成一次ReadView.

MVCC总结：

使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView。

InnoDB加锁：

意向锁是InnoDB自动加的，不需要用户干预；
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排它锁；
对于普通SELECT语句，InnoDB不会加任何锁；
InnoDB在事务的执行过程中，使用两阶段锁协议，随时可以加锁，而且会根据数据库隔离级别自动加锁，锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放；

InnoDB行锁的实现方式：

InnoDB行锁是通过给索引上的索引列加锁来实现的，InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
不论是使用主键索引、唯一索引或普通索引。InnoDB都会使用行锁来对数据加锁；
只有执行计划真正使用了索引，才能使用行锁（可以通过 explain 检查 SQL 的执行计划）

例子：

创建一个没有索引和主键的表，mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;(注意没有建立主键)
插入数据，mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
这时session1执行：mysql> set autocommit=0; select * from tab_no_index where id = 1 for update; 结果：1 row in set (0.00 sec)
这时session2执行：mysql> set autocommit=0; select * from tab_no_index where id = 2 for update; 结果：等待...

看起来session1只给id为1的行加上了排它锁，但是在实际请求id为2的行的排他锁的时候发生了等待，原因是InnoDB在没有索引的情况下，只能使用表锁，锁住整张表的记录。在给id字段加上索引后就，再重新执行这个例子，session2可以获得锁。

由于InnoDB是针对索引加的锁，不是针对记录加的锁，所以虽然访问不同的记录，但是如果是使用相同的索引键，还是会出现锁冲突而导致等待，应用设计的时候要注意这一点。

例子：

创建一个id列有索引，name列没有索引的表，mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
建立索引，mysql> alter table tab_with_index add index id(id);
插入数据，mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
这时session1执行：mysql> set autocommit=0; select * from tab_with_index where id = 1 and name = '1' for update; 结果：成功加锁，1 row in set (0.00 sec)
这时session2执行： mysql> set autocommit=0; select * from tab_with_index where id = 1 and name = '4' for update; 结果：等待...

虽然session1和session2访问的是不同的记录，但是因为都是使用了id为1的索引键(name没有索引)，都是给相同索引列上加行锁，所以一个会话需要等待另一个会话释放行锁。

记录锁(行锁)

记录锁是锁住记录的，这里要说明的是这里锁住的是索引记录，而不是我们真正的数据记录。

如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住.
如果没有表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。
如果要锁的列没有索引，则会进行全表记录加锁。

间隙锁(Gap Lock)

当我们用范围条件而不是相等条件检索数据，并请求排它锁时，InnoDB会给符合条件的有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做"间隙"(Gap),InnoDB也会对这个间隙加锁，这种锁的机制就是间隙锁。

很显然，在使用范围条件检索并锁定记录的时候，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

间隙锁的目的是为了防止幻读，它会把锁的范围扩大，控制间隙锁的参数是：innodb_locks_unsafe_for_binlog，这个参数默认值是OFF，也就是启用间隙锁，他是一个bool值，当值为true时表示disable间隙锁。

注意：间隙锁只会在Repeatable Read隔离级别下使用。

间隙锁例子：

假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101

执行语句：
Select * from emp where empid > 100 for update;
上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

间隙锁导致死锁例子：

表task_queue
Id          taskId
1             2
3             9
10          20
40          41

第一个会话：

mysql> set autocommit=0;

mysql> delete from task_queue where taskId = 20;

mysql> insert into task_queue values(20, 20);

第二个会话：

mysql> set autocommit=0;

mysql> delete from task_queue where taskId = 25;

mysql> insert into task_queue values(30, 25);

在没有并发，或是极少并发的情况下，这样会可能会正常执行，在Mysql中，事务最终都是穿行执行，但是在高并发的情况下，执行的顺序就极有可能发生改变，变成下面这个样子：

mysql> delete from task_queue where taskId = 20;
mysql> delete from task_queue where taskId = 25;
mysql> insert into task_queue values(20, 20);
mysql> insert into task_queue values(30, 25);

这个时候最后一条语句：insert into task_queue values(30, 25); 执行时就会爆出死锁错误。因为删除taskId = 20这条记录的时候，20 -- 41 都被锁住了，他们都取得了这一个数据段的共享锁，所以在获取这个数据段的排它锁时出现死锁。

这种问题的解决办法：通过修改innodb_locaks_unsafe_for_binlog参数来取消间隙锁从而达到避免这种情况的死锁的方式尚待商量，那就只有修改代码逻辑，存在才删除，尽量不去删除不存在的记录。

next-key锁

这个锁本质是记录锁加上gap锁。在RR隔离级别下(InnoDB默认)，Innodb对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。为什么呢? 因为唯一索引能确定行数，而其他索引不能确定行数，有可能在其他事务中会再次添加这个索引的数据会造成幻读。

MyISAM避免死锁

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁

如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。
通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
改变事务隔离级别

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

一些优化锁性能的建议

尽量使用较低的隔离级别；
精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会
选择合理的事务大小，小事务发生锁冲突的几率也更小
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
不要申请超过实际需要的锁级别
除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能