MySQL教程（第4版） 课件 第10章 事务管理和多用户操作

第10章

事务管理和多用户操作——事

务01事务的概念事务的概念在MySQL环境中，事务由一个单独单元的一个或多个SQL语句组成。这个单元中的SQL语句是互相依赖的，而单元作为一个整体是不可分割的。如果单元中的一个语句不能完成，整个单元就会回滚（撤销），所有影响到的数据将返回到事务开始以前的状态。因而，只有事务中的所有语句都成功地执行才能说明这个事务被成功地执行。例如：在学生成绩数据库（xscj）中，下列情况都是一个完整的事务。（1）删除一个学生及格以上的成绩和减少对应的总学分。（2）修改一个成绩（从不及格到及格以上）需要增加学生的总学分。（3）修改一个成绩（从及格以上到不及格）需要减少学生的总学分。（4）删除学生记录的同时需要删除该学生的所有成绩记录。（5）删除课程记录的同时需要删除该课程的所有成绩记录。02ACID属性原子性一致性隔离性持久性ACID属性1．原子性原子性指事务是数据库工作的最小单位，一个事务中的所有操作要么全部成功提交，要么全部失败回滚。例如：插入一个学生及格以上成绩和增加对应学生的总学分这两个操作要么同时成功要么同时失败，故应在同一个事务中。2．一致性一致性指事务操作不能破坏数据的一致性，数据库在一个事务的执行前后都应处于一致状态。例如：假设在插入学生成绩后数据库发生故障（例如宕机）则事务被迫中断，但修改学生总学分未完成，已经写入数据库的成绩记录就会破坏与学生总学分数据的一致性。ACID属性3.隔离性隔离性指数据库并发情况下，并发的事务之间是隔离的，一个事务的执行不能被其他事务影响。隔离性比较复杂，在本节后面专门介绍。4.持久性持久性指一旦事务提交，则其对数据的变更就是永久性的，即使数据库发生任何故障都不应该对数据造成影响。例如：插入一个学生及格以上成绩和增加对应学生的总学分这两个操作事务已经完成，但数据库故障，仍然要保证两个操作结果正确并保存到数据库中。03事

务

处

理开始事务结束事务撤销事务和回滚事务事务处理1．开始事务STARTTRANSACTION|BEGINWORK2．结束事务使得自从事务开始以来所执行的所有修改成为数据库的永久部分，也标记一个事务的结束。COMMIT[AND[NO]CHAIN][[NO]RELEASE]说明：（1）可选的ANDCHAIN子句会在当前事务结束时立刻启动一个新事务，并且新事务与刚结束的事务有相同的隔离级。（2）RELEASE子句在终止了当前事务后，会让服务器断开与当前客户端的连接。包含NO关键字可以抑制CHAIN或RELEASE完成。事务处理3．撤销事务和回滚事务（1）撤销事务所做的修改，并结束当前这个事务。ROLLBACK[AND[NO]CHAIN][[NO]RELEASE]选项功能同结束事务（COMMIT）。（2）可以不撤销事务所有操作，而是回滚到一个事务的保存点。当然，此前在事务中需要设置保存点，设置语句如下：SAVEPOINT保存点名如果在保存点被设置后，当前事务对数据进行了更改，则这些更改会在回滚到保存点时被撤销。事务处理（3）删除指定的保存点RELEASESAVEPOINT保存点名它从当前事务的一组保存点中删除指定的保存点。如果指定的保存点不存在，则会出现错误。下面几个语句说明了有关事务的处理过程：1.STARTTRANSACTION2.UPDATE…3.DELETE…4.SAVEPOINTS1;5.DELETE…6.ROLLBACKWORKTOSAVEPOINTS1;7.INSERT…8.COMMITWORK;04事务应用实例含事务编程执行含事务程序事务功能测试事务应用实例【例】创建包含事务的存储过程，插入学生课程成绩。1.含事务编程创建一个存储过程input_cj，插入一个学生成绩。USExscj;DROPPROCEDUREIFEXISTSinput_cj;DELIMITER$$CREATEPROCEDUREinput_cj(INxhchar(6),INkchchar(3),INcjint)BEGIN DECLARExfint(1); #（a） DECLAREtransErrintDEFAULT0; #（b.1） DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETtransErr=1; #（b.2）

