mysql数据库的4中隔离级别详解

众所周知MySQL从5.5.8开始Innodb就是默认的存储引擎Innodb最大的特点是支持事务、支持行级锁。 既然支持事务那么就会有处理并发事务带来的问题更新丢失、脏读、不可重复读、幻读相应的为了解决这四个问题 就产生了事务隔离级别未提交读(Read uncommitted)已提交读(Read committed)可重复读(Repeatable read)可序列化(Serializable)。下面通过事例一一阐述它们的概念与联系1.事务的特性原子性:指处于同一个事务中的多条语句是不可分割的。即它对数据库的修改要么全部执行要么全部不执行一致性事务必须使数据库从一个一致性状态变换到另外一个一致性状态。比如转账转账前两个账户余额之和为2k转账之后也应该是2K。隔离性指多线程环境下一个线程中的事务不能被其他线程中的事务打扰持久性事务一旦提交就应该被永久保存起来。事务是干什么用的?:事务是访问和更新数据库的程序执行单元事务中包含一个或者多个SQL语句这些语句要么都执行要么就都不执行也就是要么都成功要么都不成功2.处理并发事务出现问题的概念说明更新丢失最后的更新覆盖了其他事务之前的更新而事务之间并不知道发生更新丢失。更新丢失可以完全避免应用对访问的数据加锁即可。脏读(针对未提交的数据)指当一个事务正在访问数据并且对数据进行了修改而这种数据还没有提交到数据库中这时另外一个事务也访问这个数据然后使用了这个数据。因为这个数据还没有提交那么另外一个事务读取到的这个数据我们称之为脏数据。依据脏数据所做的操作肯能是不正确的。例子1.财务将董震的工资从1000修改成了8000但未提交事务2.此时应董震读取自己的工资发现自己的工资变成了8000高兴的上蹦下跳3.接着财务发现操作有误回滚了事务此时董震的工资又变成了1000此时董震记取的工资8000是一个 脏数据不可重复读(读取数据本身的对比)指在一个事务内多次读同一数据。在这个事务还没有执行结束另外一个事务也访问该同一数据那么在第一个事务中的两次读取数据之间由于第二个事务的修改第一个事务两次读到的数据可能是不一样的这样就发生了在一个事物内两次连续读到的数据是不一样的这种情况被称为是不可重复读。幻读(读取结果集条数的对比)幻读是指当事务不是独立执行时发生的一种现象例如第一个事务对一个表中的数据进行了修改这种修改涉及到了表中的全部数据行。同时第二个事务也修改了这个表中的数据这种修改是向表中插入一行新数据。那么以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行就好像发生了幻觉一样例子目前公司员工工资为1000的有10人1.事务1读取所有的员工工资为1000的员工。2.这时事务2向employee表插入了一条员工纪录工资也为10003.事务1再次读取所有工资为1000的员工共读取了11条记录。这就是一个幻读3.事务隔离级别并发处理带来的问题中更新丢失可以完全避免由应用对数据加锁即可。脏读、不可重读度、幻读其实都是数据库的一致性问题必须由一定的事务隔离机制来解决。 其中一种方法是不用加锁通过一定的机制生成一个数据请求时间点的一致性快照并用这个快照来提供一个界别的一致性读取。从用户的角度看好像是数据库提偶拱了 统一数据的多个版本。这种技术叫做数据库多版本并发控制MVCC 多版本数据库。事务隔离的本质是使事务在一定程度上串行化执行显然和并发机制是矛盾的。数据库的事务隔离越严格并发负作用越小代价越高(影响并发访问)。为了解决隔离和并大的矛盾IOS SQL92规定了4个隔离级别。(隔离串行)隔离级别MVCC 使用方式特点未提交读 READ UNCOMMITTED不使用 MVCC直接读取最新数据已提交读 READ COMMITTED每次 SELECT 创建新 Read View避免脏读可重复读 REPEATABLE READ事务首次 SELECT 创建 Read View后续复用避免不可重复读可序列化 SERIALIZABLE不使用 MVCC使用锁完全串行化大多数数据库默认的事务隔离级别是 已提交读(Read Committed)比如 SQL Server , Oracle。但 MySQL 的默认隔离级别是 可重复读(Repeatable Read)未提交读 Read uncommitted未提交读顾名思义就是一个事务可以读取另一个未提交事务的数据。事例老板要给程序员发工资程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字按成3.9万/月该钱已经打到程序员的户口但是事务还没有提交就在这时程序员去查看自己这个月的工资发现比往常多了3千元以为涨工资了非常高兴。但是老板及时发现了不对马上回滚差点就提交了的事务将数字改成3.6万再提交。分析实际程序员这个月的工资还是3.6万但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。因此在这种隔离级别下查询是不会加锁的也由于查询的不加锁所以这种隔离级别的一致性是最差的可能会产生“脏读”、“不可重复读”、“幻读”。如无特殊情况基本是不会使用这种隔离级别的。那怎么解决脏读呢Read committed(已提交读)能解决脏读问题。已提交读Read Committed已提交读顾名思义就是只能读到已经提交了的内容事例程序员拿着信用卡去享受生活卡里当然是只有3.6万当他买单时程序员事务开启收费系统事先检测到他的卡里有3.6万就在这个时候程序员的妻子要把钱全部转出充当家用并提交。当收费系统准备扣款时再检测卡里的金额发现已经没钱了第二次检测金额当然要等待妻子转出金额事务提交完。程序员就会很郁闷明明卡里是有钱的…分析这就是已提交读若有事务对数据进行更新UPDATE操作时读操作事务要等待这个更新操作事务提交后才能读取数据可以解决脏读问题。但在这个事例中出现了一个事务范围内两个相同的查询却返回了不同数据这就是不可重复读。这是各种系统中最常用的一种隔离级别也是SQL Server和Oracle的默认隔离级别。这种隔离级别能够有效的避免脏读但除非在查询中显示的加锁如select * from T where id1lock in share mode; select * from T where id1 for update;不然普通的查询是不会加锁的。那为什么“已提交读”同“未提交读”一样都没有查询加锁但是却能够避免脏读呢这就要说道另一个机制“快照snapshot”而这种既能保证一致性又不加锁的读也被称为“快照读Snapshot Read”假设没有“快照读”那么当一个更新的事务没有提交时另一个对更新数据进行查询的事务会因为无法查询而被阻塞因为上了X锁即写锁所以不能得到S锁即读锁这种情况下并发能力就相当的差。而“快照读”就可以完成高并发的查询不过“读提交”只能避免“脏读”并不能避免“不可重复读”和“幻读”。那怎么解决可能的不可重复读问题Repeatable read(可重复读)可重复读(Repeated Read)可重复读顾名思义就是专门针对“不可重复读”这种情况而制定的隔离级别自然它就可以有效的避免“不可重复读”。而它也是MySql的默认隔离级别。事例程序员拿着信用卡去享受生活卡里当然是只有3.6万当他埋单时事务开启不允许其他事务的UPDATE修改操作收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。分析重复读可以解决不可重复读问题。写到这里应该明白的一点就是不可重复读对应的是修改即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作而不是UPDATE操作。什么时候会出现幻读事例程序员某一天去消费花了2千元然后他的妻子去查看他今天的消费记录全表扫描FTS妻子事务开启看到确实是花了2千元就在这个时候程序员花了1万买了一部电脑即新增INSERT了一条消费记录并提交。当妻子打印程序员的消费记录清单时妻子事务提交发现花了1.2万元似乎出现了幻觉这就是幻读。在这个级别下普通的查询同样是使用的“快照读”但是和“已提交读”不同的是当事务启动时就不允许进行“修改操作Update”了而“不可重复读”恰恰是因为两次读取之间进行了数据的修改因此“可重复读”能够有效的避免“不可重复读”但却避免不了“幻读”因为幻读是由于“插入或者删除操作Insert or Delete”而产生的。那怎么解决幻读问题Serializable(可序列化)可序列化 (Serializable)这是数据库最高的隔离级别这种级别下事务“串行化顺序执行”也就是一个一个排队执行。这种级别下“脏读”、“不可重复读”、“幻读”都可以被避免但是执行效率奇差性能开销也最大所以基本没人会用。MySql如何解决幻读问题1什么是幻读前提条件InnoDB引擎可重复读隔离级别使用当前读时。在一次事务里面多次查询之后结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行表现一个事务(同一个read view)在前后两次查询同一范围的时候后一次查询看到了前一次查询没有看到的行。两点需要说明1、在可重复读隔离级别下普通查询是快照读是不会看到别的事务插入的数据的幻读只在当前读下才会出现。2、幻读专指新插入的行读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。2幻读产生的原因行锁只能锁住行即使把所有的行记录都上锁也阻止不了新插入的记录。3如何解决幻读在快照读读情况下mysql通过mvcc来避免幻读在当前读读情况下mysql通过next-key来避免幻读四.什么是快照读、当前读1.快照读(1).原理读取专门的快照 (对于RC快照ReadView会在每个语句中创建。对于RR快照是在事务启动时创建的)简单的select操作即可(不需要加锁,如: select ... lock in share mode, select ... for update)针对的也是select操作- 在MVCC中查询看到的不是最新数据 -- 而是事务开始时的数据快照 SELECT * FROM users; -- 这是快照读2.实现机制以PostgreSQL为例数据行结构-- 每行数据的隐藏系统字段 ( xmin, -- 创建此版本的事务ID xmax, -- 删除/更新此版本的事务ID ctid, -- 当前元组的物理位置 ... )使用快照读来避免脏读场景银行转账操作表结构CREATE TABLE accounts ( id INT PRIMARY KEY, name VARCHAR(50), balance DECIMAL(10, 2) ); INSERT INTO accounts VALUES (1, Alice, 1000.00), (2, Bob, 500.00);案例1脏读的避免过程-- 事务T1转账事务 BEGIN; -- 事务ID: 1001 -- 步骤1从Alice账户扣款 UPDATE accounts SET balance 900.00 WHERE id 1; -- 此时创建新版本V2: (xmin1001, xmaxnull) -- 老版本V1: (xmin500, xmax1001) -- 事务还未提交-- 事务T2查询事务读提交隔离级别 BEGIN; -- 事务ID: 1002 -- 获取快照记录活跃事务[1001] -- 步骤2查询Alice余额 SELECT balance FROM accounts WHERE id 1; -- 可见性检查 -- 最新版本V2: xmin1001在活跃事务列表中 -- 回滚找到V1: xmin500不在活跃列表中可见 -- 返回结果1000.00未提交的修改看不到 COMMIT;-- 事务T1继续 -- 步骤3向Bob账户加款 UPDATE accounts SET balance 600.00 WHERE id 2; -- 步骤4提交 COMMIT; -- 此时T1从事务列表中移除-- 事务T3提交后查询 BEGIN; -- 事务ID: 1003 -- 快照中的活跃事务[] SELECT balance FROM accounts WHERE id 1; -- 可见性检查 -- 版本V2: xmin1001不在活跃列表中可见 -- 返回结果900.00已提交的修改 COMMIT;当前读读取最新版本的记录, 没有快照。 在InnoDB中当前读取根本不会创建任何快照。select ... lock in share modeselect ... for updateinsertupdatedelete针对如下操作, 会让如下操作阻塞:insertupdatedelete在RR(可重复读)级别下, 快照读是通过MVVC(多版本控制)和undo log来实现的,当前读是通过手动加record lock(记录锁)和gap lock(间隙锁)来实现的。所以从上面的显示来看如果需要实时显示数据还是需要通过加锁来实现。这个时候会使用next-key技术来实现总结为什么会出现“脏读”因为没有“select”操作没有规矩。为什么会出现“不可重复读”因为“update”操作没有规矩。为什么会出现“幻读”因为“insert”和“delete”操作没有规矩。“读未提Read Uncommitted”能预防啥啥都预防不了。“读提交Read Committed”能预防啥使用“快照读Snapshot Read”避免“脏读”但是可能出现“不可重复读”和“幻读”。“可重复读Repeated Red”能预防啥使用“快照读Snapshot Read”锁住被读取记录避免出现“脏读”、“不可重复读”但是可能出现“幻读”。“串行化Serializable”能预防啥有效避免“脏读”、“不可重复读”、“幻读”不过执行效率奇差性能开销也最大所以基本没人会用