四.吊打面试官系列-数据库优化-Mysql锁和事务原理

置顶 已于 2024-04-19 09:02:51 修改
阅读量1.8k 收藏 25
点赞数 49
分类专栏： 《升职加薪之深入Mysql性能优化》 《升职加薪系列》 文章标签： 数据库 mysql
于 2024-04-16 19:52:12 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u014494148/article/details/137713701
版权

前言

本篇文章主要讲解两块内容：Mysql中的锁和ACID原理，这2个部分是面试的时候被问的蛮多的看完本篇文章之后相信你对Mysql事务会有更深层次的理解，如果文章对你有所帮助请记得好评

一.Mysql中的锁

1.锁的分类

在Mysql中锁也分为很多种，按照不同的维度有不同的分法：

  • 从性能上分为(或者按照加锁和不加锁)：乐观锁和悲观锁
  • 按照对数据的读写操作分为：读锁和写锁(都属于悲观锁)
  • 按照锁的范围粒度分为：表锁、页锁、行锁

下面我们一一理解这些说的含义和用途。

2.乐观锁和悲观锁

悲观锁和乐观锁是按照加锁机制进行分类的，是一种设计理念，并不是具体的某把锁，乐观锁和悲观锁不仅适用于数据库也适用于Java应用中或其他中间件比如：ElasticSearch也用到乐观锁。

悲观锁

悲观锁的设计理念是悲观的，认为总是有线程并发问题导致数据不安全，所以在整个数据处理过程中，将数据处于锁定状态，数据处理完成后释放锁（一般是事务提交释放锁）。悲观锁的实现，往往依靠数据库提供的锁机制。通常是在 select语句后面增加 for update 来锁定数据。其实对于synchronized，Lock等锁也是悲观锁的一种。下面是在Java中的用法，以修改Account为案例

1. 查询User，通过 select id,amount from Account where id = 1 for update 加锁
2. 在业务代码中修改amount的值
3. 执行update，完成事务提交了

上面的代码在第一步的时候id =1 的这行数据就会被上锁，因为我们增加了for update ,直到第三步事务提交完成才会释放锁，也就意味着如果有2个线程同时执行上面的代码，只有1个线程能执行，第二个线程会阻塞在第一步进行等锁。等第一个线程释放锁后，第二个线程才能继续执行。这样就保证了2个线程安全执行。 这个效果就和使用synchronized锁代码块类似。

乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，在数据处理的过程中会不加锁，在数据进行提交更新的时候(update更新到数据库的时候)，才会正式对数据的冲突与否进行检测，如果发现冲突了就进行重试或者抛出异常等。在数据库中通使用version版本号，或者时间戳，或者UUID来实现乐观锁，AtomicInteger等原子类，ElasticSearch也是使用乐观锁来控制并发修改。

下面我们举例来说明，现有2个线程修改同一个账户，都对账户金额+100，只是时间点一前一后，如图：
在这里插入图片描述
上图出现了明显的问题，A线程基于100+100，把数据库修改为200，B线程比A线程晚一步，但是也是基于100+100，又把数据库修改为200，正确的结果应该是300，但是实际上是200，这就是事务并发修改出现的丢失更新问题，B的更新把A的更新给覆盖掉了。

解决这个问题我们可以使用悲观锁，在查询账户的SQL后面增加for update,这样的话A线程的事务没提交之前，B事务都会阻塞在 查询账户等锁，等A线程提交事务，数据库修改为200后，B现成再查下就是以200为基础+100，结果是正确的。 但是这种场景最好的解决方案是乐观锁，性能更优。

乐观锁通常就是通过一个：Version 版本号来判断是否产生事务并发修改，我们来给account表增加一个version字段，每次查询的时候需要把 version查询出俩，每次update的时候需要判断version版本号是否一致，如下
在这里插入图片描述
当我们在执行update的时候需要加上条件 version = #{version} 来比较版本号，如果：B线程从 查询 到 修改 这之间，A线程修改了该条数据，那么数据库版本号会被+1，B线程在执行update的时候version会出现不匹配的情况。那么这个时候我们直到可以能出现了并发修改问题了，我们可以通过接受到update的返回值，如果发现是 0 ，就抛出异常，或者进行重试等操作，这样就可以避免覆盖更新了。

