第10章 MySQL多用户事务管理

第10章

MySQL多用户事务管理事务管理10.110.2多用户访问10.1事务管理10.1.1事务的概念使用一个简单的例子来帮助理解事务：向公司添加一名新的雇员（见图10.1）。10.1.2ACID属性1．原子性原子性意味着每个事务都必须被看作是一个不可分割的单元。假设一个事务由两个或者多个任务组成，其中的语句必须同时成功才能认为整个事务是成功的。如果事务失败，系统将会返回到该事务以前的状态。2．一致性不管事务是完全成功完成还是中途失败，当事务使系统处于一致的状态时存在一致性。参照前面的例子，一致性是指如果从系统中删除了一个雇员，则所有和该雇员相关的数据，包括工资数据和组的成员资格也要被删除。3．隔离性隔离性是指每个事务在它自己的空间发生，和其他发生在系统中的事务隔离，而且事务的结果只有在它完全被执行时才能看到。即使在这样的一个系统中同时发生了多个事务，隔离性原则保证某个特定事务在完全完成之前，其结果是看不见的。10.1.2ACID属性4．持久性持久性是指即使系统崩溃，一个提交的事务仍然存在。当一个事务完成，数据库的日志已经被更新时，持久性就开始发生作用。大多数RDBMS产品通过保存所有行为的日志来保证数据的持久性，这些行为是指在数据库中以任何方法更改数据。数据库日志记录了所有对于表的更新、查询、报表等。10.1.3事务处理在MySQL中，当一个会话开始时，系统变量AUTOCOMMIT值为1，即自动提交功能是打开的，当用户每执行一条SQL语句后，该语句对数据库的修改就立即被提交成为持久性修改保存到磁盘上，一个事务也就结束了。因此，用户必须关闭自动提交，事务才能由多条SQL语句组成，使用如下语句：SET@@AUTOCOMMIT=0;执行此语句后，必须明确地指示每个事务的终止，事务中的SQL语句对数据库所做的修改才能成为持久化修改。例如，执行如下语句：deletefromxswhere学号='081101';select*fromxs;10.1.3事务处理1．开始事务当一个应用程序的第一条SQL语句或者在COMMIT或ROLLBACK语句（后面介绍）后的第一条SQL执行后，一个新的事务也就开始了。另外还可以使用一条STARTTRANSACTION语句来显式地启动一个事务。语法格式：STARTTRANSACTION|BEGINWORK2．结束事务COMMIT语句是提交语句，它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，也标志一个事务的结束，其语法格式为：COMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE]10.1.3事务处理3．撤销事务ROLLBACK语句是撤销语句，它撤销事务所做的修改，并结束当前这个事务。

语法格式：ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE]在前面的举例中，若在最后加上以下这条语句：rollbackwork;10.1.3事务处理4．回滚事务除了撤销整个事务，用户还可以使用ROLLBACKTO语句使事务回滚到某个点，在这之前需要使用SAVEPOINT语句来设置一个保存点。SAVEPOINT语法格式为：SAVEPOINTidentifier其中，identifier为保存点的名称。ROLLBACKTOSAVEPOINT语句会向已命名的保存点回滚一个事务。如果在保存点被设置后，当前事务对数据进行了更改，则这些更改会在回滚中被撤销，语法格式为：ROLLBACK[WORK]TOSAVEPOINTidentifierRELEASESAVEPOINT语句会从当前事务的一组保存点中删除已命名的保存点。不出现提交或回滚。如果保存点不存在，会出现错误。语法格式为：RELEASESAVEPOINTidentifier10.1.3事务处理下面几个语句说明了有关事务的处理过程：1.STARTTRANSACTION2.UPDATE…3.DELETE…4.SAVEPOINTS1;5.DELETE…6.ROLLBACKWORKTOSAVEPOINTS1;7.INSERT…8.COMMITWORK;10.1.4事务隔离级基于ANSI/ISOSQL规范，MySQL提供了下面4种隔离级：序列化（SERIALIZABLE）、可重复读（REPEATABLEREAD）、提交读（READCOMMITTED）、未提交读（READUNCOMMITTED）。只有支持事务的存储引擎才可以定义一个隔离级。定义隔离级可以使用SETTRANSACTION语句。语法格式：SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVELSERIALIZABLE|REPEATABLEREAD|READCOMMITTED|READUNCOMMITTED10.1.4事务隔离级（1）序列化如果隔离级为序列化，用户之间通过一个接一个顺序地执行当前的事务提供了事务之间最大限度的隔离。

（2）可重复读在这一级上，事务不会被看成是一个序列。不过，当前在执行事务的变化仍然不能看到，也就是说，如果用户在同一个事务中执行同条SELECT语句数次，结果总是相同的。（3）提交读READCOMMITTED隔离级的安全性比REPEATABLEREAD隔离级的安全性要差。不仅处于这一级的事务可以看到其他事务添加的新记录，而且其他事务对现存记录做出的修改一旦被提交，也可以看到。10.1.4事务隔离级（4）未提交读提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。系统变量TX_ISOLATION中存储了事务的隔离级，可以使用SELECT随时获得当前隔离级的值，如下所示：10.1.4事务隔离级没有一个标准公式来决定哪个隔离级适用于应用程序—大多数情况下，这是一个主观的决定，它是基于应用程序的容错能力和应用程序开发者对于潜在数据错误的影响的判断。隔离级的选择对于每个应用程序也是没有标准的。例如，同一个应用程序的不同事务基于执行的任务需要不同的隔离级。图10.2中列出了事务隔离级和性能之间的关系。10.2多用户访问10.2.1锁定的级别表锁定：一个特殊类型的访问，整个表被客户锁定。根据锁定的类型，其他客户不能向表中插入记录，甚至从中读数据也受到限制。页锁定：MySQL将锁定表中的某些行（称作页）。被锁定的行只对锁定最初的线程是可行的。行锁定：行级的锁定比表级锁定或页级锁定对锁定过程提供了更精细的控制。死锁：如果很多用户同时访问数据库的话，一个常见的现象就是死锁。10.2.2锁定与解锁MySQL提供了LOCKTABLES语句来锁定当前线程的表，语法格式如下：LOCKTABLEStbl_name[ASalias]{READ[LOCAL]|[LOW_PRIORITY]WRITE}说明：表锁定支持以下类型的锁定。READ：读锁定，确保用户可以读取表，但是不能修改表。WRITE：写锁定，只有锁定该表的用户可以修改表，其他用户无法访问该表。10.2.2锁定与解锁【例10.1】在xs表上设置一个只读锁定。locktablesxsread;【例10.2】在kc表上设置一个写锁定。locktableskcwrite;在锁定表以后，可以使用UNLOCKTABLES命令解除锁定，语法格式为：unlocktables;10.2.3并发访问的问题丢失更新（LostUpdate），指当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，因此最后的更新将重写由其他事务所做的更新，这将导致数据丢失。脏读（DirtyRead）指一个事务正在访问数据，而其他事务正在更新该数据，但尚未提交，此时就会发生脏读问题，即第一个事务所读取的数据是“脏”（不正确）数据，它可能会引起错误。10.2.3并发访问的问题不可重复读（UnrepeatableRead）是当一个事务多次访问同一行而且每次读取不同的数据时会发生的问题。它与脏读有相似之处，因为该事