北邮-大三下-数据库实验七-mysql版本

09211311班实验报告学院：计算机学院课程名称：数据库系统实验名称：实验七数据库的事务创建与运行实验班级：2009211311姓名：schnee学号：09211311班实验七数据库的事务创建与运行实验1.实验目的通过实验，了解mysql数据库系统中各类数据库事务的定义机制和基于锁的并发控制机制，掌握mysql数据库系统的事务控制机制。2.实验环境操作系统：MicrosoftWindows7旗舰版（32位）。软件：数据库版本：MySQL5.53.实验内容及过程3.1.定义三种模式的数据库事务事务是由相关操作构成的一个完整的操作单元。两次连续成功的COMMIT或ROLLBACK之间的操作，称为一个事务。对数据库所做的一系列修改，在修改过程中，暂时不写入数据库，而是缓存起来，用户在自己的终端可以预览变化，直到全部修改完成，并经过检查确认无误后，一次性提交并写入数据库，在提交之前，必要的话所做的修改都可以取消。提交之后，就不能撤销，提交成功后其他用户才可以通过查询浏览数据的变化。事务的特点ACID:原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)。一个有效的事务处理系统必须满足相关标准。原子性：一个事务必须被视为一个单独的内部“不可分”的工作单元，以确保整个事务要么全部执行，要么全部回滚。一致性：数据库总是从一种一致性状态转换到另一种一致性状态。隔离性：某个事务的结果只有在完成之后才对其他事务可见。在上述例子中，当数据库执行完insert语句，还未执行delete语句时，如果此时另一个客户端对数据库的访问也同时运行，它将仍视符合条件的记录在b表中。持久性：一旦一个事务提交，事务所做的数据改变将是永久的。3.1.1.显式事务显式事务，由用户指定，允许用户决定哪批工作必须成功完成，否则所有部分都不完成。操作包括starttransaction,rollback,commit。我们先创建一个innodb类型表格course2，其实可以直接对course操作的。在此只是为了练一练innodb的一些操作。09211311班mysql默认采用autocommit模式运行。故为了创建显式事务，我们需要修改autocommit变量。先查看此变量的默认值修改变量autocommit，禁止自动提交，并创建一个显式事务。执行第一条插入语句并设置第一个回滚点p1。执行第二个插入语句并设置第二个回滚点p2。09211311班回滚到p1，则p2自动被丢弃。回滚到原始点，即事务开始的点。发现操作都被回滚。显式事务执行成功。显式事务即事务没有自动提交，可以回滚到原始点，在rollback和commit之前对数据库的修改都可以挽回，而不是永久写入。3.1.2.自动提交事务mysql默认采用autocommit模式运行。这意味着，当您执行一个用于更新（修改）表的语句之后，MySQL立刻把更新存储到磁盘中。默认级别为不可重复读。设置自动提交为ON。按照3.1.1.操作，发现rollbacktop1出错，虽然设置保存点成功，但是实际上每一个语句已经自动提交了，也就是说已经永久写入了。所以事务无法回滚。09211311班3.1.3.隐式事务虽然我们设置自动提交为OFF，但是在事务中如果有createtable，alterfunciton，dropindex等等语句，则隐含地结束一个事务，似乎是在执行本语句前，你已经进行了一个commit。如下图所示，createtable就隐含了一个事务的结束。正是这个隐式事务导致了回滚的失败。虽然rollback执行看似成功，但是实际却没能发挥作用。因为隐式事务已经无法挽回。3.2.查看事务的锁信息和隔离级别3.2.1.查看事务的锁信息09211311班查看系统上表锁定争夺：查看系统上的行锁的争夺情况：InnoDB以Oracle的风格，对行级进行锁定，并且默认运行查询作为非锁定持续读。由于感兴趣，所以下面对innodb的行锁定做个小测验。我们对innodb引擎的表A进行i=1的行锁定。再另开一个数据库访问B，并且在B中也开始一个事务，对i=1行进行操作。但是由于i=1行已经被锁定，所以只有先执行的A端commit后，B端才能执行事务。当A为提交时，B只能等待，若是等待过久，则会超时。09211311班3.2.2.查看事务的隔离级别查看innodb系统级别的事务隔离级别：查看innodb会话级别的事务隔离级别：3.2.3.学习并试验事务的四种隔离级别（非实验要求内容）SQL标准定义了4类隔离级别。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。ReadUncommitted（读取未提交内容）：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（DirtyRead）。ReadCommitted（读取提交内容）：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（NonrepeatableRead），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。RepeatableRead（可重读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（PhantomRead）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，MultiversionConcurrencyControl）机制解决了该问题。Serializable（可串行化）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：脏读(DrityRead)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。不可重复读(Non-repeatableread):在一个事务的两次查询之中数据不一致，这09211311班可能是两次查询过程中间插入了一个事务更新的原有的数据。幻读(PhantomRead):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：3.2.3.1.读未提交readuncommitted改变数据库访问端A隔离级别为readuncommitted。另开一个mysql端B，对test进行插入操作，但是如下图所示（左B右A），我们看到虽然B自动提交事务，但是A端无法查看B的插入。直到A端也提交事务，重新对数据进行查询时才看到数据的更新。也就是说在同一个事务里数据保持一致了。上述的实验中就没有出现脏读。但是实际上未提交读隔离级别可能造成脏读现象。即事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录。未提交读是最低的隔离级别。09211311班比如下面这个事务就出现了脏读。3.2.3.2.已提交读readcommitted已提交读会有幻想读。已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务A在两次查询的数据不一致，因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录，但不要求可重复读。比如下面截图中（仍然是左B右A），A在同一个事务里重复读的结果不同，就是因为两次读之间B对数据做了操作，从而出现了不可重复读。09211311班3.2.3.3.可重复读repeatableread可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，但是可能产生幻读问题(注意是可能，因为数据库对隔离级别的实现有所差别)。像以上的实验，就没有出现数据幻读的问题。把A改为可重复读。B端提交一个插入。A端可以读到已经提交的记录B端新插入5。09211311班A读不到未提交的5的插入记录A端提交，但是A的新事务同样读不到。B提交。A也提交，然后才可以读到B的提交信息。3.2.3.4.可串行化serializableserializable完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。A改为serializableB为可重复读。A启动事务。09211311班B插入。A欲操作，但是由于B先操作了，所以A只能等待B。不幸超时了。B提交了。A可以正常操作了。3.3.利用SQL语句和数据库API函数控制事务3.3.1.利用SQL语句控制事务利用前面显式事务，设置回滚点等可以控制事务。具体实验详见前面部分的实验内容。3.3.2.利用数据库API函数控制事务在之前编写的程序中，可以通过ODBC进行事务控制。具体实验参见之前实验报告。3.4.利用事务调试语句，查看事务相关信息，调试事务可以利用下面两个语句来查看commit和rollback次数。自动提交模式下，没有显式COMMIT，com_commit这个值不计数。showstatuslike'com_commit'09211311班showstatuslike'com_rollback'不过mysql好象没有trancount或transactioncount，不像SQLServer可以统计事务个数。4.实验小结实验中遇到的问题：这次实验进行比较顺利，只是按照实验要求逐步编程实现即可。实验中，但是由于MySQL本身的问题纠结了很久。MySQL对部分事务调试语句的支持没有像SQLServer那么充分，查找了好多资料才勉强找到两条调试的语句。而实验三的部分由于与之前的实验相似，也没有详细列出。实验心得：通过这次实验，我了解了mysql数据库系统中各类数据库事务的定义机制和基于锁的并发控制机制，掌握mysql数据库系统的事务控制机制。虽然实验的第二部分只是要求查看事务的隔离级别，本来小小的几句话就能解决，但是我出于对隔离级别的好奇，自己做了大量实验来亲身体验不同的隔离级别的特点和区别，并且在实验中认识不同隔离级别的缺点，比如脏读等的情况。要求查看锁信息的部分，我也按照网络资料做了一个小实验，体验了一下innodb行锁定的情况。虽然实验的时间要比其他同学长很多，但是我却从中学到了课堂上没有的知识，还是很值得的。实验不可小看，只有在实践中在自己的编程实现中，才能真正地较好掌握课堂所学的知识。实践出真知就是实验课的意义所在。