悲观锁是在业务一开始就加锁，业务处理完成之后，释放锁，拿Mysql来说当执行加了 for update 的查询语句时就加锁，业务处理完成，事务提交就释放锁。而乐观锁是不加锁，只是最后做数据同步的时候，判断该条数据时候被别的线程修改过，来决定要不要执行当前业操作。 所以一个是加锁，一个是不加锁的，乐观锁的性能是更高的除此之外还可以通过UUID，时间戳等方式来实现乐观锁。

3.读锁和写锁

在Mysql中按数据的读写操作分为：共享锁(读锁)和排它锁(写锁)，共享锁意味着多个事务可以获取到同一把锁，通常是作用于读操作。而排它锁是相互排斥的，只可能有一个事务可以获取排它锁，通常是发生在写操作上。

读锁

读锁也叫共享锁（share Lock）:又称(S锁)，允许多个事务同时获得该锁,也就是允许多个事务同时读一条数据。当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持[并发]的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。在select语句末尾加上lock in share mode关键字即可加共享锁。

写锁

写锁也叫排他锁（exclusive Lock）又称(X锁)，当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取，避免了出现脏数据和脏读的问题。 Mysql在执行写操作时默认会加上排它锁，或者我们可以在select语句末尾加上for update关键字来实现排它锁。

对于读锁和写锁的兼容性你只需要记住：只要有写锁出现，一定是相互排斥的，需要等锁。都是读锁则相互兼容

锁读锁写锁
读锁YN
写锁NN
意向锁

意向锁（Intention Lock）：又称I锁，针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

意向锁主要分为：

  • 意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
  • 意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

4.表锁页锁行锁

表锁

表锁(table lock)是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问;特点:粒度大，加锁简单，容易冲突;一般用在整表数据迁移的场景，业务中一般不会锁表,加锁和释放锁如下

LOCK TABLES table_name [READ | WRITE];
 
-- 在这里执行查询操作
 
UNLOCK TABLES;
页锁

页锁就是在页的粒度上进行锁定，页级锁是MysQL中锁定粒度介于行级锁和表级锁中间的一种锁．表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点:开销和加锁时间界于表锁和行锁之间，会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。只有BDB存储引擎支持页锁

行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问，特点:粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高

InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁比如：select * from t_user name name = zs for update ，如果name字段没有索引，那么在可重复读(RR)的隔离级别下Mysql会把整个表锁住。因为行锁扫描的是索引，在RR隔离级别下为了防止当前事务扫描过的索引被其他事务修改(会出现不可重复读问题)，或者间隙被其他事务插入数据(会出现幻读问题)，所以Mysql会把扫描过的索引记录和间隙都上锁。

间隙锁

是属于行锁的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。间隙锁只会出现在REPEATABLE_READ(重复读)的事务级别中。间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的，事务并发会产生幻读问题，在RR隔离级别中，Mysql通过间隙锁来解决幻读问题。下面是一个间隙锁的案例：假如数据库有数据ID为： 1，2，3，8，10 ； 那么 3-8，8-10 ，10-正无穷 三个间隙，当我们执行

select * from 表 where id =  5 for update

那么 3-8这个区间就会被锁住，其他事务是无法插入向3-8这个范围插入数据的。其他区间也是一样的。

也就是说，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

临键锁(Next-key Locks)

(Next-Key lock):也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。如果你使用select … in share mode或者select … for update语句，那么InnoDB会使用临键锁，因而可以防止幻读。

临键锁 ：锁住的是前面间隙+行 ，比如：select * from table where id >= 2 for update ，那么除了会把2-5之间加上间隙锁，还会多锁住一行2。

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞，Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了

6.锁超时

什么是锁超时

另外：当某个线程操作一个条被别的线程加了锁的数据时，等待时间过长就会出现：Lock wait timeout exceeded 异常，

select * from t_user where id = 1 for UPDATE
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.445s

默认情况下：InnoDB的锁获取时间是50s，可以通过SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; 命令来查看超时时间，

分析锁超时

