为什么需要事务
现在的很多软件都是多用户,多程序,多线程的,对同一个表可能同时有很多人在用,为保持数据的一致性,所以提出了事务的概念。
A 给B 要划钱,A 的账户-1000元, B 的账户就要+1000元,这两个update 语句必须作为一个整体来执行,不然A 扣钱了,B 没有加钱这种情况很难处理。
什么是事务
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
事务用来管理DDL、DML、DCL操作,比如 insert,update,delete 语句,默认是自动提交的。
什么存储引擎支持事务
1.查看数据库下面是否支持事务(InnoDB支持)?
show engines;
2.查看mysql当前默认的存储引擎?
show variables like '%storage_engine%';
3.查看某张表的存储引擎?
show create table 表名 ;
4.对于表的存储结构的修改?
建立InnoDB 表:
Create table .... type=InnoDB; Alter table table_name type=InnoDB;
事物的特性
一般来说,事务是必须满足4个条件(ACID):
Atomicity(原子性)
Consistency(稳定性、一致性)
Isolation(隔离性)
Durability(可靠性、持久性)
原子性(atomicity):一个事务是一个不可分割的工作单位,事务中包括的操作要么都做,要么都不做。
一致性(consistency):事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性(isolation):一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
持久性(durability):持久性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
原子性(atomicity)
一个事务必须被视为一个不可分割的最小单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作。
比如: 张三给李四转账
1、张三的账户扣除500元
2、李四的账户增加500元
不能出现张三的账户扣除500元,但李四的账户没有增加500元的情况。要么全部成功,要么全部失败。
一致性(consistency)
一致性是指事务将数据库从一种一致性转换到另外一种一致性状态,在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。
比如:
1、张三的账户扣除500元
2、李四的账户增加500元
但是由于某种原因导致李四的账户增加1000元,这就不符合一致性。不能因为任何原因,导致李四收到两次钱。
持久性(durability)
一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,已经提交的修改数据也不会丢失。
隔离性(isolation)
隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其他事务是不可见的。
事务并发问题
在事务的并发操作中可能会出现一些问题:
丢失更新:一个事务更新之后,另一个事务也更新了,但是第二个事务回滚了,则第一个事务也被回滚了。
脏读:一个事务读取到另一个事务尚未提交的数据。
假设张三的工资原本是1000元,财务人员在某一时刻将他的工资改为了5000元,但这个修改事务并没有提交,此时张三读取自己的工资,发现工资变成了5000,很高兴。但是随后,财务人员发现操作有误,回滚了事务,这是张三的工资又变成了1000元。
像这样,张三读取的工资数5000就是一个脏数据。如果在第一个事务提交前,任何其他事务不可读取其修改过的值,则可以避免该问题。
不可重复读:一个事务因读取到另一个事务已提交的数据。导致对同一条记录读取两次以上的结果不一致。update操作
在事务1中,张三读取了自己的工资为1000元,但针对工资的操作并没有完成。在另外的一个事务中,财务人员修改了张三的工资为2000元,并提交了事务。这时在事务1中,张三再次读取自己的工资时,工资就变成了2000元,这就造成了两次读的数据不一致了。具体的解决方法是,只有在修改事务完全提交之后,才允许读取数据。
幻读:一个事务因读取到另一个事务已提交的数据。导致对同一张表读取两次以上的结果不一致。insert、delete操作
在事务1中,我们读取了10条工资是1000元的员工记录,这是事务2又插入了一条员工记录,工资也是1000元,那么事务1再次以“工资是1000元的员工”作为查询条件读取时,就会返回11条记录。解决方法是,在操作事务完成数据处理之前,任何其他事物都不可以添加新数据,则可避免该问题。
事物的隔离级别
为了避免上面出现的几种情况,在标准SQL规范中,定义了4个事务隔离级别,不同的隔离级别对事务的处理不同。
现在来看看MySQL数据库为我们提供的四种隔离级别(由低到高):
Read uncommitted (读未提交):最低级别,任何情况都无法保证。
Read committed (读已提交):可避免脏读的发生。
Repeatable read (可重复读):可避免脏读、不可重复读的发生。
Serializable (串行化):可避免脏读、不可重复读、幻读的发生。
默认隔离级别
大多数数据库的默认隔离级别是Read committed(读已提交),比如Oracle、DB2等。
MySQL数据库的默认隔离级别是Repeatable read(可重复读)。
如何查看和设置隔离级别
在MySQL数据库中查看当前事务的隔离级别:
select @@tx_isolation;
在MySQL数据库中设置事务的隔离级别:
set [glogal | session] transaction isolation level 隔离级别名称;
set tx_isolation=’隔离级别名称;
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
set SESSION TRANSACTION ISOLATION LEVEL read committed;
set SESSION TRANSACTION ISOLATION LEVEL repeatable read;
set SESSION TRANSACTION ISOLATION LEVEL serializable;
注意事项
隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。
对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取,而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题,在可能出现这类问题的个别场合,可以由应用程序采用悲观锁或乐观锁来控制。
事务隔离级别为可重复读时,如果有索引(包括主键索引)的时候,以索引列为条件更新数据,会存在间隙锁间、行锁、页锁的问题,从而锁住一些行;如果没有索引,更新数据时会锁住整张表。
事务隔离级别为串行化时,读写数据都会锁住整张表。
隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大,对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read Committed,它能够避免脏读取,而且具有较好的并发性能。
事务语法
开启事务
1、begin
2、START TRANSACTION(推荐)
3、begin work
事务回滚
rollback
事务提交
commit
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。