本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • MySQL基础教程(9)MySQL事务、隔离级别与锁

MySQL基础教程(9)MySQL事务、隔离级别与锁

发布:TangLu2020-1-4 11:38分类: MySQL 标签: mysql

一、MySQL事务

1、事务的四大特性(简称ACID)

原子性(atomicity):原子是不可再切分的,所以事务的原子性也就是说事务开始后所有操作要么全部执行,要么全部不执行,不应该有部分执行的情况存在。如果事务在执行过程中出错(包含会话断开、服务宕机)会由undo log(记录数据修改前的状态)回滚到事务开始前的状态。

一致性(consistency):事务开始前和结束后,完整性约束没有被破坏。比如A向B转账1000,不应存在A扣除1000后而B没有增加的情况。由redo log(记录数据修改后的状态)来保持一致性。

隔离性(isolation):同一个时间内只允许一个事务对同一个数据进行处理,不同事务之间彼此不能有干扰。 

持久性(durability):事务完成后数据应该落盘,能够用久保存,并且不能再rollback回滚。


2、事务控制语句。需要注意事务通常是针对DML语句而言,因为每个DDL都是单独的事务,如果DML中加有DDL会触发自动提交

start transaction | begin ;  #放在DML语句的头部
rollback  #放在SQL最后,可以回滚所有事务
commit #放在SQL最后代表提交事务


3、事务的自动提交功能

当数据库设置autocommit=1时(默认为1),即便没有加begin语句开启事务,数据库也会自动开启,并且最后自动进行commit。若涉及到交易场景建议关闭事务的自动提交,但需要注意长事务问题。非交易类的场景可以打开该功能。


二、MySQL的事务隔离级别

1、MySQL的4种事务隔离级别

读未提交(read uncommitted):最低的隔离级别,在一个事务中可以读取到另一个事务已修改但尚未提交的结果。该隔离级别容易造成脏读、不可重复读、幻读,所以不建议使用。

读已提交(read commited):只有事务提交后,其更新结果才可以被其他事务查询。Oracle、SQL Server默认隔离级别,推荐使用。该隔离级别解决了RU级别的脏读问题,但还是会出现不可重复读、幻读。

可重复读(repeatable read)Innodb引擎的默认隔离级别。事务1反复读取同一份数据,其结果应该总是相同的,即便事务2已经修改了数据并提交,但是在事务1中依然不会有变化。该隔离级别解决了脏读、不可重复读以及大部分幻读的发生。

串行化(serializable):事务串行化处理,不会出现脏读、幻读、不可重复读的问题,隔离级别最高,但性能最差。


2、脏读、不可重复度与幻读

脏读:在事务1中查询到的数据是事务2已经修改过但未提交的,一旦事务2发生回滚,那事务1获取到的数据就是无效的

不可重复度:在事务1中对一个数据进行多次查询,因为事务2的修改导致事务1得到的结果是不一样的。

幻读:在一个事务更新过程中,有其他事务抢先更新了数据,导致最终结果和预想不一致。比如事务1对年龄<30的数据进行修改,事务2在事务1提交之前又新增了一个<30的数据,这个时候事务1提交之后会看到依然有一个<30的数据存在,给开发者带来修改失败的幻觉。


3、MySQL事务隔离级别的设置

#查看事务隔离级别
show global variables like 'tx_isolation';
#在线设置事务隔离级别
set global tx_isolation='READ-COMMITTED'
#永久生效
vi /etc/my.cnf
tx_isolation=READ-COMMITTED'


三、MySQL锁

锁可以保证事务之间的隔离性,也保证了数据的一致性。被上锁的资源不会被其他事务修改。锁是属于资源的而不是某个事务的特性。



1、锁的分类与区别

全局锁:对整个数据库加读锁,当需要让整个数据库处于只读状态时才会加全局读锁,比如需要对使用非InnoDB引擎的数据库做全库逻辑备份。加锁后其他线程的增删改语句、建表、修改表结构语句都会被阻塞。

mysql > flush tables with read lock


表锁:一般是在数据库引擎不支持行锁的情况下才会用到表锁,如果程序里有locak tables这样的语句就需要注意更换引擎或者代码了。锁定整张表,开销小,加锁快,不会出现死锁,但是发生冲突概率高,并发低。适用于查询为主、少量更新的应用,如WEB应用。


元数据锁:修改元数据时会触发的锁,会对整个表进行上锁,比如DDL语句

行锁:当2个事务对同一行数据进行更新的时候会出现行锁。开销大,加锁慢,会出现死锁,冲突概率低,并发度高。适合于一些在线事务处理系统

页锁:介于表锁和行锁之间,会出现死锁

间隙锁(GAP锁):对一个事务修改的数据中的空隙上锁,只会在RR隔离级别才会有这种锁,可以防止幻读。比如事务1执行update test set num=10 where num < 10,如果表中存在1,3,5,10这几个值,那么被锁的范围应该是10-5,5-3,3-1。这个时候去执行insert into test values (8)是会被锁住的


2、锁超时时间相关配置

lock_wait_timeout = 1800  #默认是1年,非常不合理,建议设置为1800秒足够
innodb_lock_wait_timeout= 10  #行锁超时时间,默认是50秒,建议调低 

查看当前产生锁的类型以及具体事务

select * from sys.innodb_lock_waits \G


温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《MySQL基础教程(9)MySQL事务、隔离级别与锁
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-560.html  [生成短网址]

已有 0/262 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~