MySQL中的锁与事务

canger_

已于 2022-03-31 15:26:32 修改

阅读量671

点赞数 1

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

于 2022-03-31 15:06:51 首次发布

本文链接：https://blog.csdn.net/Canger_/article/details/123870954

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

锁与事务

锁

乐观锁

乐观锁是一种思想，不是真正意义上的锁。它会先尝试对资源进行修改，在写回时判断资源是否进行了改变，如果没有发生改变就会写回，否则就会进行重试，在整个的执行过程中其实都没有对数据库进行加锁。像无锁队列，CAS操作都是乐观锁的一种表现。

MVCC

Multiversion Concurrency Control —— 多版本并发控制技术

乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

MVCC主要解决的是并发事务中读的问题

MVCC在MySQL中的实现

MVCC的实现依赖于：隐藏字段、Read View、undo log

隐藏字段

DB_TRX_ID：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR：回滚指针，指向该行的 undo log 。如果该行未被更新过，则为空
DB_ROW_ID：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会默认生成该 id ，并生成聚簇索引

ReadView

class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */

  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */

  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */

  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */

  m_closed;                     /* 标记 Read View 是否 close */
}

ReadView主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
m_creator_trx_id：创建该 Read View 的事务 ID

undo log

undo log 主要有两个作用：

当事务回滚时用于将数据恢复到修改前的样子
另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

在 InnoDB 存储引擎中 undo log 分为两种： insert undo log 和 update undo log：

insert undo log ：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
update undo log ：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除

不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
INSERT时，保存当前事务版本号为行的创建版本号
DELETE时，保存当前事务版本号为行的删除版本号
UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

MVCC工作的事务隔离级别

读取已提交（RC)

通过读取最新的事务版本号来实现。即在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表)

可重复读 (RR)

通过读取历史的事务版本号来实现可重复读。即在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View(m_ids 列表)

事务隔离级别具体描述见下文。

MVCC的读机制