遇到这种问题的决绝思路是：

  1. 分析并解决死锁：死锁是另一种导致锁等待超时的原因，检查系统日志并找出导致死锁的查询，然后修改它们以避免死锁。另外可以通过show PROCESSLIST 显示进程列表来分析SQL锁住的原因
  2. 优化查询：检查导致锁等待超时的查询语句，并优化它们以减少执行时间。
  3. 增加超时时间：如果查询优化不能解决问题，可以考虑增加innodb_lock_wait_timeout的值。这可以通过在MySQL配置文件中设置或者在会话级别动态设置。
  4. 硬件资源：如果查询优化和超时时间增加都无法解决问题，可能需要考虑增加服务器的硬件资源，如CPU或内存，以提高数据库的并发处理能力。
查看锁信息

在排查问题的过程中我们可以通过show status like 'innodb_row_lock%';来查看锁的情况
在这里插入图片描述
对各个状态量的说明如下：

  • Innodb_row_lock_current_waits: 当前正在等待锁定的数量
  • Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
  • Innodb_row_lock_time_avg: 每次等待所花平均时间
  • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
  • Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：平均时长，总次数，等待总时长，当发现大量锁等待时长比较久的时候，就应该针对性优化了。下面是查看事务信息

查看事务信息

可以通过下面命令查看当前事务的情况

  • select * from INFORMATION_SCHEMA.INNODB_TRX : 查看当前事务信息
    在这里插入图片描述

  • select * from INFORMATION_SCHEMA.INNODB_LOCKS : 查看锁，8.0之后需要换成这张表performance_schema.data_locks
    在这里插入图片描述

  • select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS ： 查看锁等待，8.0之后需要换成这张表 performance_schema.data_lock_waits
    在这里插入图片描述

  • kill trx_mysql_thread_id ：释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到。执行kill 线程ID即可杀掉等待锁的线程，如果事务等锁时间太长可以手动kill掉
    在这里插入图片描述

  • show engine innodb status; ：查看锁等待详细信息
    在这里插入图片描述
    通过status内容可以分析锁的情况，如下

=====================================
2024-04-18 09:12:39 0x4008 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 422 srv_active, 0 srv_shutdown, 254128 srv_idle
srv_master_thread log flush and writes: 254506
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 17880
OS WAIT ARRAY INFO: signal count 18636
RW-shared spins 0, rounds 50773, OS waits 6933
RW-excl spins 0, rounds 8923, OS waits 214
RW-sx spins 271, rounds 7421, OS waits 217
Spin rounds per wait: 50773.00 RW-shared, 8923.00 RW-excl, 27.38 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 200989
Purge done for trx's n:o < 200975 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284135159995904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 200988, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 38, OS thread handle 25672, query id 5081 localhost ::1 root statistics
select * from t_user where id = 1 for UPDATE
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2015 page no 4 n bits 336 index PRIMARY of table `test`.`t_user` trx id 200988 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 8; hex 0000000000000001; asc         ;;
 1: len 6; hex 000000030f32; asc      2;;
 2: len 7; hex ae0000019b0110; asc        ;;
 3: SQL NULL;
 4: len 4; hex 80000012; asc     ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 8; hex 8000000000000001; asc         ;;
 7: len 5; hex 3131313131; asc 11111;;
 8: len 5; hex 3131313131; asc 11111;;
 9: len 3; hex 313131; asc 111;;
 10: SQL NULL;

------------------
---TRANSACTION 200981, ACTIVE 454 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 36, OS thread handle 16392, query id 5092 localhost ::1 root starting
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2661772 OS file reads, 206735 OS file writes, 53342 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 210, seg size 212, 17 merges
merged operations:
 insert 1190, delete mark 9, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 4863601883
Log flushed up to   4863601883
Pages flushed up to 4863601883
Last checkpoint at  4863601874
0 pending log flushes, 0 pending chkp writes
5838 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 188802
Buffer pool size   512
Free buffers       250
Database pages     256
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2658163, created 146311, written 169097
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=5272, Main thread ID=7944, state: sleeping
Number of rows inserted 19119354, updated 43, deleted 9, read 358383195
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

二.事务的四大特性

1.什么是事务

