首页/应用软件/内容

MySQL InnoDB4个事务级别与脏读、不重复读、幻读是什么

应用软件2022-10-30 阅读()
SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言。在使用它时,只需要发出“做什么”的命令,“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便,已经成为了数据库操作的基础,并且现在几乎所有的数据库均支持SQL。

1、MySQL InnoDB事务隔离级别脏读、可重复读、幻读

MySQL InnoDB事务的隔离级别有四级,默认是“可重复读”(REPEATABLE READ)。

· 1).未提交读(READUNCOMMITTED)。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据(脏读)( 隔离级别最低,并发性能高 )

· 2).提交读(READCOMMITTED)。本事务读取到的是最新的数据(其他事务提交后的)。问题是,在同一个事务里,前后两次相同的SELECT会读到不同的结果(不重复读)。会出现不可重复读、幻读问题(锁定正在读取的行)

· 3).可重复读(REPEATABLEREAD)。在同一个事务里,SELECT的结果是事务开始时时间点的状态,因此,同样的SELECT操作读到的结果会是一致的。但是,会有幻读现象(稍后解释)。会出幻读(锁定所读取的所有行)。

· 4).串行化(SERIALIZABLE)。读操作会隐式获取共享锁,可以保证不同事务间的互斥(锁表)。


四个级别逐渐增强,每个级别解决一个问题。

· 1).脏读。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据。

· 2).不重复读。解决了脏读后,会遇到,同一个事务执行过程中,另外一个事务提交了新数据,因此本事务先后两次读到的数据结果会不一致。

· 3).幻读。解决了不重复读,保证了同一个事务里,查询的结果都是事务开始时的状态(一致性)。但是,如果另一个事务同时提交了新数据,本事务再更新时,就会“惊奇的”发现了这些新数据,貌似之前读到的数据是“鬼影”一样的幻觉。

具体地:

1). 脏读

首先区分脏页和脏数据

脏页是内存的缓冲池中已经修改的page,未及时flush到硬盘,但已经写到redo log中。读取和修改缓冲池的page很正常,可以提高效率,flush即可同步。脏数据是指事务对缓冲池中的行记录record进行了修改,但是还没提交!!!,如果这时读取缓冲池中未提交的行数据就叫脏读,违反了事务的隔离性。脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。

2). 不可重复读

是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,第二个事务已经提交。那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。例如,一个编辑人员两次读取同一文档,但在两次读取之间,作者重写了该文档。当编辑人员第二次读取文档时,文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档,则可以避免该问题

3). 幻读 :

是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。例如,一个编辑人员更改作者提交的文档,但当生产部门将其更改内容合并到该文档的主复本时,发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前,任何人都不能将新材料添加到文档中,则可以避免该问题。

2、隔离级别实验

以下实验基于博主MySQL Server 5.6

首先创建一个表,如下:

USE test;  
CREATE TABLE `t` (  
  
  `a` int(11) NOT NULL PRIMARY KEY  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


2.1、实验一:解释脏读、可重复读问题


事务A READ-UNCOMMITTED

事务B READ-COMMITTED,

事务C-1 REPEATABLE-READ

事务C-2 REPEATABLE-READ

事务D SERIALIZABLE

set autocommit =0;

start transaction ;

start transaction;

insert into t(a)values(4);

select * from t;

1,2,3,4(脏读:读取到了未提交的事务中的数据)

select * from t;

1,2,3(解决脏读)

select * from t;

1,2,3

select * from t;

1,2,3

select * from t;

1,2,3

commit;

select * from t:

1,2,3,4

select * from t:

1,2,3,4

select * from t:

1,2,3,4 (与上面的不在一个事务中,所以读到为事务提交后最新的,所以可读到4)

select * from t:

1,2,3(重复读:由于与上面的在一个事务中,所以只读到事务开始事务的数据,也就是重复读)

select * from t:

1,2,3,4

commit(提交事务,下面的就是一个新的事务,所以可以读到事务提交以后的最新数据)

select * from t:

1,2,3,4

READ-UNCOMMITTED 会产生脏读,基本很少适用于实际场景,所以基本不使用。


2.2、实验二:测试READ-COMMITTED与REPEATABLE-READ

事务A

事务B READ-COMMITTED

事务C REPEATABLE-READ

set autocommit =0;

start transaction ;

start transaction;

start transaction;

insert into t(a)values(4);

select * from t;

1,2,3

select * from t;

1,2,3

commit;

select * from t:

1,2,3,4

select * from t:

1,2,3(重复读:由于与上面的在一个事务中,所以只读到事务开始事务的数据,也就是重复读)

commit(提交事务,下面的就是一个新的事务,所以可以读到事务提交以后的最新数据)

select * from t:

1,2,3,4

REPEATABLE-READ可以确保一个事务中读取的数据是可重复的,也就是相同的读取(第一次读取以后,即使其他事务已经提交新的数据,同一个事务中再次select也并不会被读取)。

READ-COMMITTED只是确保读取最新事务已经提交的数据。

当然数据的可见性都是对不同事务来说的,同一个事务,都是可以读到此事务中最新数据的。如下,

  1. start transaction;  
    insert into t(a)values(4);  
    select *from t;    
    1,2,3,4;  
    insert into t(a)values(5);  
    select *from t;  
    1,2,3,4,5;


2.3、实验三:测试SERIALIZABLE事务对其他的影响


事务A SERIALIZABLE

事务B READ-UNCOMMITTED

事务C READ-COMMITTED,

事务D REPEATABLE-READ

事务E SERIALIZABLE

set autocommit =0;

start transaction ;

start transaction;

select a from t union all select sleep(1000) from dual;

insert into t(a)values(5);

insert into t(a)values(5);

insert into t(a)values(5);

insert into t(a)values(5);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

SERIALIZABLE 串行化执行,导致所有其他事务不得不等待事务A结束才行可以执行,这里特意使用了sleep函数,直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒,而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。

SERIALIZABLE是相当严格的串行化执行模式,不管是读还是写,都会影响其他读取相同的表的事务。是严格的表级读写排他锁。也就失去了innodb引擎的优点。实际应用很少。


2.4、实验四:幻读

一些文章写到InnoDB的可重复读避免了“幻读”(phantom read),这个说法并不准确。做个实验:(以下所有试验要注意存储引擎和隔离级别)

  1. CREATE TABLE `t_bitfly` (

  2. `id` bigint(20) NOT NULL default '0',

  3. `value` varchar(32) default NULL,

  4. PRIMARY KEY (`id`)

  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  6. select @@global.tx_isolation, @@tx_isolation;

  7. +-----------------------+-----------------+

  8. (北联网教程,专业提供视频软件下载)

    第1页  第2页  第3页  第4页  第5页  第6页  第7页  第8页  第9页  第10页  第11页  第12页  第13页  第14页  第15页  第16页  第17页  第18页  第19页  第20页  第21页  第22页  第23页  第24页  第25页  第26页  第27页  第28页  第29页  第30页  第31页  第32页  第33页  第34页  第35页  第36页  第37页  第38页  第39页  第40页  第41页  第42页  第43页  第44页  第45页  第46页  第47页  第48页  第49页  第50页  第51页  第52页  第53页  第54页  第55页  第56页  第57页  第58页  第59页  第60页  第61页  第62页  第63页  第64页  第65页  第66页  第67页  第68页  第69页  第70页  第71页  第72页  第73页  第74页  第75页  第76页  第77页  第78页  第79页  第80页  第81页  第82页  第83页  第84页  第85页  第86页  第87页  第88页  第89页  第90页  第91页  第92页  第93页  第94页  第95页  第96页  第97页  第98页  第99页  第100页  第101页  第102页  第103页  第104页  第105页  第106页  第107页  第108页  第109页  第110页  第111页  第112页  第113页  第114页  第115页  第116页  第117页  第118页  第119页  第120页  第121页  第122页  第123页  第124页  第125页  第126页  第127页  第128页  第129页  第130页  第131页  第132页  第133页  第134页  第135页  第136页  第137页  第138页  第139页  第140页  第141页  第142页  第143页  第144页  第145页  第146页  第147页  第148页  第149页  第150页  第151页  第152页  第153页  第154页  第155页  第156页  第157页  第158页  第159页  第160页  第161页  第162页  第163页  第164页  第165页  第166页  第167页  第168页  第169页  第170页 

    ……

相关阅读