STARTTRANSACTION; #（b.3） SELECT学分INTOxfFROMkcWHERE课程号=kch; #（c.1） INSERTINTOcjVALUES(xh,kch,xf); #（c.2） SELECTSLEEP(10); #（d） IFcj>=60THEN #（c.3） UPDATExsSET总学分=总学分+xfWHERE学号=xh; #（c.3） ENDIF; IFtransErr=1THEN #（b.4）

ROLLBACK; #（b.4） SELECT'成绩插入不成功！'; ELSE #（b.5） COMMIT; #（b.5） SELECT'成绩插入成功！'; ENDIF;END$$DELIMITER;事务应用实例说明：（a）局部变量xf：存放kch对应课程学分。（b）事务处理：定义SQL事务出错局部变量（transErr），初始值为0（b.1）；定义SQL事务出错处理方法（CONTINUE：继续运行），并且执行“SETtransErr=1”语句（b.2）；开始事务（b.3）；如果SQL事务出错，回滚到事务前状态（b.4）；否则事务提交确认（b.5）。（c）存储过程功能：查询kch对应课程学分（c.1）；插入成绩记录（c.2）；如果成绩cj大于等于60，学生表对应xh学生的总学分加课程学分（c.3）。（d）等待10s，如果MySQL数据库不在本机，可以在这期间断开本机网络来模拟网络故障，这时INSERT语句已经执行，而UPDATE没有执行。事务应用实例2.执行含事务程序SET@@sql_mode='STRICT_TRANS_TABLES'; #（a）SET@@AUTOCOMMIT=0; #（b）USExscj;CALLinput_cj('201101','302',86); #（c）CALLinput_cj('201188','302',70); #（d）说明：（a）设置系统为严格模式，使不合法数据不能插入（b）事务处理程序（SET@@AUTOCOMMIT=0）:使SQL语句对数据库所做的修改持久化。（c）调用存储过程input_cj()。操作结果显示“成绩插入成功！”。（d）调用存储过程input_cj()。操作结果显示“成绩插入不成功！”。事务应用实例3.事务功能测试修改存储过程体如下：BEGIN DECLARExfint(1); #（a）DECLAREEXITHANDLERFORSQLEXCEPTIONROLLBACK; #（b.2）

STARTTRANSACTION; #（b.3） SELECT学分INTOxfFROMkcWHERE课程号=kch; #（c.1） INSERTINTOcjVALUES(xh,kch,xf); #（c.2） SELECTSLEEP(10); #（d） IFcj>=60THEN #（c.3） UPDATExsSET总学分=总学分+xfWHERE学号=xh; #（c.3） ENDIF; COMMIT;

SELECT'成绩插入成功！';END$$05事务隔离级事务隔离级基于ANSI/ISOSQL规范，MySQL提供了4种隔离级，语句如下。SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVELSERIALIZABLE |REPEATABLEREAD |READCOMMITTED |READUNCOMMITTED说明：（1）隔离级选项如下。SERIALIZABLE（序列化）：用户之间通过一个接一个顺序地执行当前的事务提供事务之间最大限度的隔离。REPEATABLEREAD（可重复读）（系统默认）：事务不会被看作一个序列，但当前执行事务时的变化仍然不能看到，也就是说，如果用户在同一个事务中执行同条SELECT语句数次，结果总是相同的。READCOMMITTED（提交读）：隔离级的安全性比可重复读要差。不仅处于这一级的事务可以看到其他事务添加的新记录，而且其他事务对现存记录做出的修改一旦被提交，也可以看到。READUNCOMMITTED（未提交读）：提供了事务之间最小限度的隔离。事务隔离级当用户从无保护的未提交读隔离级转移到更安全的序列化隔离级时，RDBMS的性能也要受到影响。原因很简单：用户要求系统提供越强的数据完整性，它就越需要做更多的工作，运行的速度也就越慢。图中列出了事务隔离级和性能之间的关系。事务隔离级（2）如果指定GLOBAL，那么定义的隔离级将适用于所有的用户；如果指定SESSION，则隔离级只适用于当前运行的会话和连接。（3）系统变量@@TX_ISOLATION中存储了事务的隔离级，默认情况下，这个系统变量的值是基于每个会话设置的，但是可以通过向SET命令行添加GLOBAL关键字修改该全局系统变量的值。（4）只有支持事务的存储引擎才可以定义隔离级。定义隔离级可以使用SETTRANSACTION语句。第10章