MVCC机制，通过判断事务版本号来决定数据读取的是历史的数据还是当前的数据。对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读 (一致性非锁定读）

```
SELECT * FROM table WHERE id = 1;
```

当前读 (锁定读)

特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁

-- 会加共享锁
SELECT * FROM table WHERE id = 1 LOCK IN SHARE MODE;

-- 会加排他锁
SELECT * FROM table WHERE id = 1 FOR UPDATE;

```
INSERT ...
```
```
UPDATE ...
```
```
DELETE ...
```

MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert、delete这些“当前读”，就需要另外的模块来解决了。

悲观锁

悲观锁是真正的锁。它会在获取资源前对资源进行加锁，确保同一时刻只有有限的线程能够访问该资源，其他想要尝试获取资源的操作都会进入等待状态，直到该线程完成了对资源的操作并且释放了锁后，其他线程才能重新操作资源。

根据互斥划分

共享锁 (Shared Lock 读锁)

允许事务对一条行数据进行读取。共享锁之间是兼容的

排他锁 (Exclusive Lock 写锁)

允许事务对一条行数据进行删除或更新。互斥锁与其他任意锁都不兼容

\	share lock	exclusive lock
share lock	√	×
exclusive lock	×	×

根据粒度划分

行锁

表锁

意向锁（Intention Lock）

意向锁根据互斥性划分又分为意向共享锁(IS)和意向互斥锁(IX)

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

锁的互斥（IS表示意向共享锁，IX表示意向排他锁，S为表级的共享锁，X为表级的排他锁）

Lock Type	IS	IX	S	X
IS	√	√	√	×
IX	√	√	×	×
S	√	×	√	×
X	×	×	×	×

总结

InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存
意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与普通的表级别的共享锁 / 排他锁互斥
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突
意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求

锁的算法

记录锁 (Record Lock)

记录锁是加到索引记录上的锁

间隙锁 (Gap Lock)

间隙锁是对索引记录中的一段连续区域的锁，例如

SELECT * FROM persons WHERE pid BETWEEN 100 AND 150 FOR UPDATE;

虽然间隙锁中也分为共享锁和互斥锁，不过它们之间并不是互斥的，也就是不同的事务可以同时持有一段相同范围的共享锁和互斥锁，它唯一阻止的就是其他事务向这个范围中添加新的记录。

如果查询一条不存在的记录，Innodb也会对查询记录的左右区间上间隙锁。

Next-Key Lock

是行锁和Gap锁的结合。解决了在RR事务隔离级别下的幻读的写问题，通过给查询到的行以及前后行都上锁来解决。通过关键字FOR UPDATE来上锁

总结：乐观锁不会存在死锁的问题，但是由于更新后验证，所以当冲突频率和重试成本较高时更推荐使用悲观锁，而需要非常高的响应速度并且并发量非常大的时候使用乐观锁就能较好的解决问题，在这时使用悲观锁就可能出现严重的性能问题

事务

事务的特性

原子性 (Atomicity)

事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不执行

一致性（Consistency）

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

隔离性（Isolation）

并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的

持久性（Durability）

一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

并发事务带来的问题

脏读 (Dirty Read)

事务A修改了某行数据，但是没有提交，而事务B读到了事务A修改前的这行数据

修改丢失 (Lost to modify)

事务A和B先后同时修改某一行数据，但是事务A的修改丢失了。举例来说：商品总数为100，事务A读取后-1=99，事务B读取的值也是100，-1后也等于99，相当于事务A的修改丢失了

不可重复读 (Unrepeatable Read)

事务A在一次事务中前后读取了两次满足条件行的记录，但是两次读取到的值却不一样。原因是两次读取的间隔中，有另一个事务对此行的记录进行了修改

幻读 (Phamtom Read)

事务A读取了几行数据，事务B又插入/删除了几行数据，事务A再次查询发现多/少了几行数据。与不可重复读的主要区别在于数据行是修改还是新增删除。

事务的隔离级别

读取未提交 (Read Uncommited)

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读

读取已提交 (Read Commited)

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

可重复读 (Repeatable Read)

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生(注意是可能)。可重复读级别，通过MVCC机制，使用了快照读(snapshot read)，读取了历史数据的方式，来解决了不可重复读的问题，以及幻读的读问题（写问题没有解决）。

type Camera struct {
	ID   uint   `gorm:"primaryKey"`
	Name string `gorm:"not null;Index"`
}
func main() {

	db, err := gorm.Open(mysql.Open("root:root@tcp(127.0.0.1:3306)/testDB?charset=utf8mb4&parseTime=true&loc=Local"), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			SingularTable: false,
		},
	})
	if err != nil {
		return
	}

	db.AutoMigrate(&Camera{})
    
	ch := make(chan struct{})
	go func() {
        //事务1只查询数据
		tx := db.Debug().Begin(&sql.TxOptions{Isolation: sql.LevelRepeatableRead})
		var cs []Camera
		tx.Raw("SELECT * FROM cameras WHERE name='camera2'").Find(&cs)
		logrus.Print(cs)
		time.Sleep(3 * time.Second)
		tx.Raw("SELECT * FROM cameras WHERE name='camera2'").Find(&cs)
		logrus.Print(cs)
		tx.Commit()
		ch <- struct{}{}
	}()

	go func() {
        //事务2进行了数据的修改
		tx := db.Debug().Begin(&sql.TxOptions{Isolation: sql.LevelRepeatableRead})
		time.Sleep(1 * time.Second)
		tx.Model(&Camera{}).Where("name='camera2'").Update("name", "camera1")
		tx.Commit()
		ch <- struct{}{}
	}()

	for i := 0; i < 2; i++ {
		<-ch
	}
	close(ch)
}

输出结果：

#事务1
[4.875ms] [rows:6] SELECT * FROM `cameras` WHERE name='camera2'
INFO[0000] [{2 camera2} {3 camera2} {4 camera2} {5 camera2} {37 camera2} {38 camera2}]

#事务2
[0.796ms] [rows:6] UPDATE `cameras` SET `name`='camera1' WHERE name='camera2'

#事务1
[0.424ms] [rows:6] SELECT * FROM `cameras` WHERE name='camera2'
INFO[0003] [{2 camera2} {3 camera2} {4 camera2} {5 camera2} {37 camera2} {38 camera2}]

可以看到事务2的修改对事务1来说不可见，事务1前后两次的重复读取是一致的，这就是通过MVCC机制在事务的第一次select时建立了快照，后续的select都是根据这个快照来读取数据。但是对于幻读，RR级别还是不能保证解决，不过可以通过给sql语句加FOR UPDATE给指定行加Next-Key Lock来解决幻读问题

没加FOR UPDATE

func main() {

	db, err := gorm.Open(mysql.Open("root:root@tcp(127.0.0.1:3306)/testDB?charset=utf8mb4&parseTime=true&loc=Local"), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			SingularTable: false,
		},
	})
	if err != nil {
		return
	}

	db.AutoMigrate(&Camera{})
	ch := make(chan struct{})
	go func() {
         //事务1前后查询2次结果，最后再删除
		tx := db.Debug().Begin(&sql.TxOptions{Isolation: sql.LevelRepeatableRead})
		var cs []Camera
		tx.Raw("SELECT * FROM cameras WHERE name='camera2'").Find(&cs)
		logrus.Print(cs)
		time.Sleep(3 * time.Second)
		tx.Raw("SELECT * FROM cameras WHERE name='camera2'").Find(&cs)
		logrus.Print(cs)
		tx.Exec("DELETE FROM cameras WHERE name='camera2'")
		tx.Commit()
		ch <- struct{}{}
	}()

	go func() {
         //事务2插入了一条新的数据
		tx := db.Debug().Begin(&sql.TxOptions{Isolation: sql.LevelRepeatableRead})
		time.Sleep(1 * time.Second)
		tx.Create(&Camera{Name: "camera2"})
		ch <- struct{}{}
		tx.Commit()
	}()

	for i := 0; i < 2; i++ {
		<-ch
	}
	close(ch)
}

