MySQL数据库应用与开发技术 课件 第10章-关系数据库事务管理

MySQL数据库应用与开发技术第10章关系数据库事务管理本单元教学目标理解事务控制的原理、规则深入理解解事务的ACID特性深入认识关系数据库事务级别分类掌握关系数据库事务控制方法

认识、了解事务的概念、作用3事务管理问题的引入

事务并发控制4问题的引入多用户同时操作数据库系统在执行用户的请求时出现故障例如：帐户A转帐给帐户B10000元。UPDATEYHZHSET余额=余额-10000WHERE帐号=‘A’UPDATEYHZHSET余额=余额+10000WHERE帐号=‘B’5事务的基本概念事务（Transaction）用户定义的一个对数据库读写操作序列一个不可分割的工作单位在关系数据库中，事务可以是一条、一组SQL语句，或整个程序。事务和程序的区别？程序包含多个事务6事务的性质原子性(Atomicity)事务中的操作要么都做，要么都不做(AllorNone)一致性(Consistency)在事务完成或回滚时，必须使所有的数据都保持一致状态，即所有数据必须是完整性的，所有数据都必须是正确的。隔离性(Isolation)并发执行的各事务不能相互干扰持续性/永久性(Durability)事务一旦提交，它对数据库的更新不再受后继操作或故障的影响★

DBMS中事务处理必须保证其ACID特性，这样才能保证数据库中数据的安全和正确。7

事务的基本概念事务的开始和结束可以由用户显式控制。SQL定义事务的语句Starttransaction(事务开始)Commit(事务提交，正常结束，将更新结果写入磁盘)Rollback(事务回滚，撤销事务中所有已完成的更新)8事务的基本概念显式定义方式

STARTTRANSACTIONSTARTTRANSACTIONSQL语句1SQL语句1

SQL语句2SQL语句2

。。。。。。。。。。

COMMITROLLBACK9COMMIT事务正常结束提交事务的所有操作（读+更新）事务中所有对数据库的更新永久生效ROLLBACK事务异常终止事务运行的过程中发生了故障，不能继续执行，回滚事务的所有更新操作事务回滚到开始时的状态事务的基本概念10事务的基本概念隐含事务与自动提交

ALTERINSERTCREATEDELETEDROPSELECTUPDATETRUNCATETABEL11例：删除仓库“WH1”表，并将职工T表当中所有在“WH1”仓库中职工记录删除事务案例BEGINTRANSACTIONMYDELDELETEFROM仓库TWHERE仓库号='WH1'DELETEFROM职工TWHERE仓库号='WH1'IF@@ERROR<>0ROLLBACKTRANSACTIONMYDELELSECOMMITTRANSACTIONMYDEL12并发控制｜干扰问题丢失更新问题读“脏”数据问题不一致分析问题幻象读问题13更新丢失问题

用SQL术语描述丢失更新问题14脏读问题｜DirtyRead

读“脏”（DirtyRead）数据问题：查询一个已经被其他事务更新、但尚未提交的数据，将会引起脏读问题。

15不一致分析问题不一致分析问题也称为不可重复读问题，很多应用可能需要校验功能，这时往往需要连续两次或多次读数据进行校验和分析，结果由于其他事务的干扰，使得前后结果不一致，从而产生校验错误（即不一致的分析）。如，对某个数据（a=10）作两次校验：1、检查其值是否为2的倍数，2、检查其减去20后是否大于0（a-20>0）如果在第1与第2步之间，这个数据被其它进程更改为25（a=25）则这个不合法的数据将变为合法数据。16幻读问题幻象读问题与不一致分析问题有关当事务A读数据时事务B在对同一个关系（事务A的结果集）进行插入或删除操作这时事务A再遍历结果集时（读同一条件的数据），会发现神秘地多出了一些元组或丢失了一些元组把这种现象称作幻象读17封锁

封锁的基本技术

封锁机制

MySQL中与封锁有关的命令

封锁粒度

意向锁