事务管理和多用户操作——多

用

户01锁定的级别锁定的级别对于不同的表类型，锁定机制也是不同的，因此理解不同级的锁定是使用MySQL的非事务表实现伪事务环境的基本条件。表锁定：一个特殊类型的访问，整个表被用户锁定。其他用户不能向表中插入记录，甚至从中读数据也受到限制。页锁定：MySQL将锁定表中的某些行（称作页）。被锁定的行只对锁定最初的线程是可写的。如果另外一个线程想要向这些行写数据，它必须等到锁被释放。不过，其他页的行仍然可以使用。行锁定：行锁定比表锁定或页锁定对锁定过程提供了更精细的控制。在这种情况下，只有线程使用的行是被锁定的，表中的其他行对于其他线程都是可用的。在多用户环境中，行锁定降低了线程间的冲突，可以使多个用户同时从一个相同表读数据甚至写数据。死锁：如果很多用户同时访问数据库，一个常见的现象就是死锁。简单地说，如果两个用户相互等待对方的数据，就会产生一个死锁。假设用户U1在R1行上定义了一个锁，并且希望在R2行上也放置一个锁，而用户U2是R2行上的一个锁的拥有者，并且希望在R1行上也放置一个锁，则这两个用户相互等待产生死锁。02锁定的级别全局锁表锁行锁锁定与解锁1.全局锁全局锁就是对整个数据库实例加锁，一般用于全库逻辑备份。全局锁让整个数据库（所有表）处于只读状态，使用这个命令后，数据库表的增删改（DML）、表结构的更改（DDL）、更新事务的提交都会被阻塞，但查询是允许的。MySQL采用下列语句加全局锁（FTWRL）。FLUSHTABLEWITHREADLOCK;当需要让整个库处于只读状态的时候，就可以使用这个命令，之后所有线程的更新操作都会被阻塞。下列语句释放所有锁:UNLOCKTABLES;但若在执行FTWRL命令之后由于客户端发生异常而断开，MySQL会自动释放这个全局锁，整个库可以回到正常更新的状态。锁定与解锁【例】全局锁测试。打开test数据库中的sp表，在浏览时增加、修改或删除表中记录，保存时系统就会显示错误信息。例如：FLUSHTABLEWITHREADLOCK;USEtest;INSERTINTOspVALUES(3,38.50);显示错误信息如图。释放所有锁，插入2条记录：UNLOCKTABLES;INSERTINTOspVALUES(3,38.50);INSERTINTOspVALUES(4,19.00);锁定与解锁2.表锁在执行修改表的的时候，一般都会给表加上表锁，可以避免一些不同步的事情出现。表锁分为两种，一种是读锁，一种是写锁。1）读锁写锁效果加读锁（共享锁）：LOCKTABLES表名READ其中，READ为加读锁。（1）加读锁的这个进程可以读（如SELECT）加读锁的表，但是不能读其他的表。（2）加读锁的这个进程不能更新（UPDATE、DELETE）加读锁的表。（3）其他进程可以读加读锁的表（因为是共享锁），也可以读其他表。其他进程更新加读锁的表会一直处于等待锁的状态，直到锁被释放后才会更新成功。加写锁（独占锁）：LOCKTABLES表名WRITE（1）加锁进程可以对加锁的表做任何操作（CURD）。（2）其他进程则不能查询加锁的表，需等待锁释放。锁定与解锁2）隐含加和释放表锁当要对表做结构变更的时候，自动加写锁。表加锁不仅会限制其他线程的读写，也会限制本线程接下来的操作。在客户端断开的时候自动释放表锁。3）查看加锁的表SHOWOPENTABLES4）分析加锁的表信息SHOWSTATUSLIKE'TABLE%'其中：Table_locks_immediate：产生表锁定的次数，每立即获取锁值加1。Table_locks_waited：出现表锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表锁争用情况。锁定与解锁【例】表锁测试。（1）在当前会话查询窗口执行下列语句。USEtest;LOCKTABLESspREAD;SELECT*FROMsp; #（a）UPDATEspSETprice=price*0.9 WHEREid=2; #（b）运行时可以执行SELECT语句，不能执行UPDATE语句（显示错误），如图。