事务: 指作为单个逻辑工作单元(Service方法)执行的一系列操作（数据库操作。），要么完全地执行，要么完全地不执行.事务可以看做是一组任务，通常对应了一个业务方法,这些任务要么全部成功,要么全部失败。在实际的业务开发中我们通常在Service层通过@Transcational注解来指定事务。我们要知道的是Spring只是帮我们管理事务，它对事务的操作也是调用数据库的事务指令来完成的。

2.事务的四大特性

事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）四个特性，简称 ACID，缺一不可。

原子性 Atomicity ：

把一组操作(一个事务)看成一个整体，这一组操作要么全部成功，或者全部失败，不可分割。比如一个service方法中对数据库做了2次写操作，那么这2个操作需要都成功，一旦某一个操作失败就必须要回顾。

Consistency 一致性

一致性要求任何写到数据库的数据都必须满足于预先定义的规则（数据不能出错），简单来说就是在任何时间点都不能出现违反一致性要求的状态。比如：A账户扣减500，B账户增加500，如果A口扣减成功，B并未增加成功，那么数据就会出问题，和我们预先定义的规则不一致。

Durability 持久性

持久性的关键在于一旦“完成提交”（committed），那么数据就不会丢失，即使Mysql重启或者宕机数据依然不会丢失，因为数据已经被持久化到磁盘。

Isolation 隔离性

多个事务之间是相互隔离的，如果多个事务同时执行产生了影响就可能会出现各种并发问题，比如：脏读，幻读等，针对于不同的并发问题可以通过不同的事务隔离级别来解决。

3. 原子性实现原理

Mysql的原子性是利用Innodb的undo log来实现，undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。 例如

  1. 当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据

  2. 当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作

  3. 当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

在这里插入图片描述

4.持久性的实现原理

Mysql通过Redo Log实现持久性，Redo Log记录的是新数据的备份。在事务提交前，先将Redo Log持久化，当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。至于数据会异步方式刷盘到Mysql磁盘文件。

采用redo log的好处？

redo log进行刷盘比对数据页刷盘效率高，具体表现如下

  • redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。

  • mysql的数据页在磁盘上是随机的,而Redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

在这里插入图片描述

redo log它由两部分组成，内存中的 redo log buffer，磁盘上的 redo log file

1.redo log file 由一组文件组成，当写满了会循环覆盖较旧的日志，这意味着不能无限依赖 redo log，更早的数据恢复需要 binlog

2.buffer 和 file 两部分组成意味着，写入了文件才真正安全，同步策略由参数innodb_flush_log_at_trx_commit 控制

  • 0 - 每隔 1s 将日志 write and flush 到磁盘
  • 1 - 每次事务提交将日志 write and flush（默认值）
  • 2 - 每次事务提交将日志 write，每隔 1s flush 到磁盘，意味着 write 意味着写入操作系统缓存，如果 MySQL 挂了，而操作系统没挂，那么数据不会丢失

关于UndoLog 和 RedoLog 请看另一篇文章《探秘MySQL底层架构：设计与实现流 程一览》

三.事务的并发问题

1.事务并发和隔离级别

在实际项目中，事务并发问题难以避免，而事务并发会产生各种各样的问题，如：脏读，幻读，不可重复读，丢失更新等

  • 脏读：事务B读到了事务A未提交的数据，事务A回滚，事务B读到的数据就是脏数据
  • 不可重复读：在同一个事务中对同一个数据读多次读到的结果不一致，通常是因为两次读之间其他事务修改了该数据
  • 幻读 ：事务B以相同的条件读多次数据读到的条数不一致，通常是因为在多次读之间别的事务insert了数据导致多次读到相同条件的数据条数不一样
  • 丢失更新 ：事务并发写的情况B事务的update覆盖了A事务的update，这叫覆盖丢失更新，还有一种是数据回滚丢失更新。

MyQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

  • 读未提交（READ UNCOMMITTED） : 简称 RU ，可以读到别的事务未提交的数据

  • 读提交 （READ COMMITTED） ：简称RC ，可以读到别的事务提交了的数据，不能读到未提交的数据

  • 可重复读 （REPEATABLE READ）：简称 RR ，同一个事务中多次读取同一个数据结果应该是一致的。

  • 串行化 （SERIALIZABLE）：加锁机制让事务串行执行，绝对安全，事务相互不会有任何影响

