— 事务;
在MySQL中,只有那些使用事务存储引擎(如InnoDB)的表才支持事务;这些语句不会对非事务存储引擎所管理的表产生任何明显影响;
— 事务流程图;
银行转账的流程, 比如由事务来保证;
— ACID;
1.定义:
1.Atomic(原子性):整个事务事务中的所有的操作要不全都成功,要不全都取消;
2.Consistent(一致性):在事务开始之前和事务结束以后,数据的完整性约束没有被破坏;
3.Isolated(隔离性):两个事务的执行是互不干扰的;
4.Durable(持久性):事务完成以后,该事务对数据库所有的操作便持久的保存在数据库中,不会被回滚;
2.缺点:事务处理能够更有力地保证数据库操作的结果,但同时也需要更多的CPU周期,内存和磁盘空间开销;事务属性对于某些应用程序来说是必不可少的,但并非所有应用程序都如此,您可以选择对您的应用程序最有意义的事务属性;
3.使用场景:
1.金融类操作通常需要使用事务,保证数据完整性会比增加开销所带来的成本更重要;
2.另一方面,如果某个应用程序用于记录通过网页访问数据库表的操作,则因服务器主机出现故障而丢失几条记录可能是允许的;
— 事务SQL控制语句;
1.SAVEPOINT:事务中可以回滚到的位置的唯一标识符;
2.ROLLBACK TO SAVEPOINT:回滚之后,savepoint仍然存在,从而可以“重用”它;
3.RELEASE SAVEPOINT:不会删除任何事务语句;
— SQL控制语句流程:示例;
mysql> SET AUTOCOMMIT = ON;
mysql> CREATE TABLE t_trans(id INT, comment VARCHAR(50));
mysql> BEGIN;
mysql> SAVEPOINT sv_bgn;
mysql> INSERT INTO t_trans VALUES(1, ‘first record!’);
mysql> SELECT * FROM t_trans;
mysql> SAVEPOINT sv_ins;
mysql> INSERT INTO t_trans VALUES(2, ‘second record!’);
mysql> UPDATE t_trans SET comment = ‘after update’ WHERE id = 2;
mysql> SAVEPOINT sv_upd;
mysql> SELECT * FROM t_trans;
mysql> ROLLBACK TO sv_ins;
mysql> SELECT * FROM t_trans;
mysql> ROLLBACK TO sv_bgn;
mysql> SELECT * FROM t_trans;
mysql> COMMIT;
TIPS:
1.START TRANSACTION或BEGIN:事务打开,直到通过COMMIT或ROLLBACK显式关闭;
2.事务结束之后/ROLLBACK到保存点之前的保存点都会被自动删除;
— AUTOCOMMIT模式;
1.启用AUTOCOMMIT后,每个语句仍会以原子方式执行(隐式提交);而不是没有事务;
2.可以通过在插入多个行时比较违反约束限制的效果,便可看出启用AUTOCOMMIT和根本不具有事务之间的差别;在非事务表(如MyISAM)中,一旦发生错误,语句就会终止,已经插入的行会保留在该表中;而对于InnoDB表,已经插入的所有行都会从该表中删除,从而不会产生任何实际影响;
INSERT INTO t VALUES(1),(‘x’);
— 隐式提交;
1.导致隐式提交语句的行为就像在执行实际语句之前发出COMMIT一样;
2.这些语句本身并非事务语句,也就是说,如果成功,则无法回滚;
— 事务存储引擎;
1.使用SHOW ENGINES\G语句可以列出所有已编译到MySQL服务器中的引擎;
2.相关信息:
1.Engine:引擎名字;
2.Support:值为YES或NO,用于指示该引擎是否可以使用;如果该值为DISABLED,则表示该引擎存在,但已关闭;值DEFAULT用于指示服务器在默认情况下使用的存储引擎;
3.Comment:相关的介绍;
4.Transactions,XA和Savepoints列用于指示该存储引擎是否支持这些功能;
— 事务隔离问题;
1.前提:服务器可能同时具有多个未提交的事务(每个会话最多一个事务),当多个客户机并发访问同一个表的数据时,可能会出现一致性问题;
2.“脏”读:一个事务读取另一个未提交的事务所做的更改;eg:假定事务T1修改了某行,如果事务T2读取该行,并发现修改内容,但T1尚未提交,则会出现“脏”读问题;之所以会成为一个问题,是因为如果T1回滚,所做的更改会被撤消,但T2并不会意识到这一点;
3.不可重复读:指原始数据不可重复读;表示一个事务内的多个相同查询返回了不同的结果集;eg:事务T1内有两次对表t1的查询,但是在查询的间隙事务T2对表做了更新,结果导致第二次查询跟第一次查询的结果不同;(InnoDB使用快照来完成)
4.虚读(幻读):假定事务T1和T2开始,并且在事务T1的一次读取或者更新了某些行,此时如果事务T2插入一个新行,而事务T1读取或者更新操作时发现该行,则会发生虚读问题(新行会成为虚行);
— 隔离级别;
1.如果一个客户机的事务更改了数据,其他客户机的事务是应发现这些更改还是应与其隔离?事务隔离级别可以确定同时进行的事务在访问相同数据时彼此交互的方式;
2.使用存储引擎可实现隔离级别,隔离级别选项在不同的数据库服务器之间是不一样的,因此,InnoDB所实现的级别可能与其他数据库系统所实现的级别并不完全对应;
3.InnoDB可实现四种隔离级别,用于控制事务所做的更改在多大程度上可由其他同时进行的事务注意到:
1.READ UNCOMMITTED:允许发生“脏”读,不可重复读和虚读;
2.READ COMMITTED:允许发生不可重复读和虚读;未提交的更改仍不可见;(Oracle默认)
3.REPEATABLE READ:无论其他事务所做的更改是否已提交,两次都会获得相同的结果;换句话说,也就是不同的事务会对相同的数据产生一致的结果;(InnoDB默认)
4.SERIALIZABLE:与REPEATABLE READ类似,但其限制性更强,即一个事务所选的行不能由其他事务更改,直到第一个事务完成为止;
4.隔离级别越高,并发性能越差;
— 隔离级别问题;
— 设置隔离级别;
1.在选项文件或者命令行可以设置的参数值为:transaction-isolation = [READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE];
2.对于SET TRANSACTION ISOLATION LEVEL语句,可以设置的参数值为:SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE];
3.此事务级别可以全局设置,也可以按会话设置,如果没有显式指定,则事务隔离级别将按会话进行设置;
4.设置的全局默认事务隔离级别适用于从设置时起所有新建立的客户机连接,现有连接不受影响;
eg:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET @@global.tx_isolation = ‘READ-COMMITTED’;
— 全局隔离级别;
1.客户机始终可以修改其自身会话的事务隔离级别,但要更改全局默认事务隔离级别,则需要SUPER特权;
2.查看当前隔离级别,可使用tx_isolation服务器变量;
1.查看当前会话:SELECT @@tx_isolation;
2.查看全局和会话:SELECT @@global.tx_isolation, @@session.tx_isolation;
如果此变量未使用前缀,则会返回会话事务隔离级别。使用 global 和 session 前缀, 可以相应地显式获取全局或会话隔离级别。
也可以使用此服务器变量来设置事务隔离级别。此隔离级别与在SET TRANSACTION ISOLATION LEVEL语法中设置的隔离级别同样有效,其差别在于,它必须以字符串形式 (而不是普通关键字)来表示,并且必须用连字符(短划线或减号)来分隔用于定义隔离 级别的词,而不是使用空格来分隔
— 事务示例:隔离;
mysql> PROMPT s1>
s1> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
s1> SELECT @@global.tx_isolation;
+———————–+
| @@global.tx_isolation |
+———————–+
| READ-COMMITTED |
+———————–+
mysql> PROMPT s2>
s2> START TRANSACTION;
s2> USE db1;
s2> INSERT INTO t1 VALUES(1);
s1> SELECT * FROM t1;
Empty Set (0.0 sec)
s2> COMMIT;
s1> SELECT * FROM t1;
+——+
| id |
+——+
| 1 |
+——+
— 锁定概念;
1.锁定机制可以防止因多个客户机同时访问数据而出现的问题;该机制会以某个客户机的身份锁定数据,以限制其他客户机访问该数据,直到释放锁定为止;
2.该锁定允许持有锁的客户机访问数据,而限制与之争用访问权限的其他客户机可以执行的操作,锁定机制的结果是,将对数据的访问序列化,这样,在多个客户机要执行相互冲突的操作时,每个客户机都必须轮流等待;
3.并非所有类型的并发访问都会产生冲突,因此,允许客户机访问数据所需的锁定类型取决于该客户机是希望读取还是希望写入:
1.如果某个客户机希望读取数据,则希望读取相同数据的其他客户机不会产生冲突,它们可以同时进行读取;但是,如果另一个客户机希望写入(修改)数据,则它必须等待,直到读取完成为止;
2.如果某个客户机希望写入数据,则所有其他客户机都必须等待,直到写入完成,而无论这些客户机是想读取还是想写入;
3.通过共享锁和互斥锁来完成;
4.锁定可以禁止并发进行相互冲突的更改并禁止读取正在更改的数据,从而可以防止数据损坏;
— 显式行锁;
1.InnoDB支持两种锁定修饰符,这两种修饰符可以添加到SELECT语句的末尾:
1.LOCK IN SHARE MODE子句:共享锁,也就是说,虽然任何其他事务都无法获得互斥锁,但其他事务可以同时使用共享锁;由于正常读取不会锁定任何内容,因此它们不会受锁定的影响;
2.FOR UPDATE子句:使用互斥锁来锁定选定的每一行,以防止其他对象获得这些行上的任何锁,但允许读取这些行;
2.在REPEATABLE READ隔离级别中,可以将LOCK IN SHARE MODE添加到SELECT操作中,这样,如果其他事务想修改选定行,则它们必须等待当前事务完成;这一点与SERIALIZABLE隔离级别的工作方式类似;
3.对于该隔离级别,InnoDB会隐式将LOCK IN SHARE MODE添加到SELECT语句中,而不会包含任何显式锁定修饰符;如果选择了在未提交的事务中修改的行,则会锁定SELECT,直到该事务提交为止;
— 死锁;
1.发生死锁的原因:
1.事务需要获得多个表上的锁定,但顺序相反;
2.每个事务因计时问题而仅获取了部分锁定;
2.处理办法:InnoDB会检测并中止(回滚)其中一个事务,并允许另一个事务完成;
1.如果InnoDB对某个事务执行完整回滚,则该事务所设置的所有锁定都会被释放;
2.但是,如果因出现错误而仅回滚了一个SQL语句,则该语句所设置的某些锁定可能会保留;(如果SELECT语句在事务中调用一个存储函数,而该函数中的一个语句出现错误,则该语句将回滚;同时,如果此后执行ROLLBACK,则整个事务将回滚)
3.发生此问题的原因是,InnoDB存储行锁的格式使它此后无法识别锁和语句之间的对应关系;
— 事务示例:死锁;
s1> CREATE TABLE t_dl1(id INT);
s1> CREATE TABLE t_dl2(id INT);
s1> INSERT INTO t_dl1 VALUES(1);
s1> INSERT INTO t_dl2 VALUES(1);
s1> COMMIT;
mysql> PROMPT s1>
s1> START TRANSACTION;
s1> UPDATE t_dl1 SET id = 1 WHERE id = 1;
mysql> PROMPT s2>
s1> START TRANSACTION;
s1> UPDATE t_dl2 SET id = 1 WHERE id = 1;
s1> UPDATE t_dl2 SET id = 1 WHERE id = 1;
s2> UPDATE t_dl1 SET id = 1 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
— 隐式锁;
1.InnoDB表会使用行级别锁定,以满足最高可能的并发;
2.对于InnoDB存储引擎,请避免使用LOCK TABLES语句;它不会提供任何额外的保护,却会减少并发性;利用自动行级别锁定,需对表进行锁定和解锁;
— 课后练习;