锁定与解锁（2）在当前会话查询窗口执行下列语句。USEtest;LOCKTABLESspWRITE;UPDATEspSETprice=price*0.9 WHEREid=2;SELECT*FROMsp;可以运行UPDATE语句和SELECT语句，如图。此时在另一个会话查询窗口，执行下列语句查询sp表记录将处于等待状态。SELECT*FROMtest.sp;（3）在当前会话查询窗口执行下列语句。USEtest;UNLOCKTABLES;锁定与解锁3．行锁行锁偏向InnoDB存储引擎，开销大、加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。不支持行锁的存储引擎意味着并发控制只能使用表锁，同一张表任何时刻只能有一个更新在执行，这就会影响到业务并发度。一个事务不管持有几个行锁，都是在执行COMMIT的时候才一起释放。在InnoDB事务中，行锁在需要的时候才加上，比如一个事务有n1行和n2行两个UPDATE语句，行锁是在执行到n1行和n2行UPDATE语句时才分别加上的，且也不是语句执行后就立刻释放，而是等到事务结束时才一起释放，这就是两阶段锁协议。InnoDB的行锁是针对索引加的锁，只要更新条件与索引项一致，对记录加行锁是系统自动进行的。锁定与解锁【例】行锁测试。当前有两个会话对数据库sp表相同（id=1）的记录进行修改，但修改的@myprice值是不同会话输入的。1）运行测试先执行下列语句，设置id=2价格：USEtest;UPDATEspSETprice=40WHEREid=2;2）创建两个查询编辑窗口，分别输入会话1事务和会话2事务程序：会话1事务：USEtest;STARTTRANSACTION;SELECT*FROMsp WHEREid=2; #（a.1）SET@myprice=38.00; #（b.1）UPDATEspSETprice=@mypriceWHEREid=2;SELECTSLEEP(10); #（c.1）COMMIT;SELECT*FROMsp WHEREid=2; #（d.1）锁定与解锁会话2事务：USEtest;STARTTRANSACTION;SELECT*FROMsp WHEREid=2; #（a.2）SET@myprice=46.00; #（b.2）UPDATEspSETprice=@mypriceWHEREid=2;SELECTSLEEP(1); #（c.2）COMMIT;SELECT*FROMsp WHEREid=2; #（d.2）说明：（a）查询进入事务前的2号商品价格（price）。（b）用变量赋不同值模拟不同用户运行这个程序时输入2号商品不同价格。（c）模拟不同用户在不同操作时网络数据库时间差异。SLEEP(10)等待10s，这样就可观察到会话1事务执行没有完成，会话1只能等待的情况。（d）查询进入事务完成后的2号商品价格（price）。锁定与解锁3）先执行会话1程序，再执行会话2程序。如图。锁定与解锁4）运行结果及分析会话1进程：a.1处显示id=2，price=40；d.1处显示id=2，price=38。会话2进程：a.1处显示id=2，price=40；d.1处显示id=2，price=46。尽管会话2进程比会话1进程迟几秒运行，但由于会话1进程修改id=2记录的事务没有提交前处于锁定状态，所以会话2进程a.1处读取的不是会话1进程修改后的值，而是与会话1进程进入事务前的值相同。而d.1和d.2为各自进程修改的值。但是，如果注释c.2语句使会话2进程在此处不延时，则有可能d.1也显示id=2，price=46。03死

锁死锁如果事务A和事务B操作同一个数据库中的同一个表，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁，事务A和事务B在互相等待对方的资源释放，于是进入了死锁状态。如表。