这四种隔离级别约束了事务与事务之间的影响程度，不同的隔离级别解决了不同的事务并发问题。在不同的业务场景中我们需要做选择，Mysql 默认的事务隔离级别是RR可重复读。

2.脏读

脏读就是读到了脏数据，有可能是因为某事务读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了不一定最终存在的数据，这就是脏读。如果事务的隔离级别是 set global transaction isolation level read uncommitted RU读未提交就会产生脏读，下面用一个图理解脏读
在这里插入图片描述
解释：读未提交就是可以读到别的事务中未提交的事务，事务B查询select * from user 把事务A未提交的数据 age = 10 查询出来了，一旦事务A回滚，那么age = 10就是脏数据，在数据库中根本不存在。

要解决脏读需要把事务隔离级别提升为 set global transaction isolation level read committed; RC 读提交，这意味着事务B只能读到事务A已经提交的数据，不能读到未提交的数据，但是RC这种隔离级别会产生 不可重复读或幻读的问题

3.不可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的，那不可重复读就是一个事务多次读取到的数据是不一致的，通常是因为事务B在做同一个数据多次读取之前，别的事务修改了该数据导致的，RU，RC 隔离级别下都有可能产生不可重复读问题。
在这里插入图片描述
上图中事务B就产生了不可重复读，在两次 select * from user 之间事务A修改了数据，导致在事务B中，2次读到的数据不一致，可以通过设置隔离级别为 set global transaction isolation level repeatable read; RR来解决不可重复读问题。这也是Mysql默认的事务隔离策略，但是该事务隔离级别(RR)没办法解决幻读问题

重要

然而：在Mysql-InnoDB的RR事务隔离级别下其实是不会产生幻读的，在之前讲锁的部分我们说过，因为Mysql通过间隙锁解决了幻读问题。它通过锁住当前查询数据的间隙，不允许别的事务往这个间隙中insert数据，那自然就能解决幻读问题了。

4.幻读

幻读和不可重复读有点类似，前者是读到了别的事务修改的数据，后置是读到了别的事务插入的数据。比如：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，在事务A中发现了事务B 新插入的数据 称为幻读 , 即：事务A两次读取相同条件的数据读到的条数不一样 下面理解一下幻读
在这里插入图片描述
在事务A第二次执行select * from user的时候，把事务B insert的数据也读取出来了，这就是幻读。2次以相同的条件读取一批数据读到的数量不一致，要解决幻读可以把事务隔离级别设置为：串行化 （SERIALIZABLE），这种隔离级别是最安全的，它通过加锁让多个事务以串行的方式执行，然而性能也是最差的，一般不用。

5.丢失更新

脏读，幻读，不可重复读都是发生在一个事务读，另外一个事务写的情况下，而丢失更新发生在两个事务写的情况下。丢失更新分为一类丢失更新(回滚丢失更新)，二类丢失更新(覆盖丢失更新)，上面在讲解乐观锁的时候已经讲解过覆盖丢失更新(二类丢失更新)问题，解决方案：悲观锁，乐观锁都可以。
在这里插入图片描述

丢失更新还有一个种情况，回滚丢失更新(一类丢失更新)，我们以甲乙两人同时操作同一账户里的钱来举这个例子：

时刻甲乙
t1查询到账户余额为100元
t2查询到账户余额为100元
t3花费10块钱买了个棒棒糖
t4提交，账户余额为90元花费20块买了个铁锤
t5后悔了，取消订单，事务回滚，账户余额为100元

以上的例子很容易解释清楚第一类丢失更新，也就是 A事务回滚时，把已经提交的B事务的更新数据覆盖了。但是这种丢失更新已经被数据库消灭掉了， 我们在使用的过程中是不会遇到了。通过设置隔离级别可以防止 Repeatable Read

6.总结

下面我们用一个表格来总结一下事务并发问题和隔离级别的关系

隔离级别脏读不可重复读幻读
读未提交(Read uncommitted)可能可能可能
读提交(Read committed)不可能可能可能
可重复读(Repeatable reads)不可能不可能可能
串行化(Serializable)不可能不可能不可能

对于读未提交而言什么安全措施都没做，可能会出现各种问题，一般不用，对于串行化是直接加锁让事务串行执行，性能比较低不建议用，多数情况下我们使用Mysql默认的隔离级别即可。