18封锁的基本原理当需要查询或更新数据时，先对数据进行封锁，以避免来自其他事务的干扰。针对不同的干扰问题可以有不同的封锁机制。

19封锁的基本原理（更新丢失）封锁以后的事件进程20封锁机制

共享封锁

独占封锁

更新封锁

有些封锁在执行完相应操作后就自动释放封锁，有些封锁则保持到事务结束（提交或撤消）时才释放（无论如何，所有的封锁都会在事务结束时自动释放）。

21共享封锁

共享封锁是为读操作设置的一种封锁，所以也称作读锁，或简称S锁目的是想读到一组不变的数据，也就是在读数据的过程中，不允许其他用户对该数据进行任何修改操作。这种封锁可以保证最大的读并发性，任何数量的用户都可以同时对同样的数据施加这种共享锁。已经实施共享锁的表拒绝来自其他事务的独占封锁和更新封锁。

22独占封锁

独占封锁也叫排他封锁，它是为写（修改、增加、删除）操作设置的一种封锁，也称为写封锁，或简称为X锁，这是最严格的一类封锁。当需要对表实施插入、删除或修改操作时，应该使用独占封锁。已经实施独占封锁的表，拒绝来自其他用户的任何封锁。23更新封锁

当需要对一个记录或一组记录进行更新时（只是修改，不包括插入和删除）使用更新封锁，该封锁的目的是防止其他用户在同一时刻修改同一记录。已经实施更新封锁的记录，拒绝来自其他用户的任何封锁。24封锁粒度

封锁的对象可以是表、也可以是元组等，我们把封锁对象的大小称为封锁粒度（Granularity）。表级锁封锁的对象为整个表页级锁封锁的对象为数据页或数据块一个大表中可以包含若干个数据页或数据块行级锁封锁的对象为数据表中的行（元组）一个数据页或数据块包含若干行数据（元组）数据库管理系统一般都具有多粒度锁定功能，允许一个事务锁定不同类型的资源。

25封锁粒度锁定在较小的粒度（例如行）可以增加并发操作的性能，但系统开销也较大。这是因为如果封锁的粒度小，则意味着需要的锁多，从而需要系统控制更多的锁。

锁定在较大的粒度（例如表）会降低操作的并发性，这是因为锁定整个表限制了其他事务对表中任意部分进行访问。封锁粒度大，则不需要太多的封锁，由于需要维护的锁较少，所以系统开销较低。

26死锁产生死锁的原因

避免死锁

发现死锁解决死锁

27产生死锁的原因右图示意了两个并发事务所发生事件的序列，两个程序都为了等待对方释放数据资源而产生死锁。

28避免死锁

相同顺序法

所有的用户程序约定都按相同的顺序来封锁表一次封锁法

为了完成一个事务，一次性封锁所需要的全部表29避免死锁的封锁

30发现死锁超时法

即一个事务在等待的时间超过了规定的时限后就认为发生了死锁。这种方法非常不可靠，如果设置的等待时限长，则不能及时发现死锁；如果设置的等待时限短，则可能会将没有发生死锁的事务误判为死锁。

31解决死锁发现死锁后解决死锁的一般策略是：自动使“年轻”的事务（即完成工作量少的事务）先退回去，然后让“年老”的事务（即完成工作量多的事务）先执行，等“年老”的事务完成并释放封锁后，“年轻”的事务再重新执行。

32隔离级别在避免干扰的情况下，适当的降低隔离级别，从而提高并发的操作效率。隔离级别越低，并发操作效率越高，但是产生干扰的可能性也越大。隔离级别越高，并发操作效率越低，同时产生干扰的可能性也越小。在设计应用时，可以在所能容忍的干扰程度范围内，尽可能降低隔离级别，提高应用的执行效率。33隔离级别未提交读（READUNCOMMITTED）：最低级别，仅可保证不读取物理损坏的数据，隔离级别最低。提交读（READCOMMITTED）：可以保证不读取“脏”数据，大多数的数据库默认级别。可重复读（REPEATABLEREAD）：可以保证读一致性，避免不一致分析问题，mysql默认级别。可串行化（SERIALIZABLE）：事务隔离的最高级别，事务之间完全隔离，强制事务串行执行，不可并发执行。SQL支持4种隔离级别：34隔离级别查询可以用下列语句查询事务隔离级别：全局事务隔离级别（整个数据库）SELECT@@global.tx_isolation;

