杭州 做网站,百度大数据,建站步骤图,多用户商城思维导图☘️博主介绍☘️#xff1a; ✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux#xff0c;也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章#xff0c;并且也会默默的点赞收藏加关注❣… ☘️博主介绍☘️ ✨又是一天没白过我是奈斯DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注❣️❣️❣️ 今天作为新一年的第一篇文章与大家分享关于MySQL事务和隔离级别的知识。在数据库管理系统中事务是确保数据完整性和一致性的关键机制。通过事务我们可以将多个数据库操作组合成一个逻辑单元要么全部执行要么全部不执行从而确保数据的正确性和一致性。 而隔离级别则是控制多个事务并发执行时如何相互影响的关键因素。不同的隔离级别提供了不同的数据可见性和并发性能需要根据具体的业务需求和性能要求来选择合适的隔离级别。 一个逻辑工作单元要成为事务必须满足所谓的ACID原子性、一致性、隔离性、持久性属性。
原子性Atomicity事务开始后所有操作要么全部做完要么全部不做不可能停滞在中间环节。事务执行过程中出错会回滚到事务开始前的状态所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体就像化学中学过的原子是物质构成的基本单位。
一致性Consistency事务开始前和结束后数据库的完整性约束没有被破坏。比如A向B转账不可能A扣了钱B却没收到。
隔离性Jsolation同一时间只允许一个事务请求同一数据不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱在A取钱的过程结束前B不能向这张卡转账。
持久性Durability事务完成后事务对数据库的所有更新将被保存到数据库不能回滚。小结原子性是事务隔离的基础隔离性和持久性是手段最终目的是为了保持数据的一致性
总结原子性是事务隔离的基础隔离性和持久性是手段最终目的是为了保持数据的一致性。 目录
案例设置4种隔离级别分析对mysql的影响。建议设置为read-committed事务级别
级别一读未提交read-uncommitted一个事务可以读到另一个事务未提交的结果为脏数据
级别二读已提交read-committed只有在事务提交后其结果才会被其他事务看见
级别三可重复读repeatable-read无论事务对数据是否进行操作事务是否提交对于同一份数据的读取结果总是相同的。只有退出会话事务才能同步数据
级别四串行化serializable隔离级别最高牺牲了系统的并发性。就是锁表不是行锁事务修改表时并没有提交禁止其他所有事务连接当前表 通过现象反映隔离级别效果 01更新丢失lost update当系统允许两个事务同时更新同一数据是发生更新丢失。例事务A将数值改为1并提交事务B将数值改为2并提交。这时值变为2。不算问题正常情况 02脏读dirty read当一个事务读取另一个事务尚未提交的修改时产生脏读。 03不可重复读non-repeatable read同一查询语句在同一事务中多次进行如果这个过程中其他事务提交了所做的修改或删除会发生每次返回不同的结果集此时发生非重复读那么就是意味着同一事务执行完全相同的select语句时可能看到不一样的结果。导致这种情况的原因可能有 (1)有一个交叉的事务有新的commit导致了数据的改变 (2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit多个commit提交时只读一次出现结果不一致 04幻读phantom read出现幻读的情况是由于并发事务引起的。在同一个事务内对于相同的查询条件在两次查询之间新增或删除了符合条件的数据导致第二次查询结果与第一次查询结果不一致的情况。解决幻读的办法 (1)调整事务隔离级别为串行化 (2)通过间隙锁和next-key locks而间隙锁和next-key locks只有在隔离级别为可重复读或以上才有二八定律RR级别: 20%的事务存在幻读;80%的事务不存在幻读的风险。在可重复读级别下查询加上for update后如果有数据返回就是行锁没有数据就加上间隙锁和next-lock key锁住一个范围不允许其他事务DML只能自己才能DML。 例事务A改了未提交事务B改其他A再查。 A把所有‘100’改为‘200’B把所有‘50’改为‘100’。A查询100发现还有数据产生幻读。 RR 级别下存在幻读的可能但也是可以使用对记录手动加 X锁RR模式下的X锁会同时进行间隙锁和next-key locks来防止幻读 的方法消除幻读。SERIALIZABLE 正是对所有事务都加 X锁 才杜绝了 幻读 四种隔离级别MySQL默认Repeated Read建议改为read committed 1read uncommitted最低的隔离级别一个事务可以读到另一个事务未提交的结果为脏数据。 2read committed (DEFAULT)只有在事务提交后其更新结果才会被其他事务看见解决了更新丢失、脏读。Oracle、db2、sql server默认的隔离级别 3Repeated Read(重复读)MySQL默认隔离级别。在一个事务中对于同一份数据的读取结果总是相同的无论是否有其他事务对这份数据进行操作以及这个事务是否提交它确保同一事务的多个实例在并发读取数据时看到同样的数据行只有退出会话同事务才能同步数据。解决了更新丢失、脏读、不可重复读。RR 级别下存在幻读的可能解决幻读的办法 (1)调整事务隔离级别为串行化 (2)通过间隙锁和next-key locks而间隙锁和next-key locks只有在隔离级别为可重复读或以上才有二八定律RR级别: 20%的事务存在幻读;80%的事务不存在幻读的风险。在可重复读级别下查询加上for update后如果有数据返回就是行锁没有数据就加上间隙锁和next-lock key锁住一个范围不允许其他事务DML只能自己才能DML 4Serializable(串行化)事务串行化执行隔离级别最高牺牲了系统的并发性。可以解决并发事务的所有问题。就是X锁表不是行锁一个事务修改表时并没有提交禁止其他所有事务连接当前表。SERIALIZABLE 正是对所有事务都加 X锁 才杜绝了 幻读但很多场景下我们的业务 sql 并不会存在 幻读 的风险。SERIALIZABLE 的一刀切虽然事务绝对安全但性能会有很多不必要的损失。故可以在 RR 下根据业务需求决定是否加锁存在幻读风险我们加锁不存在就不加锁事务安全与性能兼备这也是 RR 作为 mysql默认隔是个事务离级别的原因所以需要正确的理解 幻读。 read committed和Repeated Read在处理并发事务时的区别 1Read Committed 隔离级别 每个读操作只能看到已经提交的事务所做的更改而不能看到其他未提交的事务所做的更改。 事务在读取数据时会对每一行数据加共享锁直到读操作完成才会释放锁定。 2Repeatable Read 隔离级别 在事务开始后所有的查询都只能看到在该事务开始之前已经提交的数据不会看到其他事务所做的更改。 事务在读取数据时会对整个表加共享锁直到事务结束才会释放锁定。 总结 Read Committed 隔离级别只保证读取已提交的数据可以避免脏读但可能出现不可重复读和幻读。 Repeatable Read 隔离级别通过在事务期间锁定读取的数据可以避免不可重复读但仍可能出现幻读。 根据具体的业务需求和并发环境来选择合适的隔离级别如果需要更高的数据一致性和读取的稳定性可以选择 Repeatable Read 隔离级别。如果对一致性要求相对较低需要更好的并发性能可以选择 Read Committed 隔离级别。 隔离级别相关参数
mysql show variables like %tx_isolation%; ---默认REPEATABLE-READ对数据不安全。建议修改为READ-COMMITTED 设置隔离级别
mysql set global tx isolationREAD-COMMITTED|READ-UNCOMMITTED|REPEATABLE-READ|SERIALIZABLE;
注在会话和全局级别修改参数都不会永久修改参数。永久修改参数只能将参数添加到my.cnf文件然后重启生效。添加参数transaction-isolationREAD-COMMITTED 案例设置4种隔离级别分析对mysql的影响。建议设置为read-committed事务级别
级别一读未提交read-uncommitted一个事务可以读到另一个事务未提交的结果为脏数据 mysql set global tx_isolationREAD-UNCOMMITTED; ---影响所有会话但重启失效 mysql show variables like %tx_isolation%; mysql set global autocommit0; ---关闭自动提交功能。这里只是为了测试所以关闭了自动提交功能默认开启 会话一 mysql create table tb(id int,name varchar(20));
mysql insert into tb values (1,itpux1);
mysql select * from tb; 会话二 mysql select * from tb; ---其他会话查到未提交事务的数据 级别二读已提交read-committed只有在事务提交后其结果才会被其他事务看见 mysql set global tx_isolationREAD-COMMITTED; ---影响所有会话但重启失效 mysql show variables like %tx_isolation%; mysql set global autocommit0; ---关闭自动提交功能。这里只是为了测试所以关闭了自动提交功能默认开启 会话一 mysql create table tb(id int,name varchar(20));
mysql insert into tb values (1,itpux1);
mysql select * from tb; 会话二 mysql select * from tb; 会话一 mysql commit; 会话二 mysql select * from tb; ----事务提交后其他事务才能读取数据 级别三可重复读repeatable-read无论事务对数据是否进行操作事务是否提交对于同一份数据的读取结果总是相同的。只有退出会话事务才能同步数据 mysql set global tx_isolationREPEATABLE-READ; ---影响所有会话但重启失效 mysql show variables like %tx_isolation%; mysql set global autocommit0; ---关闭自动提交功能。这里只是为了测试所以关闭了自动提交功能默认开启 会话一 mysql select * from tb; mysql update tbset id1000 where nameitpux1;
mysql commit; 会话二会话一打开时确保会话二一同打开 mysql select * from itpux; [rootmysql2 ~]# mysql -u root -p ---退出会话相当于结束一个事务。然后重新登录一个会话同事务
mysql select * from tb; ---查询到其他事务提交的事务 级别四串行化serializable隔离级别最高牺牲了系统的并发性。就是锁表不是行锁事务修改表时并没有提交禁止其他所有事务连接当前表 mysql set global tx_isolationSERIALIZABLE; ---影响所有会话但重启失效 mysql show variables like %tx_isolation%; mysql set global autocommit0; ---关闭自动提交功能。这里只是为了测试所以关闭了自动提交功能默认开启 会话一 mysql select * from tb; mysql update itpux set id9999 where nameitpux1; 会话二 mysql select * from tb; ----查询没有响应。事务级别为串行化事务没结束之前对操作的对象进行锁表 会话一 mysql commit; ---结束事务提交事务 会话二 mysql select * from tb; ---事务完成后其他事务才能读取数据 总结串行化serializable事务隔离级别只有读读之间可以并发读写/写读/写写都要阻塞进行锁表。也就是no MVCC多版本并发控制 好啦今天的内容结束了希望这边文章可以让大家对事务和隔离级别有所了解。