四.事务隔离级别MVCC

1.什么是MVCC

对于RC,RR事务隔离级别利用-MVCC机制实现，MVCC指的是多版本并发控制”(Multi Version Concurrency Control)。大致含义是为一个行记录数据生成多个版本的快照数据，这些快照数据在undo log中。 如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。这样做的优势是可以读写分离，减少锁的冲突，大大提高了数据库的并发能力。

2.MVCC快照版本链

undo log日志存储在 表空间的 ibdata 文件中，当一个事务对记录做了变更操作就会产生undo log，也就是说undo log记录了记录变更的逻辑过程。当一个事务要更新一行记录时，会把当前记录当做历史快照保存下来，多个历史快照会用两个隐藏字段trx_id(事务ID)和roll_pointer(回滚指针)串起来，形成一个历史版本链。可以用于MVCC和事务回滚。比如多个事务对id为1的数据做了更新，会形成如下图这种历史版本链：

  • trx_id ： 事务ID，一次事务会产生一个版本日志
  • roll_pointer : 回滚指针，指向上一个版本undo log日志
    在这里插入图片描述

比如：数据库account.amount = 100，事务ID为1,，现在事务2修改 amount=200,那么会先生成一个修改前的 undolog 日志版本，然后再修改表记录数据，并把roll_pointer指向undolog 日志，如果再来一个事务3修改amount=300,那么会基于最新版本的undolog 日志生成一个新的undolog 日志，他的roll_pointer会指向上一个版本的undolog日志，这样就形成版本链。表记录的roll_pointer指向的是最新版本的undolog 日志。

每次对数据的变更都会产生一个undo log，当一条记录被变更多次时，那么就会产生多条undo log，undo log记录的是变更前的日志，并且每个undo log的序号是递增的，那么当要回滚的时候，按照序号依次向前推，就可以找到我们的原始数据了。

对于delete，insert ，update 会有些区别，undo log日志中会记录回滚类型(delete,insert,update)，比如：我们执行一次insert into ... account 操作，就会生成一条insert undo log，并且数据的回滚指针会指向它。undo log会记录undo log的序号、插入主键的列和值…，那么在进行rollback的时候，通过主键直接把对应的数据删除即可

比如：我们执行一次update ... account操作，就会生成一条update undo log，会把老的记录写入新的undo log，表记录的回滚指针指向新的undo log，需要回滚的时候就找到老数据重新update还原即可

比如：我们执行一次delete ... acount操作，就会生成一条 delete undo log 日志，日志会把老数据保存，需要回滚的时候就找到要还原的数据重新insert即可。

有了多版本日志可以实现并发读，提升性能，那么这个版本链是如何执行的，假如我们有一条ID为1的数据，现在多个事务对其做修改操作，那么每次修改都会：基于一个版本的undo log日志生成一行新的undo log 日志，把值修改掉，然后当前版本的undo log 的 roll_pointer(回滚指针)指向了上一个版本的undo log 日志形成链条，如果需要回滚就通过这个roll_pointer找到上一个版本的undo log 日志进行回滚。

3.RR和RC的区别

对于读已提交 RC 和 可重复读RR 在MVCC版本链中查找版本日志是不一样的，读已提交每次在读的时候会去版本链中找有：事务提交标志的那一条日志也就是对应数据库最新的那条日志，因为事务已经提交，这样的话读已提交每次都可以读到提交事务后的最新数据，
在这里插入图片描述
而对于可重复读而言，每次读的都是当前事务对应的那条版本日志，即使后续有其他事务生成了新的版本日志，RR也只会读当前事务对应的版本日志，这样的话在同一个日志中多次读到的都是相同的数据。
在这里插入图片描述

4.当前读和快照读

在MVCC中，有两种读操作：当前读和快照读。

  • 当前读：读取最新数据，要求获取最新的数据，且看到其他事务尚未提交的修改。
  • 快照读：不需要最新数据，只需要事务开始时一致的数据版本。

比如在RR级别下，多次读操作都是读取的是当前版本的数据这个就是快照读，快照读指的是每次读取Undolog版本链快照的数据，但是如果在当前事务中对该数据发生了修改操作，那么会读取最新的数据进行更新(当前读)，后续再读的话读到的就是最新的数据了。

