SQL基础数据教程 5

任务8

实现学生选课系统数据库事务控制——事务目录Contents学习目标8.1任务描述8.2知识准备8.2.1事务概述8.2.2事务的特性8.2.3事务的基本操作8.2.4事务的隔离级别8.3任务实现8.4任务小结学期末，王宁的同学李四向他借1000元，于是，王宁到自动存取款机上转账。如果余额足够，则在转账成功后，王宁的账户减少1000元，李四的账户增加1000元，如果转账过程中发生异常情况，则需把所有数据回退，在这个过程中，需要用到事务的知识，从而保证数据的一致性。通过启动事务，了解事务的概念和特性，了解事务的隔离级别，掌握事务的提交、回滚等操作。实现学生选课系统数据库事务控制学习目标了解事务的概念，能够说出事务的特性；掌握事务的基本操作，能够开启、提交和回滚事务；了解事务的隔离级别，能够说出MySQL中事务的隔离级别以及每个隔离级别的特点。实现学生选课系统数据库事务控制8.1任务描述针对数据库scoredb的表department进行查询操作，查询结果如图8-1所示。在department表的基础上，利用SQL语句插入两条信息('22222','康复学院','张君楠')和('33333','土木工程学院','赵瑞')，首先启动事务，然后开始执行SQL语句，当SQL语句执行完毕后，事务结束时必须提交事务，才能使事务中的所有操作永久生效，即修改操作永久保存在数据库中。当退出数据库重新登录后，使用SELECT语句再次对department表进行查询，查询结果如图8-2所示。实现学生选课系统数据库事务控制8.1任务描述实现学生选课系统数据库事务控制图8-1插入记录前的查询结果8.2知识准备实现学生选课系统数据库事务控制图8-2事务中插入记录后的查询结果8.2知识准备在数据库开发过程中，经常会为了完成某一功能而编写一组SQL语句。为确保每一组SQL语句操作数据的完整性，MySQL引入了事务的管理，MySQL的事务处理机制在程序开发过程中有着非常重要的作用，它可以使整个系统更加安全，保证在同一个事务中的操作具有同步性。事务在MySQL中相当于一个工作单元，使用事务可以确保同时发生的行为不发生冲突，并且维护数据的完整性，确保数据的有效性。本节将对事务进行详细的讲解。实现学生选课系统数据库事务控制8.2.1事务概述所谓的事务是指由用户定义的一系列数据库更新操作，这些操作要么都执行，要么都不执行，是一个不可分割的逻辑工作单元。这里的更新操作主要指对数据库内容产生修改作用的操作，如INSERT、DELETE、UPDATE等操作。事务是数据库中实现数据的一致性的重要技术。例如在银行转账业务处理过程中，客户A要给客户B转账，当转账进行到一半时，发生断电等异常事故，导致客户A的钱已转出，客户B的钱还没有转入，这样就会导致数据库中数据的不一致，给客户带来损失。在转账业务中引|入事务机制，就可以在意外发生时撤销整个转账业务，将数据库恢复到数据处理之前的状态，从而确保数据的一致性。实现学生选课系统数据库事务控制8.2.2事务的特性MySQL中的事务必须具有4个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），简称ACID特性。1.原子性原子性是指事务是一个不可分割的逻辑工作单元，事务处理的操作要么全部执行，要么全部不执行。事务中如果有任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库的状态返回到事务执行前的状态。实现学生选课系统数据库事务控制8.2.2事务的特性2.一致性一致性是指事务必须在执行前后都要处于一致性状态。如果事务操作全部正确执行，则数据库的变化将生效，从而处于有效状态；如果事务处理失败，系统将会回滚，从而将数据库恢复到事务执行前的有效状态。3.隔离性隔离性是指当多个事务并发执行时，各个事务之间不能相互干扰。4.持久性持久性是指事务完成后，事务对数据库中数据的修改将永久保存。实现学生选课系统数据库事务控制8.2.3事务的基本操作对事务进行初步的了解后，接下来学习事务的处理，主要是事务的基本操作，包括启动事务、提交事务以及回滚事务，具体介绍如下。1.启动事务在MySQL中启动事务的语法格式如下：STARTTRANSACTION;2.提交事务启动事务之后，就开始执行事务内的SOL语句，当SOL语句执行完毕后，必须提交事务，才能使事务中的所有操作永久生效。提交事务的语法格式如下：COMMIT;3.回滚事务当事务在执行过程中遇到错误时，事务中的所有操作都要被取消，返回到事务执行前的状态，这就是回滚事务。回滚事务的语法格式如下：ROLLBACK;实现学生选课系统数据库事务控制8.2.4事务的隔离级别MySQL数据库是多线程并发访问的，用户可以通过不同的线程执行不同的事务，在多个线程同时开启事务访问数据库时，可能会出现脏读、不可重复读以及幻读等情况。为了保证这些事务之间不受影响，设置事务的隔离级别十分必要。实现学生选课系统数据库事务控制8.2.4事务的隔离级别1.启动事务多个线程同时开启事务访问时数据库的情况（1）脏读脏读是指一个事务读取另一个事务没有提交的数据，是指第一个事务正在访问数据，并且对数据进行了修改，当这些修改还没有提交时，第二个事务访问和使用了这些数据。如果第一个事务回滚，那么第二个事务访问和使用的数据则是脏数据。（2）不可重复读不可重复读是指在一个事务内，对同一数据进行两次相同查询，所返回结果不同。这是由于在一个事务两次读取数据之间，有第二个事务对数据进行了修改，造成两次读取数据的结果不相同。（3）幻读幻读是指在同一事务中，两次按相同查询条件查询到的记录的行数不一样。造成幻读的原因在于事务处理没有结束时，其他事务对同一数据集合增加或者删除了记录。实现学生选课系统数据库事务控制8.2.4事务的隔离级别2.隔离级别概述为了避免以上情况的发生，MySQL设置了事务的4种隔离级别，由低到高分别为READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE，其能够有效地防止脏读、不可重复读以及幻读等情况的发生。（1）READUNCOMMITTEDREADUNCOMMITTED就是“读未提交”，在该隔离级别下的事务可以读取另一个未提交事务的数据，它是事务中的最低的隔离级别，这种读取的方式也被称为脏读（dirtyread），很少应用。实现学生选课系统数据库事务控制8.2.4事务的隔离级别2.隔离级别概述（2）READCOMMITTEDREADCOMMITTED就是“读提交”，顾名思义，就是在该隔离级别下的事务只能读取其他事务已经提交的数据，是大多数DBMS（如SQLServer、Oracle）的默认隔离级别，避免了脏读的现象，但这种隔离级别容易出现不可重复读的问题，不可重复读是指在一个事务中，数据发生了改变，多次查询的结果不一致。（3）REPEATABLEREADREPEATABLEREAD就是“可重复读”，它是MySQL的默认事务隔离级别，能够确保同一事务的多个实例并发读取数据时，读到的数据行是相同的，解决了脏读和不可重复读的问题，但这种隔离级别容易出现幻读的问题。实现学生选课系统数据库事务控制8.2.4事务的隔离级别2.隔离级别概述（4）SERIALIZABLESERIALIZABLE就是“可串行化”，它是MySQL最高的、最严格的隔离级别，通过对事务进行强制性的排序，在每个读的数据行上加锁，使这些事务不会相互冲突，从而解决了脏读、不可重复读和幻读的问题，但这种隔离级别容易出现超时和锁竞争现象，因此，也是性能最低的一种隔离级别，除非为了数据的稳定性，需要强制减少并发的情况时，才会选择此种隔离级别。各个隔离级别可能产生的问题如表8-1所示。实现学生选课系统数据库事务控制8.2.4事务的隔离级别2.隔离级别概述实现学生选课系统数据库事务控制8.2.4事务的隔离级别3.修改隔离级别用户可以用SETTRANSACTION语句改变当前会话或所有新建连接的隔离级别，其语法式如下：SET[SESSION|GLOBAL]TRANSACTIONISOLATIONLEVEL{READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}例如，设置当前会话的隔离级别为READCOMMITTED，具体语句如下：SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;实现学生选课系统数据库事务控制8.3任务实现在本章中，主要完成的任务是事务的基本操作，下面以学生管理系统数据库scoredb举例进行详细介绍。实现学生选课系统数据库事务控制8.3任务实现8.3.1手动开启事务、提交事务为了使事务中SQL语句执行的修改操作永久保存在数据库中，事务处理结束时必须由用户提交事务。例如，在手动提交的方式下，启动一个事务，在department表中插入两条记录，具体语句如下:STARTTRANSACTION;INSERTINTOdepartmentVALUES('22222','康复学院','张君楠');INSERTINTOdepartmentVALUES('33333','土木工程学院','赵瑞’);执行之后用SELECT语句查询department表，执行结果如图8-3所示。实现学生选课系统数据库事务控制8.3任务实现8.3.1手动开启事务、提交事务实现学生选课系统数据库事务控制图8-3事务中插入记录后的查询结果从图8-3可以看出，事务的处理似乎已经完成，但是退出数据库重新登录后，再次对department表进行查询，结果如图8-4所示。8.3任务实现8.3.1手动开启事务、提交事务实现学生选课系统数据库事务控制从图8-4可以看出，事务中的记录插入操作最终并未完成，这是因为事务未经提交就已经退出数据库了，由于采用的是手动提交模式，事务中操作被自动取消了。图8-4重新登录后查询department表的记录8.3任务实现8.3.1手动开启事务、提交事务为了能够永久把两条记录写入数据库中，需要在事务处理结束后加入COMMIT语句，完成整个事务的提交具体语句如下：STARTTRANSACTION;INSERTINTOdepartmentVALUES(‘22222’,’康复学院’,’张君楠’);INSERTINTOdepartmentVALUES(‘33333’,’土木工程学院’,’赵瑞’);COMMIT;执行完毕后，退出数据库重新登录后，使用SELECT语句查询department表中的记录，查询结果如图8-5所示。实现学生选课系统数据库事务控制8.3任务实现8.3.1手动开启事务、提交事务实现学生选课系统数据库事务控制从图8-5可以看出，两条记录被永久性地插入数据表department中了，是因为事务处理结束后加入了COMMIT语句，完成了整个事务的提交。图8-5重新登录后查询department表的结果8.3任务实现8.3.2回滚事务如果事务尚未提交时出现了操作错误，可以通过事务的回滚来取消当前事务，把数据库恢复到事务处理之前的状态。下面用一个示例说明事务回滚。首先在department表中插入两条记录，且先不提交，语句如下：STARTTRANSACTION;INSERTINTOdepartmentVALUES(‘44444’,’马克思主义学院’,’周轩’);INSERTINTOdepartmentVALUES(‘55555’,’电子信息学院’,’郑思洁’);实现学生选课系统数据库事务控制8.3任务实现8.3.2