mysql 锁与事务

mysql 锁与事务

锁

按锁级别划分：共享锁与排他锁

• 共享锁（读锁）：其他事务可以读，但不能写。多个共享锁是可以并行的

  共享锁就是只针对update时候加锁，在未对update操作提交之前，其他事务只能够获取最新的记录但不能够update操作。

• 排他锁（写锁） ：其他事务不能读取，也不能写。

  一开始就对记录上锁了，在本事务未提交之前别的事务无权进行任何操作

按锁的粒度划分：表级锁，行级锁和页级锁（mysql BDB支持）

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

按使用方式可以分为：乐观锁和悲观锁

• 悲观锁

  它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态

  数据库流程如下：

  • 在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
  • 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
  • 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
  • 其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

• 乐观锁

  假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。

  在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚

  一般的实现乐观锁的方式就是记录数据版本：第一种是使用版本号，第二种是使用时间戳。

innodb

InnoDB 存储引擎的锁的算法有三种：

• **Record lock：记录锁，**单个行记录上的锁

• Gap lock：间隙锁，锁定一个范围，不包括记录本身，锁的就是两个值之间的空隙

  根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

• **Next-key lock：record+gap 临键锁，**锁定一个范围，包含记录本

间隙锁的目的是为了防止幻读

innodb自动使用间隙锁的条件：

• 必须在RR级别下
• 检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

Next-key Lock：等于行锁加间隙锁。扫描索引记录后，先对索引记录加上行锁，再对索引两边的间隙加上间隙锁。

注意：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

注意

• 在不通过索引条件查询的时候,InnoDB 确实使用的是表锁,而不是行锁。
• 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。
• 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
• 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

意向锁——意向共享锁和意向排他锁

没有意向锁，你做表锁的话，要挨行去扫描行锁吧。

首先，意向锁是一种表锁。

其次，意向锁是InnoDB自动加的，不需要用户干预。

意向锁的加锁过程是自动完成的。

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

【意向锁的共享问题】

意向锁是表锁，它的互斥性是针对表级别的事务，比如一个事务要获取一张表的写权限。所以意向锁对于表级别的事务是互斥的。但是对于行级别的事务是共享的，也就是说，一个意向锁可以被多个行级别的事务所持有

InnoDB加锁方法：

• 意向锁是 InnoDB 自动加的， 不需用户干预。

• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；

• 对于普通 SELECT 语句，InnoDB 不会加任何锁；
  事务可以通过以下语句显式给记录集加共享锁或排他锁：

• • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

锁模式的兼容

（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。）

事务

一言蔽之，事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性

一致性： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

1. 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
3. 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

持久性，隔离性，原子性为一致性的前提

并发带来的问题

• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

小结：

脏读是读取了尚未提交的数据，不可重复读是读取了不停更新的数据（修改），幻读是指读取了不停更新的数据（新增）。

事务的隔离级别

目的：避免脏读，不可重复读，幻读

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

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 的 REPEATABLE-READ（可重读）**并不保证避免幻读，**需要应用使用加锁读来保证。而这个加锁读使用到的机制就是 Next-Key Locks。

一条sql语句的执行过程

client客户端+MySQL 主要分为 Server 层和存储引擎层

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
• 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

server层的流程

1. 连接器：身份认证和权限相关(登录 MySQL 的时候)。也是一个TCP连接

• 建立连接：在客户端工具中输入连接命令，完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候需要你输入用户名和密码；
• 获取权限：如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限；
• 维持和管理连接：客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就需要重连，然后再执行请求

2. 查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。存储的数据是以键值对的形式进行存储，如果开启了缓存，那么在一条查询sql语句进来时会先判断缓存中是否包含当前的sql语句键值对，如果存在直接将其对应的结果返回，如果不存在再执行后面一系列操作。如果没有开启则直接跳过。
3. 分析器：没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

• 词法分析：主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。
• 语法分析：一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

4. 优化器：按照 MySQL 认为最优的方案去执行。
5. . 执行器：执行语句，然后从存储引擎返回数据。
   如果对表 T 有没有执行查询的权限，则会返回错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

写

读

参考

一条 SQL 语句在 MySQL 中如何被执行的? | JavaGuide
https://www.cnblogs.com/mengxinJ/p/14045520.html