mysql 已提交读 mysql读已提交锁

转载

索姆拉 2023-12-31 15:27:55

文章标签 mysql 已提交读 mysql innodb 锁加锁共享锁意向锁 文章分类 MySQL 数据库

文中论述仅限于InnoDB，比较浅显，由于没有找到可靠可信的资料，可能存在错误。

首先回顾事务的分类，MySQL中，事务可以分为四个级别：详写版本

未提交读：Read uncommitted

所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读(Dirty Read)。

已提交读：Read committed

一个事务只能看见已经提交事务所做的改变，会出现不可重复读的问题。

可重复读：Repeatable read

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，会出现幻读的问题。

可串行化：Serializable

强制事务排序，使之不可能相互冲突，从而解决幻读问题。它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

在MySQL默认的隔离级别(可重复读)下，已经不存在脏读、不可重复读的问题，并且InnoDB存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了幻读的问题。

为什么还需要锁机制呢？

来看下面这种情况：当两个或多个事务选择同一行，然后基于最初选定的值并发更新该行时，由于每个事务都不知道其他事务的存在，就会发生更新丢失问题——最后的更新覆盖了其他事务所做的更新。

所以对于更新丢失这种问题，并不能单靠数据库事务控制器来解决，需要对要更新的数据加必要的锁来解决。

我们知道，InnoDB存储引擎的两个重要特性就是事务管理和行级锁。

所谓行级锁，顾名思义，就是对特定的一行或多行进行加锁。行级锁的锁定粒度最小(相对于表级锁和页级锁)，发生锁冲突的概率最低，并发度也最高。但行锁可能会出现死锁。

死锁的例子：

比如事务A和事务B同时对记录a和记录b进行更新，事务A先对记录a更新(更新操作时InnoDB会对该行自动加锁)，然后尝试对记录b更新，事务B先对记录b更新(假如与事务A更新记录a同时进行)，然后尝试对记录a更新。此时，两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。

InnoDB实现了两种类型的行锁。

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

排他锁(Ｘ)：允许获取排他锁的事务更新数据，阻止其他事务取得相同的记录集共享读锁和排他写锁。

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

①意向共享锁(IS)：事务打算给记录行共享锁，事务在给一个记录行加共享锁前必须先取得该表的IS锁。

②意向排他锁(IX)：事务打算给记录行加排他锁，事务在给一个记录行加排他锁前必须先取得该表的IX锁。

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

意向锁有啥用：解决表级锁和行级锁之间的冲突，比如事务A对表table1中的某条记录加了共享锁，让这一行只能读，不能写。此时，又有事务B申请table1的表锁。如果事务B申请成功，那么理论上它就能修改table1中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

那什么时候需要加锁呢？

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的记录集加排他锁；

对于普通SELECT语句，InnoDB不会任何锁，除非通过下述两种方式显式地加锁：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE：加共享锁

SELECT * FROM table_name WHERE ... FOR UPDATE：加排它锁

那只要是UPDATE、DELETE和INSERT语句，都一定是行锁吗？并不是！

InnoDB行锁是通过索引上的索引项来实现的：只有通过索引条件检索记录，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突(表锁粒度大，并发情况下极容易发生锁冲突)，从而影响并发性能。

另外，InnoDB还有一种锁，叫间隙锁。

当我们用范围条件而不是相等条件检索数据来请求共享或排他锁时，InnoDB除了会给符合条件的已有记录的索引项加锁，还会对键值在条件范围内但并不存在的记录进行加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

举例来说

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

SQL：SELECT * FROM emp WHERE empid > 100 FOR UPDATE