执行结果：

#事务1的
[0.207ms] [rows:0] SELECT * FROM cameras WHERE name='camera2'
INFO[0000] []

#事务2的
[0.548ms] [rows:1] INSERT INTO `cameras` (`name`) VALUES ('camera2')

#事务1的
[0.385ms] [rows:0] SELECT * FROM cameras WHERE name='camera2'
INFO[0003] []

#事务1的
[0.256ms] [rows:1] DELETE FROM cameras WHERE name='camera2'

可以看到DELETE时发现删除的数据实际上是1条，而不是第一次读取的0条，发生了幻读。

但是为什么第二条SELECT还是0条呢，依然是通过MVCC机制，使得SELECT语句执行的是快照读，读取到的是历史数据，而不是最新的数据，来保证了可重复读。可以将第二条SELECT的sql语句改写成如下：

SELECT * FROM cameras WHERE name='camera2' LOCK IN SHARE MODE;

那么就会执行当前读，就能读到事务2中插入的最新数据，结果如下：

#事务1的
[0.255ms] [rows:0] SELECT * FROM cameras WHERE name='camera2'
INFO[0000] []

#事务2的
[0.498ms] [rows:1] INSERT INTO `cameras` (`name`) VALUES ('camera2')

#事务1的
[0.372ms] [rows:1] SELECT * FROM cameras WHERE name='camera2' LOCK IN SHARE MODE
INFO[0003] [{45 camera2}]

#事务1的
[0.239ms] [rows:1] DELETE FROM cameras WHERE name='camera2'

加了FOR UPDATE

func main() {

	db, err := gorm.Open(mysql.Open("root:root@tcp(127.0.0.1:3306)/testDB?charset=utf8mb4&parseTime=true&loc=Local"), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			SingularTable: false,
		},
	})
	if err != nil {
		return
	}

	db.AutoMigrate(&Camera{})
	ch := make(chan struct{})
	go func() {
         //第一次查询直接加Next-key Lock
		tx := db.Debug().Begin(&sql.TxOptions{Isolation: sql.LevelRepeatableRead})
		var cs []Camera
		tx.Raw("SELECT * FROM cameras WHERE name='camera2' FOR UPDATE").Find(&cs)
		logrus.Print(cs)
		time.Sleep(3 * time.Second)
		tx.Raw("SELECT * FROM cameras WHERE name='camera2'").Find(&cs)
		logrus.Print(cs)
		tx.Exec("DELETE FROM cameras WHERE name='camera2'")
		tx.Commit()
		ch <- struct{}{}
	}()

	go func() {
         //插入数据
		tx := db.Debug().Begin(&sql.TxOptions{Isolation: sql.LevelRepeatableRead})
		time.Sleep(1 * time.Second)
		tx.Create(&Camera{Name: "camera2"})
		ch <- struct{}{}
		tx.Commit()
	}()

	for i := 0; i < 2; i++ {
		<-ch
	}
	close(ch)
}

执行结果

#事务1的
[0.266ms] [rows:3] SELECT * FROM cameras WHERE name='camera2' FOR UPDATE
INFO[0000] [{39 camera2} {40 camera2} {41 camera2}]

#事务1的
[0.352ms] [rows:3] SELECT * FROM cameras WHERE name='camera2'
INFO[0003] [{39 camera2} {40 camera2} {41 camera2}]

#事务1的
[0.257ms] [rows:3] DELETE FROM cameras WHERE name='camera2'

#事务2的
SLOW SQL >= 200ms
[2004.219ms] [rows:1] INSERT INTO `cameras` (`name`) VALUES ('camera2')

可以看到DELETE时删除的数据也是3条，而且我们发现事务2中的插入一直阻塞到事务1提交之后，才执行，因此变成了一个慢sql。这就是因为FOR UPDATE给查询到的行上了Next-key Lock，来互斥同一时间想要插入到所在行的其他事务，来解决了幻读中写的问题。

可串行化 (Serializable)

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。分布式事务一般会将事务的隔离级别提升到串行化。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√(MVCC解决幻读的读问题，可以通过加next-key解决幻读的写问题)
SERIALIZABLE	×	×	×