35隔离级别设置mysql引擎为：innodb的事务级别设置方法是：set+

作用域+transactionisolationlevel+隔离级别如：用下列语句设置全局事务隔离级别：设置隔离级别为：未提交读setglobaltransactionisolationlevelREADUNCOMMITTED;

设置隔离级别为：提交读setglobaltransactionisolationlevelREADCOMMITTED;设置隔离级别为：可重复读setglobaltransactionisolationlevelREPEATABLEREAD;设置隔离级别为：可串行化setglobaltransactionisolationlevelSERIALIZABLE;36命令行｜事务操作开启一个事务方式一starttransaction方式二begin提交一个事务方式一commit回滚一个事务方式一rollback37演示｜建表CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_id`int(10)unsignedNOTNULLauto_increment,`user_name`varchar(45)NOTNULL,`pass_word`varchar(45)NOTNULL,`email`varchar(45)NOTNULL,`phone`varchar(45)NOTNULL,`sex`char(1)NOTNULL,`score`int(10)unsignedNOTNULL,PRIMARYKEY(`user_id`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8;INSERTINTO`user`(`user_id`,`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES(1,'LiMing','LiMing','LiMing@','83278904','0',60),(2,'ZhuangPing','ZhuangPing','ZhuangPing@','83278678','0',70),(3,'LuMei','LuMei','LuMei@','83278904','1',10),(4,'QiaoBing','QiaoBing','QiaoBing@','83278452','1',70),(5,'Kerry','Kerry','Kerry@','83278678','1',50),(6,'Jetty','Jetty','Jetty@','83278904','0',90),(7,'Lucy','Lucy','Lucy@','83278904','0',40),(8,'Honey','Honey','Honey@','83278904','1',80),(9,'Wendy','Wendy','Wendy@','83278452','0',50),(10,'Rose','Rose','Rose@','83278904','0',30);38事务隔离性｜演示-1打开第一个mysql客户端，手动开户事务，进行一个update操作（未提交或回滚事务）39事务隔离性｜演示-2打开第二个mysql客户端检索上面被更新的记录，可以看到数据还是之前未做update操作前的数据40事务隔离性｜演示-3打开第三个mysql客户端，手动开户事务，对第一个浏览器update的那条记录，进行delete操作，可以看到，操作无法进行，事务处于等待中41事务隔离性｜演示-4第一个mysql客户端，进行事务提交，则第二个mysql客户端，马上可以检索到update的数据，则第三个mysql客户端则可进行delete操作第一个客户端提交事务第二个客户端检索到新数据42脏读｜演示-1设置mysql全局事务隔离级别为：未提交读setglobaltransactionisolationlevelREADUNCOMMITTED;43脏读｜演示-2打开第一个mysql客户端，检索user_id=2的记录，可以看到email字段值为：ZhuangPing@44脏读｜演示-3打开第二个mysql客户端，手动开户事务，进行一个update操作（未提交或回滚事务）45脏读｜演示-4打开第三个mysql客户端，检索刚刚进行更新操作的记录，可以看到刚刚更新但还末提交事务的数据，已经被检索到。46脏读｜演示-5回到第二个mysql客户端，回滚事务，则刚刚的update操作无效。47脏读｜演示-6重新回到第三个mysql客户端，检索user_id=2的记录，可以看到数据已经变成末做update操作前的状态，第4步操作中读到的数据为脏数据。48可重复读｜演示-1可重复读：在同一个事务以内，多次对同一资源读取的数值是一样的，不管外部事务如何操作该资源（包括：删除、修改，并且外部事务已完成并提交）第一步：设置mysql全局事务隔离级别为：可重复读setglobaltransactionisolationlevelREPE