Mysql事务及ACID详解
事务及其ACID属性
- 原子性:事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
- 一致性:在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规
则都必须应用于事务的修改,以保持数据的完整性。(和原子性差不多。当事务结束之后,事务里的数据状态要保持一直。sql操作要么全部都做完,要么全部都失败,针对与数据层面讲) - 隔离性:数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独
立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。(一个事务不被另一个事务影响) - 持久性:事务完成后,它对于数据地修改是永久的,即使系统出现故障也能保持
并发事务带来的问题
- 脏写或更新丢失:最后的事务更新覆盖了前面事务所进行的更新
例子:A事务和B事务开始同时读取库存10,接着A事务对库存进行操作,将库存减到了5,而此时B事务也对库存进行了操作,将库存减到了9,而正确的库存应该是4,这时候数据就出现了问题 - 脏读:事务A读到了事务B修改但未提交的数据
例子:事务A对库存10进行操作将库存减5,此时B事务读取到的库存为5,B事务继续对库存进行操作将库存减4,此时库存为1。但是,**此时事务A并未提交,而是进行了回滚操作,**此时库存将会变为10,所以A事务用到了B事务的脏数据(不考虑事务隔离级别)。 - 不可重复读:事务A内部的相同查询语句在不同时刻读出的结果不同,不符合隔离性
例子:A事务第一次查询的库存为10,在没有做任何操作的情况下,在同一个事务内进行第二次相同的查询,库存变为了9,第一次和第二次查询的结果不同,就是不可重复读。 - 幻读:事务A读取到事务B提交的数据,不符合隔离性
例子:事务A第一次查询id<=5的数据集时只有2条记录,此时事务B插入了一条新的数据,那么在第二次查询<=5的数据集却出现了三条记录。事务A第一次读和第二次读的数据集数量不同,这就是幻读
Mysql事务隔离级别详解
事务隔离级别
“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | 可能 | 可能 | 可能 |
读已提交 | 不可能 | 可能 | 可能 |
可重复读 | 不可能 | 不可能 | 可能 |
可串行化 | 不可能 | 不可能 | 不可能 |
可重复读由于MVCC机制,在读取数据的时候没有加锁。而可串行化会在任何查询语句上都加一把锁,当事务A查询数据时,所查到的数据都加一把锁(查出多少条记录就锁多少条记录,过滤掉的记录还可以修改),这时候事务B想要进行sql操作,就必须得等事务A结束
常看当前数据库的事务隔离级别: show variables like ‘tx_isolation’
设置事务隔离级别:set tx_isolation=‘REPEATABLE-READ’;
Mysql默认的事务隔离级别是可重复读,用Spring开发程序时,如果不设置隔离级别默认用Mysql设置的隔离级别,如果Spring设置了就用已经设置的隔离级别
Mysql锁机制详解
是什么:锁是计算机协调多个进程或线程并发访问某一资源的机制。
锁分类:
-
从性能上分分为:乐观锁,悲观锁
-
从对数据库操作的类型分为:读锁和写锁(都属于悲观锁)
读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排它锁):当前写操作没有完成时,会阻止其他读锁和写锁 -
从数据操作的颗粒度分为:行锁和表锁
表锁:每次操作锁住整张表,消耗小,加锁快,不会出现死锁;锁定颗粒度大,发生锁冲突的概率最高,并发度最低,一般用在表迁移的场景。
行锁:每次锁住操作的行,消耗大,加锁慢,会出现死锁;锁定颗粒度小,发生锁冲突概率最低,并发度高
InnoDB与MYISAM的最大不同有两点:
-
InnoDB支持事务(TRANSACTION)
-
InnoDB支持行级锁
一个session开启事务更新不提交,另一个session更新同一条记录会阻塞,更新不同记录不会阻塞
总结:
- MyISAM在写操作时会自动给锁涉及的表加读锁,在进行更新、插入、删除操作的时候会自动给设计的表加写锁
- InnoDb在执行查询语句时不会加行锁(串行化隔离级别除外),但是在在进行更新、插入、删除操作的时候会加写锁
- 一句话:读锁会阻塞写锁,写锁会阻塞读锁和写锁
间隙锁
- 是什么:锁的是数据集中2条数据之间的空隙。
- 作用:在可重复读的隔离级别下,也有可能解决幻读,就是使用间隙锁。
比如:
如上图现在有3个区间:(1,5),(5,11),(11,正无穷)
在sessionA中执行 : update user set name = ‘dd’ where id >3 and id <7。因为3在(1,5)区间,7在(5,11),那么其他session无法在(1,11]这个区间的所有的记录以及记录之间的间隙里插入或修改数据。间隙锁是在可重复读隔离级别下才会生效。
临键锁(Next-Key Locks)
Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(1,11]的整个区间可以叫做临键锁。
无索引行锁会升级成表锁
锁主要加载索引上,如果对非索引字段进行更新,那么行锁可能就变成表锁。
SessionA执行:update user set name = ‘dd’ where account = ‘333’;
SessionB对表任何操作都会阻塞。
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且这个索引不能失效,否则会从行锁升级为表锁
行锁分析:
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
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:系统启动后到现在总共等待的次数
尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,
然后根据分析结果着手制定优化计划。
Mysql锁优化
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能减少检索条件范围,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行
- 尽可能低级别事务隔离