5.Readview视图

在MVCC中还有一个很重要的概念：一致性视图read-view,它决定了当前事务到底能不能查询到某个版本的undog log日志数据，ReadView是由当前事务执行查询时:所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成 ，在ReadView中会形成 3 个区间 ①小于min_id的区间 ， ② 大于min_id小于 max_id ③大于max_id 。
在这里插入图片描述
如果当前事务的 trx_id < min_id ，也就是绿色部分，表示该版本的日志已经提交，对当前事务是可见的

如果当前事务的trx_id > max_id ，也就是红色部分，表示事务未开始，版本日志对当前事务不可见

如果当前事务trx_id 处于之间，也就是黄色部分，如果当前事务trx_id处于readview数组中，表示事务还没提交，版本日志对当前事务不可见

如果当前事务trx_id 处于之间，也就是黄色部分，如果当前事务trx_id不处于readview数组中，表示事务已经提交，则可见

readview和可见性算法其实就是记录了sql查询那个时刻数据库里提交和未提交所有事务的状态。
要实现RR隔离级别，事务里每次执行查询操作readview都是使用第一次查询时生成的readview，也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见，当然可以实现每次查询的可重复读的效果了。

要实现RC隔离级别，事务里每次执行查询操作readview都会按照数据库当前状态重新生成readview，也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见，当然实现的就是每次都能查到已提交的最新数据效果了。

文章到这里就结束了，建议收藏，随便给个好评。

