mysql 行锁最大并发 TPS mysql行锁并发量

转载

autohost 2023-10-02 20:40:59

文章标签 mysql 行锁最大并发 TPS mysql 数据库 java 后端 文章分类 MySQL 数据库

一、锁的概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。
锁保证数据并发访问的一致性、有效性。
锁冲突也是影响数据库并发访问性能的一个重要因素。
锁是Mysql在服务器层和存储引擎层的并发控制。

一句话总结就是：锁机制用于管理对共享资源的并发访问。

二、锁的分类

从操作粒度区分：

表级锁：每次操作锁住整张表。开销小，加锁快，粒度大，不会出现死锁，触发锁冲突的概率高，并发度低。
行级锁：每次操作锁住一行数据。开销大，加锁慢，粒度小，会出现死锁，触发锁冲突的概率低，并发度高。
页级锁：每次锁定相邻的一组记录。锁定粒度、开销、加锁时间介于行级锁和表级锁之间，会出现死锁，并发度一般。

三种锁在不同存储引擎中的应用：

	表锁	行锁	页锁
MyISAM	√
InnoDB	√	√
BDB	√		√

从操作类型区分：

读锁（S）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响（其他事务可以读，但不能写）
写锁（X）：排它锁，当前的写操作没有完成时，会阻塞其他的读和写操作（其他事务既不能读取，也不能写）

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

意向共享锁（IS）：事务在给一个数据加行共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务在给一个数据加行排他锁前必须先取得该表的 IX 锁。

这两种意向锁都属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS和IX锁，表明某个事务正在持有某些行的锁、或该事务准备去持有锁。意向锁存在是为了协调锁之间的关系，支持多粒度锁共存。

★ 为什么意向锁是表级锁呢？

答：为了减少确认次数，提升性能。如果意向锁是行锁，需要遍历每一行去确认数据是否已经加锁；而如果是表锁的话，就只需要判断一次就知道有没有数据行被锁定。

从操作性能区分：

乐观锁：一般采用的方式是对数据记录版本进行对比，在数据更新提交时才会进行冲突检测，如果发现冲突了，则提示错误信息。
悲观锁：在对一条记录进行修改时，为了避免被其他人修改，在修改数据之前先锁定再修改的方式。读锁和写锁是悲观锁的不同实现。

三、表级锁

偏向 MyISAM 存储引擎，MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。在自动加锁的情况下，MyISAM 总是一次性获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁的原因。

MySQL 的表级锁有两种模式，分别是共享锁（读锁）和排他锁（写锁），它们的兼容关系如下：

	读锁	写锁
读锁	兼容	不兼容
写锁	不兼容	不兼容

结合上表，对 MyISAM 表进行操作，会出现以下情况：

对 MyISAM 表的进行读操作（加读锁），不会阻塞其他进程和本线程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程和本线程的写操作。
对 MyISAM 表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读和写操作。

简而言之，就是读锁会阻塞写，但不会堵塞读。而写锁则会堵塞读和写，下面来看例子：MySQL好玩的读写锁案例（表级锁）

四、事务

4.1 什么是事务

事务是由一步或几步数据库操作序列组成的逻辑执行单元，要么全部执行，要么全部不执行。事务具有以下4个属性：

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性。事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性（lsolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

4.2 并发事务处理带来的问题

更新丢失（Lost Update）：当两个或多个事务选择同一数据进行修改，由于每个事务都不知道其他事务的存在，就会导致最后一个事务所做的更新覆盖了其他事务所做的更新。
脏读（Dirty Reads）：当⼀个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外⼀个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，所以另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
不可重复读（Non-Repeatable Reads）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第二个事务的修改导致第⼀个事务两次读取的数据不⼀样。这就出现了在⼀个事务中两次读取的数据是不⼀样的情况，因此称为不可重复读。
幻读（Phantom Reads）：幻读与不可重复读类似。它发生在第一个事务读取了几行数据，接着另一个并发事务插入或删除了一些数据。随后第一个事务接之前相同的查询条件重新查询，发现多了或者少了一些原本不存在的记录，就好像发生了幻觉⼀样，所以称为幻读。

4.3 事务的隔离级别

脏读、不可重复读和幻读，其实都是数据库读一致性的问题，必须由数据库提供一定的事务隔离机制来解决。SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED（读取未提交）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读或幻读。
READ-COMMITTED（读取已提交）：允许读取并发事务已经提交的数据，可以防止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ（可重复读）：对同一数据的多次读取结果都是⼀致的，除非数据是被本身事务所修改，可以防止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE（可串行化）：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

	脏读	不可重复读	幻读
读取未提交	√	√	√
读取已提交	×	√	√
可重复读	×	×	√
可串行化	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读），我们可以通过 show variables like 'tx_isolation' 来查看当前数据库的事务隔离级别。

mysql 行锁最大并发 TPS mysql行锁并发量_数据库

数据库的事务隔离级别越高，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，它们更关心数据并发访问的能力。

五、行级锁

偏向InnoDB存储引擎。InnoDB 与 MyISAM 最大不同之处在于：InnoDB 支持事务和默认采用行级锁。

5.1 InnoDB 加锁规则

对于普通 select 语句，InnoDB 不会加任何锁。
对于 insert、update、和 delete 语句，InnoDB 会自动给涉及数据集加排他锁。
意向锁是 InnoDB 自动加的，不需用户干预。

事务可以通过以下语句显式给记录集加共享锁或排他锁：

select ... lock in share mode  // 加共享锁

其他事务可以查询这些记录集，并且也可以对该记录集加 S 锁，但不能对该记录集进行 DML 操作。

使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据加了 S 锁，所以此时如果当前事务要对该数据进行更新操作，则很有可能造成死锁。

select ... for update  // 加排他锁

其他事务可以查询该记录集，但是不能对该记录集加 S 锁或 X 锁，而是等待获得锁。

使用场景：为了确保让自己查到的数据是最新数据，并且查到后的数据只允许自己修改。

5.2 InnoDB 行锁实现细节

InnoDB 行锁是通过给索引上的索引项加锁来实现的，所以 InnoDB 的这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行锁；否则，InnoDB 将使用表锁！
不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，才能使用行锁。即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
由于 MySQL 的行锁是针对索引加的锁，而不是针对记录加的锁，所以即使多个session访问不同行的记录，但是如果是使用相同的索引键，还是会出现锁冲突的，后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁。

5.3 InnoDB 的间隙锁

当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做 间隙（GAP)，InnoDB也会对这个“间隙”加锁，这种锁机制就是间隙锁（Next-Key锁）。

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

InnoDB使用间隙锁的目的：

防止幻读，以满足相关隔离级别的要求
主要通过两个方面实现：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据
满足恢复和复制的需要
MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在从数据库中不断做基于 BINLOG 的恢复）有以下特点：
（1）MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
（2）MySQL 的 Binlog 是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。
由此可见，MySQL 的恢复机制要求在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，其实也就是不允许出现幻读。