(三)MySQL锁机制 + 事务

表锁(偏读)

偏向MyISAM存储引擎。开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最低

  • 查看当前数据库中表的上锁情况,0表示未上锁。
show open tables;
  • 添加读/写锁
lock table 表名 read(write) , 表名2...
  • 释放表锁
unlock table;
加读锁(共享)
  • 当前会话和其他会话均可读取加了读锁的表
  • 当前会话不能读取其他表(先把自己的事干好)
  • 其他会话要修改加了读锁的表,必须等待锁释放(阻塞状态)
加写锁(独占)
  • 当前会话可以读取和修改加了写锁的表
  • 当前会话也不能读取其他的表(先把自己的事干好)
  • 其他会话想要读取加了写锁的表,必须等待锁释放(阻塞状态)
结论

MyISAM在执行查询语句时,会自动给涉及的所有表加读锁。再执行增删改操作时,会自动给涉及的表加写锁

简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞。

表锁的分析
  • 查看哪些表被锁了,0表锁未锁:show open table
  • 通过 show status like 'table%'命令

    • Table_locks_immediate:产生表级锁定的次数,每立刻获取一次,锁值加1
    • Table_locks_waited:不能立刻获取锁的次数,每等待一次锁值加1。此值高说明存在严重的表级锁争用情况。
  • 此外,MyISAM是写锁优先调度,这也是不适合做主表的引擎。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。

行锁(偏写)

偏向InnoDB存储引擎。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

InnoDB与MyISAM的最大两个不同点:①支持事务,②采用了行级锁

  • 操作同一行数据

修改表中的同一行数据,将导致 session2 发生阻塞,一旦 session1 提交事务,session2 将执行更新操作。

  • 操作不同行数据

由于采用行锁,session2 和 session1 互不干涉,所以 session2 中的修改操作没有阻塞

  • 无索引导致行锁升级为表锁

    • session1

      set autocommit=0; #关闭自动提交事务
      
      #varchar数据类型不用‘’,导致系统自动转换类型,导致索引失效
      update test_innodb_lock set a=44 where b=4000; 
    • session2

      set autocommit=0;
      
      update test_innodb_lock set b='9001' where a=9; 
      #执行发现发生了阻塞
    • session中的SQL语句索引失效,导致行锁变为表锁。session2需要等待session1提交事务释放锁。
为什么建表后要创建索引?因为不创建索引的话,更新操作的时候,行锁会因为没有索引而变为表锁。

想要锁定某一行时,在后面加上:for update,其他的操作会被阻塞,知道锁定行的会话提交。

总结
  1. InnoDB实现了行级锁定,带来的性能损耗比表级锁定会更高一些,但是在整体并发处理能力方面要远远优于MyISAM
  2. InnoDB的行级锁也有脆弱的一面,当我们使用不当的时候(索引失效变为表锁),可能会让InnoDB的整体性能比MyISAM的差。

事务

事务的特性

特性 说明
原子性(Atomicity) 一个事务中的所有操作,要么全部成功,要么全部失败
一致性(Consistency) 事务开始之前和结束之后,数据库的完整性没有被破坏
隔离性(Isolation) 不同事务之间互不影响(有四种隔离级别)
持久性(Durability) 事务一旦提交,其结果就会持久化,就算发生宕机也能恢复数据
一致性的例子:转账来说,A和B的钱一共5000,不管A和B如何转账,事务结束后A和B的钱加起来还得是5000。
  • 数据库层面怎么实现事务?

    • InnoDB已经帮我们开启了事务,并且每一条语句都会自动提交。如果要保证多条语句的事务性,就要手动开启事务。

InnoDB存储引擎对ACID的实现方式

利用回滚日志(undo log) 和 重做日志(redo log) 两种表实现事务,并实现 MVCC (多版本并发控制)

redo log(重做日志)

  • 用于记录数据修改后的状态
  • 每当有操作执行前,先将相关操作写入redo log。这样当断电等意外发生时,系统恢复后,可以继续完成这些更改。
Binlog日志的两个最重要的使用场景?
  • MySQL主从复制
  • 数据恢复(通过mysqlbinlog工具)
redolog 和 binlog的区别?
  1. redolog是InnoDB特有的,binlog是MySQL的Server层实现的,所有引擎都能使用。
  2. redolog是物理日志,binlog是逻辑日志
  3. redolog是循环着写的,会覆盖,因为空间是固定的会用完。binlog是追加写的,写到一定大小后会切换到下一个。
  4. redolog在事务执行过程中不断写入,binlog是在事务最终提交前写入的。

undo log(回滚日志)

  • 用于记录数据修改前的状态
  • 当一些更改执行一半发生意外时,而无法完成,则可以根据undo log恢复到之前的版本。(undolog记录的是逻辑日志)
  • 相反的记录。当delete一条记录时,undo log 中会记录一条对应的 insert 记录,反之亦然。当 update 一条记录时,它记录一条对应相反的 update 记录。
例如某一时刻数据库宕机了,有两个事务,一个事务已经提交,另一个事务正在处理。数据库重启后,就要根据日志进行前滚或回滚。把已提交事务的更改写到数据文件,未提交的事务更改恢复到事务开始前的状态。

MySQL中ACID底层实现

  • 原子性:主要依靠undo.log日志实现,即在事务失败时执行回滚。
  • 持久性:主要依靠redo.log日志实现。首先,MySQL持久化通过缓存来提高效率。但由于缓存断电就没了,所以需要redo.log日志。在执行修改操作时,sql会先写入到redo.log日志,再写入缓存中。这样即使断电,也能保证数据不丢失,达到持久性
  • 隔离性:数据库四种隔离级别,通过MVCC和锁实现。
  • 一致性:而上面三种特性就是为了保证数据库的有一致性(redolog+undolog)