墨家巨子@俏如来
关注
专栏目录
MySQL锁机制和优化
zhangtangzhao的博客
10-30 446
MySQL锁机制和SQL优化
MySQL 海量数据优化（理论+实战） 吊打面试官
Trouvailless的博客
05-20 2938
一、准备表数据 咱们建一张用户表，表中的字段有用户ID、用户名、地址、记录创建时间，如图所示 OK，接下来准备写一个存储过程插入一百万条数据 CREATE TABLE `t_user` ( `id` int NOT NULL, `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `create_ti
MySQL锁的优化
wangxucumt的专栏
10-08 125
转载至http://see.xidian.edu.cn/cpp/html/1481.html 一、获取锁等待情况 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺： mysql> show status like 'Table%'; +----------------------------+---------...
Mysql高级篇（中）——锁机制
最新发布
weixin_44666786的博客
09-27 1331
本篇博文主要介绍了MySQL中锁的相关概念、工作原理、各自的优缺点、使用场景、注意事项及锁监控策略，让大家对MySQL中的锁有更清晰的认识
事务和锁
weixin_30670965的博客
04-11 221
事务和锁 闪四清 简述 空间管理 概念和特点 ...
mysql之锁优化
qq_41820066的博客
10-05 602
mysql之锁优化
mysql 锁状态优化_mysql 锁优化
weixin_42512936的博客
01-19 198
一.myisam存储引擎锁优化1.合理理由读写优先级MyISAM 的表锁，写互相阻塞的表锁，默认系统是写优先，可改为读有先：low_priority_updates=1如果我们的系统是一个以读为主，而且要优先保证查询性能的话，可以通过设置系统参数选项low_priority_updates=1，将写的优先级设置为比读的优先级低，即可让告诉MySQL 尽量先处理读请求。若系统需要有限保证数据写入的性...
MySQL面试题大全（吊打面试官）.rar
02-04
本资料“MySQL面试题大全（吊打面试官）”涵盖了从基础到高级的各类面试问题，旨在帮助求职者全面了解MySQL的面试知识点。 1. **基础概念** - **SQL**：了解SQL语言的基本语法，包括查询、插入、更新、删除数据的...
吊打面试官的MySQL灵魂100问.pdf
01-07
用回答问题的方式,让自己对知识点的理解...此文不会事无巨细的从 select 的用法开始讲解 mysql,主要针对的是开发人员需要知道的一些 MySQL 的知识点,主要包括索引,事务,优化等方面,以在面试中高 频的问句形式给出答案
吊打面试官的MySQL灵魂100问
02-29
- 数据管理便捷，支持多种数据操作和事务管理功能。 - 缺点：相比内存存储，有一定的性能开销。 **1.2 SQL简介** - **定义：**SQL（Structured Query Language，结构化查询语言）是一种用于管理关系型数据库的...
MySQL锁机制及其优化
高云平的博客
07-18 381
文章目录概述锁的定义innodb中锁的类型latch的优化MySQL表锁的优化innodb行锁的优化死锁优化结语 概述 在一般的数据库驱动的业务中，很大的一个难点就是：在最大程度地利用数据库的并发访问的同时，还要确保每个用户能以一致的方式读取和修改数据，为此，MySQL就有了锁（locking）的机制。频繁出现的锁的不仅本身消耗着资源，也影响着数据库的运行性能，因此，做好数据库的锁优化，对于数据库...
Mysql查询正在执行的事务以及等待锁的操作方式
12-16
使用navicat测试学习: 首先使用set autocommit = 0;(取消自动提交,则当执行语句commit或者rollback执行提交事务或者回滚) 在打开一个执行update 查询 正在执行的事务： SELECT * FROM information_schema.INNODB_TRX 根据这个事务的线程ID（trx_mysql_thread_id）: 从上图看出对应的mysql 线程：一个94362 （第二个正在等待锁）另一个是93847(第一个update 正在执行 没有提交事务) 可以使用mysql命令：kill  线程id       杀掉线程 期间如果并未杀掉
MySQL优化系列16-优化MySQL的锁
只是甲的博客
07-20 4692
备注:测试数据库版本为MySQL 8.0 文章目录一. 内部锁定方法1.1 行锁1.2 表锁1.3 选择锁定类型二.表锁问题2.1 性能考虑优先考虑InnoDB2.2 锁定性能问题的解决方法三. 并发插入四.元数据锁4.1 元数据锁获取4.2 元数据锁释放五. 外部锁参考: 一. 内部锁定方法 本节讨论内部锁定;也就是说，在MySQL服务器内部执行锁定，以管理多个会话对表内容的争用。这种类型的锁定是内部的，因为它完全由服务器执行，不涉及其他程序。 1.1 行锁 MySQL为InnoDB表使用行级锁来支持多个
mysql数据库锁优化和注意 -- [转一篇很好的文章]
我的笔记
04-11 3904
前言 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接应想到一个数据库系统的并发处理能力 和性能，所以锁定机制的实现也就成为了各种数据库的核心技术之一。本章将对MySQL 中两种使用最为频繁的存储引擎MyISAM 和Innodb 各自的锁定机制进行较为详细的分析。 7．1 MySQL 锁定机制简介 数据库锁定机制简单来说就是数据库为了保证数据的 一致性而使各种共享资源在被
mysql性能优化之- 合理利用锁机制优化MySQL
taotaobaobei的博客
01-19 452
MyISAM表锁优化建议 对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的 附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到，所以造成锁定资源 的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。 所以，在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别 是不可能改变的了，...
mysql 锁优化
Timeinsist的博客
01-17 311
20.1 概述 mysql锁分类 表级锁：开销小，枷锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 行级锁：开销大，枷锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发性度最高。 页面锁：开销和加锁时间节约表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发性一般； 存储引擎对锁的支持 **锁冲突：**例如说事务A将某几行上锁后，事务B又对其上锁，锁不能共存否则...
数据库事务和锁——INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_TRX表的简单介绍
隔壁老猪
01-14 1339
INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_TRX是MYSQL中事务和锁相关的表。通常我们遇到事务超时或锁相关问题时，直接运行下面SQL语句即可进行简单检查： --查看事务 select * from information_schema.INNODB_TRX; --查看锁 select * from information_schema.INNODB_...
mysql锁优化_mysql锁以及配置优化
weixin_33171271的博客
02-07 283
1. 看有没有锁等待SHOW STATUS LIKE 'innodb_row_lock%';2. 查看哪个事务在等待(被阻塞了)USE information_schemaSELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';trx_id : 事务ID号trx_state : 当前事务的状态trx_mysq...
MySQL中一些查看事务和锁情况的常用语句
热门推荐
刀刀的专栏
10-22 1万+
一些查看数据库中事务和锁情况的常用语句查看事务等待状况：SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blo
评论 8
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

墨家巨子@俏如来

你的鼓励是我最大的动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值