MySQL并发控制_mysql 并发-CSDN博客

本文链接：https://blog.csdn.net/qq_41977838/article/details/123624128

MySQL并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制问题,MySQL可以在两个层面进行并发控制：服务器层和存储引擎层。

MySQL通过加锁实现并发控制：

锁有两类：

读锁：共享锁，即一个读锁不会阻塞其它读锁，多个用户可同时读取同一个资源，而不互相干扰。
写锁：排他锁，即一个写锁会阻塞其它读写锁，在给定时间内，只有一个用户能执行写入。

锁粒度：

表级锁：锁定整张表
行级锁：并发程度更高，但维护较麻烦，会增加系统开销，易产生死锁。行级锁只能在存储引擎级别实现，MyISAM存储引擎不支持行级锁

读锁

读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。

设置读锁

lock table sys_user READ

在这里插入图片描述

执行更新操作

UPDATE sys_user set update_time =  now() where id = 1

在这里插入图片描述

表“sys_user”已被读锁锁定，无法更新

写锁

写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于线程安全策略考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL 会通过锁定防止其他用户读取同一数据。大多数时候，MySQL 锁的内部管理都是透明的

设置写锁

lock table sys_user write

在这里插入图片描述
可以看到，当表 sys_user 在一个进程中被写锁了，该进程对表 sys_user 既有读的权限，又有写的权限。

在这里插入图片描述
可以看到，其他的进程既没有读的权限，又没有写的权限

锁粒度

一种提高共享资源并发性的方式就是让锁定对象更具有选择性。尽量只锁定需要修改的部分数据，而不是所有资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

问题是加锁也需要消耗资源。锁的各种操作，包括获得锁，检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量时间来管理锁，而不是存取数据，那么系统会因此受到影响。

所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。大多数商业数据库系统还没有提供更多的选择，一般都是在表上加行级锁（row-level lock）, 并以各种复杂的方式来实现，以便在锁较多的情况下尽可能提供更好的性能。

而 MySQL 则提供了多种选择，每种 MySQL 存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能。

表锁

表锁是 MySQL 中最基本的锁策略，并且是开销最小的策略。

在特定的场景中，表锁也可能有良好的性能。例如，READ LOCAL 表锁支持某些类型的并发操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面）

尽管存储引擎可以管理自己的锁，MySQL 本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如 ALTER TABLE 之类的语句使用表锁，而忽略存储引擎的锁机制

行级锁

行级锁可以最大程度的支持并发处理（同时也带来了巨大的锁开销）。众所周知，在 InnoDB 和 XtraDB, 以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎中的锁实现，MYISAM引擎只支持表级锁，而InnoDB 引擎能够支持行级锁

共享锁

select * from sys_user LOCK IN SHARE MODE;

MySQL会对查询结果集中每行都添加共享锁。

使用共享锁线程可对其锁定记录进行读取，其他线程同一也可对锁定记录进行读取操作，并且这两个线程读取的数据都属于同一个版本
对于写入操作，使用共享锁的线程需要分情况讨论，当只有当前线程对指定记录使用共享锁时，线程是可对该记录进行写入操作（包括更新与删除），这是由于在写入操作前，线程向该记录申请了排他锁，然后才进行写入操作；当其他线程也对该记录使用共享锁时，则不可进行写入操作，系统会有报错提示。不对锁定记录使用共享锁的线程，当然是不可进行写入操作了，写入操作会阻塞。
使用共享锁进程可再次对锁定记录申请共享锁，系统并不报错，但是操作本身并没有太大意义。其他线程同样也可以对锁定记录申请共享锁。
使用共享锁进程可对其锁定记录申请排他锁；而其他进程是不可以对锁定记录申请排他锁，申请会阻塞。

排他锁

select * from sys_user FOR UPDATE;

MySQL会对查询结果集中每行都添加排他锁，在事务操作中，任何对记录的更新与删除操作会自动加上排他锁

使用排他锁线程可以对其锁定记录进行读取，读取的内容为当前事务的最新版本；而对于不使用排他锁的线程，同样是可以进行读取操作，这种特性是一致性非锁定读。即对于同一条记录，数据库记录多个版本，在事务内的更新操作会反映到新版本中，而旧版本会提供给其他线程进行读取操作
使用排他锁线程可对其锁定记录进行写入操作；对于不使用排他锁的线程，对锁定记录的写操作是不允许的，请求会阻塞
使用排他锁进程可对其锁定记录申请共享锁，但是申请共享锁之后，线程并不会释放原先的排他锁，因此该记录对外表现出排他锁的性质；其他线程是不可对已锁定记录申请共享锁，请求会阻塞
用排他锁进程可对其锁定记录申请排他锁（实际上并没有任何意义）；而其他进程是不可对锁定记录申请排他锁，申请会阻塞

MVCC 多版本并发控制

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC），不仅是 MySQL ,包括 Oracle 等其他数据库系统都实现了 MVCC ，但各自的实现机制不尽相同，因为 MVCC 没有一个统一的实现标准。

MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务的开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

前面说到不同存储引擎的 MVCC 实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

实现原理

InnoDB 的 MVCC ，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number）每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较，下面看一下 REPEATABLE READ 隔离级别下，MVCC 具体是如何操作的

SELECT
InnoDB 会根据以下两个条件检查每行记录：