事务并发带来的问题

  • 丢失更新:(更新操作 加上排它锁来避免)
  • 某一个事务的回滚提交,导致另一个事务已更新数据的丢失
  • 脏读:读取了其他并发事务未提交的数据。破坏了事务的隔离性。
  • 不可重复读:读取了其他并发事务提交的数据。针对update和delete,也是破坏了事务的隔离性。
  • 幻读:读取了其他并发事务提交的数据。针对insert

(三)MySQL锁机制 + 事务_第1张图片

隔离级别:读未提交—>读已提交RC—>可重复读RR—>可串行化

  • MySQL5.5+的InnoDB,在RR级别下能解决幻读。

(三)MySQL锁机制 + 事务_第2张图片

  • V1、V2、V3的值在不同隔离级别下分别是多少?

    • 读未提交:20、20、20
    • 读已提交:18、20、20
    • 可重复读:18、18、20
    • 可串行化:18、18、20

事务隔离级别的实现方式

LBCC(Lock-Based Concurrent Control)基于锁的并发控制

InnoDB锁:

(三)MySQL锁机制 + 事务_第3张图片

  • 共享锁又名读锁,共享锁是可以重入的,但无法修改。
  • 排它锁又名写锁
  • 意向锁:意向锁是表锁,是创建共享锁或排它锁时自动创建的,无法手动创建。作用是,用来告诉你有没有已经锁定的数据,能提高加表锁的效率。

InnoDB锁的底层实现

这是一个只有主键id为1、5、9、11的表,锁的区间如下:

(三)MySQL锁机制 + 事务_第4张图片

Gap是开区间,Next-Key右边是闭区间。

  • Recrod Locks——记录锁(等值匹配,精准匹配)
select * from user where id=1 for update;

锁住本条 id = 1 的记录

  • Gap Locks——间隙锁(范围匹配)
select * from user where id>5 and id<9 for update;

锁住 (5,9)的区间,这时候就不能插入了。间隙锁只存在于RR隔离级别。

思考: select * from user where id>15 for update; 锁哪里?

范围匹配,触发间隙锁,因为表里最大只有11,索引间隙锁会把(11,+oo) 的区间锁住,插入id=12的数据也不行。

  • Next-Key Locks——临键锁(范围匹配,且命中某一条记录)

临键锁是 记录锁和间隙锁的结合。

select * from user where id>5 and id<11 for update;

范围匹配,且命中表中的记录 9,触发临键锁 。锁住了(5,9](9,11],即(5,11]。所以插入id=11的数据也不行。

MVCC(Multi-Version Concurrent Control) 多版本的并发控制。

  • 一条记录在系统中可以存在多个版本,是InnoDB实现事务并发的重要功能。

具体的实现时是,在数据库的每一行,添加额外的三个字段。

  1. DB_TRX_ID:记录更新该行的最后一个事务ID
  2. DB_ROLL_PTR:指向该行对应的 undo log 的指针
  3. DB_ROW_ID:单调递增的行ID,就是AUTO_INCREMENT的主键id

(三)MySQL锁机制 + 事务_第5张图片

快照读与当前读

InnoDB拥有一个自增的全局事务ID,每一个事务开启,都会记录当前事务的唯一ID。同时,新事务创建时,事务系统会将当前未提交的所有事务id 组成的数组 提供 给这个新事务,这个数组我们称为 TRX_ID 集合

  • 快照读

单纯的select操作。

每一个事务更新数据时,都会记录最后更新的事务 DB_TRX_ID

(三)MySQL锁机制 + 事务_第6张图片

如果这一行数据的DB_TRX_IDTRX_ID集合中 大于 当前事务的事务ID,那么就说明这行数据是在当前事务开启后提交的。否则说明这行数据是在当前事务开启之前提交的。

如果遇到了当前事务开启后提交的数据,当前事务会通过DB_ROLL_PTR找到回滚日志,然后进行逻辑上的回滚拿到事务开启时的原数据。

这个通过 undolog+数据行 获取到事务开启时的原始数据的过程就是“快照读”。

详细解释: BD_TRX_ID 是别人的事务ID,我们的事务ID和别人的事务ID都是在未提交事务集合里的,如果别人的事务ID大于我的事务ID,说明这个事务是比我后开启的,那么就说明这行数据是我开启事务后,其他事务改的
  • 当前读

很多时候,我们读取数据库的时候,需要读取的是行的当前数据,不是事务开启时的原始数据。

当前读是通过锁实现的,通过next-key算法将这区域锁上了,其他事务无法修改。

主要包含以下操作:

insert
update
select ... lock in share mode         #共享锁
select ... for update                #排它锁

Innodb在RR级别如何避免幻读?

  • 在快照读情况下(select),通过MVCC来避免幻读

    通过undo log,找到原始数据。

  • 在当前读情况下,通过next-Key来避免幻读

(三)MySQL锁机制 + 事务_第7张图片


问题:InnoDB能解决幻读,但是解决的并不完美。用MVCC实现快照读存在缺陷,就是一旦某个事务的修改操作,覆盖到了其他事务插入的“幻行”,那么这些“幻行”在下次查询时就会再次出现,从而出现幻象问题。

(三)MySQL锁机制 + 事务_第8张图片

你可能感兴趣的