MySQL——事务
核心知识:
1.什么是事务?一组原子性的SQL查询语句
2.事务的四种属性:ACID
3.四种隔离级别:读取未提交内容、读取提交内容、重复读、串行化。
4.什么是幻读?幻读有那些解决办法?连续读取同一数据得到不同的结果就是幻读,InnoDB通过多版本并发控制机制来解决幻读问题。
5.什么是死锁?怎样解决死锁问题?一般有那些手段?死锁是一种现象;死锁必须有部分事务回滚;
6.MyISAM和内存表没有事务。事务型引擎有哪些?
1.什么是事务
事务是一组原子性的SQL查询语句,也可以被看作一个工作单元,如果数据库引擎能够成功地对数据库应用所有的查询语句,
它就会执行所有查询,但是,如果任何一条查询语句因为崩溃或其他原因而无法执行,那么所有语句就不会执行。
也就是说,事务内的语句要么全部执行,要么一句也不执行。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#MySQL默认是AUTOCOMMIT(自动提交),所以每一个SQL语句都算作一个事务。 #现在表是这样的 +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 0 | 周杰 | 男 | | 1 | 科比 | 男 | | 2 | 毛线 | 男 | | 3 | 小鸟 | 男 | | 4 | 蒋大爷 | 男 | | 5 | 秦子琪 | 女 | +----+-----------+------+ #客户端1,如下操作 mysql> start transaction; #开启一个事务 Query OK, 0 rows affected (0.01 sec) mysql> insert into score values(6,'魏武','女'); #添加一条数据 Query OK, 1 row affected (0.00 sec) #没有显示开启一个事务之前,提交成功就能查看 #查看客户端2 +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 0 | 周杰 | 男 | | 1 | 科比 | 男 | | 2 | 毛线 | 男 | | 3 | 小鸟 | 男 | | 4 | 蒋大爷 | 男 | | 5 | 秦子琪 | 女 | #没有添加进去 +----+-----------+------+ #为什么,因为客户端那边的事务并没有结束,所以没有写入到磁盘中 #客户端1结束事务 mysql> COMMIT; #结束事务 #再次查看客户端2 +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 0 | 周杰 | 男 | | 1 | 科比 | 男 | | 2 | 毛线 | 男 | | 3 | 小鸟 | 男 | | 4 | 蒋大爷 | 男 | | 5 | 秦子琪 | 女 | | 6 | 魏武 | 女 | +----+-----------+------+ 如果一个事务没有完成,没有完成可能是由于中途出错,自动回滚,也可能是由于没有提交,导致数据没有同步到数据库中。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#客户端1 mysql> start transaction; #开启一个事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into score values(7,'魏文','男'); Query OK, 1 row affected (0.00 sec) mysql> updatee score set name = '魏大棒子' where id = 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'updatee score set name = '魏大棒子' where id = 8' at line 1 #出错了 mysql> COMMIT; #结束错误 Query OK, 0 rows affected (0.02 sec) #已经成功的SQL语句,因为事务没有完成并没有写入到数据库 #客户端2(并没写入到数据库) +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 0 | 周杰 | 男 | | 1 | 科比 | 男 | | 2 | 毛线 | 男 | | 3 | 小鸟 | 男 | | 4 | 蒋大爷 | 男 | | 5 | 秦子琪 | 女 | | 6 | 魏武 | 女 | | 7 | 魏文 | 男 | +----+-----------+------+
2.事务的四种属性(ACID)
原子性(Atomicity)
一个事务必须被视为一个单独的内部“不可分”的工作单元,以确保整个事务要么全部执行,要么全部回滚。
当一个事务具有原子性时,该事务绝对不会被部分执行,要么完全执行,要么根本不执行。
一致性(Consistency)
数据库总是从一种一致性状态转换到另一种一致性状态,不会出现半成品状态的出现。
如果最终事务根本没有被提交,任何事务处理过程中所做的数据改变,也不会影响到数据库的内容。
隔离性(Lsolation)
某个事务的结构只有在完成之后才对其他事务可见。
也就是一个事务执行完毕之后才会执行另外一个事务,多个事务之间不会相互干扰。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#客户端1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from score; +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 0 | 周杰 | 男 | | 1 | 科比 | 男 | | 2 | 毛线 | 男 | | 3 | 小鸟 | 男 | | 4 | 蒋大爷 | 男 | | 5 | 秦子琪 | 女 | | 6 | 魏武 | 女 | | 7 | 魏文 | 男 | +----+-----------+------+ 8 rows in set (0.00 sec) mysql> update score set name = "魏小武" where id = 6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> #并没有结束事务 #客户端2,我要再去改那一行没有提交的记录 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update score set name = '魏大武' where id = 6; #先等别人完成,超过一定时间就会报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #当一个事务对一行记录进行修改的时候会给它上锁,只有这个事务结束之后,才会给它解锁,其他事务没有进不去只能等着,等不下去了就走了。
持久性(Durability)
一旦一个事务提交,事务所做的数据改变将是永久的。
这意味着数据改变已被记录,即使系统崩溃,数据也不会丢失。
持久性是个有点模糊的概念,因为实际上持久性也分很多级别。
有些持久性策略提供了一种强壮的安全保证,另一些则未必,没有什么东西是 100%永远持久的。
3.隔离级别
隔离的问题比想象的要复杂。SQL标准定义了4类隔离级别,包括一些具体规则,用来限定事务内外的哪些改变是可变的,那些是不可变的。
低级别的隔离级别一般支持更高的并发处理,并拥有更低的系统开销。需要注意的是,每种存储引擎实现的隔离级别略有不同。
READ UNCOMMITTED(读取未提交内容)
所有事务都可以“看到”未提交事务的执行结果。在这种级别上,可能会产生很多问题,读取未提交数据,也被称为“脏读”(Dirty Read)。
READ COMMITTED(读取提交内容)
大多数数据库系统的默认隔离级别是READ COMMITTED(但这不是MySQL默认的)。
它满足了隔离的早先简单定义:一个事务在开始时,只能“看见”已经提交事务所做的改变,一个事务从开始到提交前,所做的任何数据改变都是不可见的,除非已经提交。这种隔离级别也支持所谓的“不可重复读”。
REPEATABLE READ(可重读):MySQL默认的隔离级别。
REPEATBLE READ隔离级别解决了READ UNCOMMITTED隔离级导致的问题。它确保同一事务的多个实例在并发读取事务时,会“看到同样的”数据行。
不过理论上,这会导致另一个棘手问题:幻读(Phantom Read)。
幻读指当用户读取某一范围的数据行时,另一事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会有“幻影”行。
也可以理解为连续读取的同一数据结果不同,InnoDB和Falcon存储引擎通过多版本并发控制机制解决了幻读问题。
SERIALIZABLE(可串行化):查询和修改不能同时进
SERIALIZABLE时最高级别的隔离级,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。
简言之,SERIALIZABLE是在每个读的数据行上加锁。在这个级别上,可能导致大量的超时现象和锁竞争现象。
隔离级别 | 脏读可能性 | 不可重复度可能性 | 幻读可能性 | 枷锁读 |
READ UNCOMMITTED | 是 | 是 | 是 | 否 |
READ COMMITTED | 否 | 是 | 是 | 否 |
REPEATABLE READ | 否 | 否 | 是 | 否 |
SERIALIZABLE | 否 | 否 | 否 | 是 |
4.死锁
死锁是指两个或多个事务在同一资源上互相占用,而导致的恶性循环现象。
当多个事务以不同的顺序试图加锁同一资源时,就会产生死锁。
任何时间同时加锁一个资源,一定产生死锁。
例如,设想下列两个事务同事处理person表:
事务1 START TRANSACTION; UPDATE person SET p_sal = 10000 WHERE p_id = 'p002'; UPDATE person SET p_sal = 3000 WHERE p_id = 'p003'; COMMIT 事务2 START TRANSACTION; UPDATE person SET p_sal = 4000 WHERE p_id = 'p003'; UPDATE person SET p_sal = 12000 WHERE p_id = 'p002'; COMMIT
如果很不凑巧,每个事务在处理过程中,都执行了第一个查询,更新了数据行,也加锁了该行数据。
接着每个事务都视图更新第二个数据行,却发现该行已被(对方)加锁,然后两个事务都开始等待对方完成,陷入无限等待中,除非有外部因素介入,才能解除死锁。
为了解决这种问题,数据库系统实现了各种死锁检测和死锁超时机制。
对于更复杂的系统,例如InnoDB存储引擎,可以预知循环相关性,并立刻返回错误(在执行前会检查是否存在冲突)。
这种解决方式实际很有效,否则死锁将导致很慢的查询。
其他的解决方式,是让查询达到一个锁等待超时时间,然后在放弃争用,但这个方法不够好。
目前InnoDB处理死锁的方法是,回滚拥有最少排他行级锁的事务(一种对最易回滚事务的大致估算,这样对整个事务系统的影响最小)。
锁现象和锁顺序是因为存储引擎而异的,某些存储引擎可能会因为使用某种顺序的语句导致死锁,其他的却不会。
死锁现象具有双重性;有些是因为真实的数据冲突产生的,无法避免,有些则是因为存储引擎的工作方式导致的。
如果不以部分或全部的方式回滚某个事物,死锁将无法解除。在事务性的系统中,这是无法更改的事实。
用户在涉及应用时,就应该考虑这种问题的处理。许多事物在事务开始时,可以简单的判定,决定重做事务
5.MySQL中的事务
MySQL提供了3个事务性存储引擎:InnnoDB、NDB Cluster和Falcon。
还有几个第三方引擎也支持事务处理,目前最知名的第三方事务性引擎是soliDB和PBXT。
(1)AUTOCOMMIT(自动提交)
MySQL默认操作模式是AUTOCOMMIT模式。这意味着除非显示的开始一个事务,否则它将把每个查询视为一个单独事务自动执行。
在当前连接中,可以通过变量设置,启动(enable)和禁用(disable)AUTOCOMMIT模式:
mysql> show variables like 'AUTOCOMMIT'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | #默认开启 +---------------+-------+ 1 row in set (0.00 sec)
值1和ON是等效的,同样,0和OFF也是等效的。如果设置AUTOCOMMIT=0,用户将一致处在某个事务中,直到用户执行一条COMMIT或ROLLBACK语句之后,MySQL将立即开始一个新事务。
对于非事务型的表,如MyISAM表或内存表(Memory Table),改变AUTOCOMMIT值没有意义,这些表本质上一直操作在AUTOCOMMIT模式。
某些命令,在一个活动事务中一旦执行,会在这些事务显示提交之前,直接触发MySQL立即提交当前事务。
MySQL允许使用SET TRANSACTION ISOLATION LEVEL命令设置隔离级别,新的隔离级别将在下一个事务开始后生效。用户也可以在配置文件中为整个服务器设置隔离级别;或者使用下列命令,只会为当前会话设置隔离级别。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
MySQL可以识别所有的4个ANSI标准隔离级别,InnoDB引擎也支持所有的隔离级别。其他的存储引擎对隔离级别的支持,则因不同的隔离级而异。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#查看存储引擎 mysql> show create table score; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | score | CREATE TABLE `score` ( `id` int(11) NOT NULL, `name` char(4) DEFAULT NULL, `sex` enum('男','女') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into score values(10,'余白沙','男'); Query OK, 1 row affected (0.00 sec) #没有提交 #客户端2 mysql> SELECT * FROM score; +----+--------------+------+ | id | name | sex | +----+--------------+------+ | 0 | 周杰 | 男 | | 1 | 科比 | 男 | | 2 | 毛线 | 男 | | 3 | 小鸟 | 男 | | 4 | 蒋大爷 | 男 | | 5 | 秦子琪 | 女 | | 6 | 魏小武 | 女 | | 7 | 魏文 | 男 | | 8 | 阳明先生 | 男 | | 9 | 王阳明 | 男 | | 10 | 余白沙 | 男 | #事务已经提交 +----+--------------+------+ 11 rows in set (0.00 sec)
在一个事务中,如果混合使用事务性表和非事务型表(例如InnoDB和MyISAM表),假如事务处理一切顺利,那么结果也会正常。
但是,如果事务回滚,那么在非事务表上所做的修改将无法取消,这将导致数据处于不一致的状态。
在这种状态下,很难对数据进行修复,并且事务会变得悬而未决,因此选择合适的存储引擎非常重要。
(2)隐式和显式锁定
InnoDB使用二相锁定协议。一个事务在执行过程中的任何时候,都可以获得锁,但只有在执行COMMIT或ROLLBACK语句后,才可以释放这些锁。它会同时释放掉所有锁。
前文描述的锁机制都是隐式锁定。InnoDB会根据用户的隔离级别,自动处理锁定。
不过,InnoDB也支持显式锁,例如以下语句:
SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE
MyISAM也支持LOCK TABLES和UNLOCK TABLES命令,这些命令有MySQL服务器实现,而不是由存储引擎。
LOCK TABLES命令与事务处理之间的交互作用比较复杂。
因此除非是在一个事务中使用LOCK TABLES,同时AUTOCOMMIT模式被禁止,否则,无论使用何种存储引擎,都不要使用LOCK TABLES命令。