第11章 事务和锁

第11章事务和锁《MySQL数据库应用教程》刘瑞新主编配套资源目录第11章事务和锁11.1事务11.2锁11.1.1事务的概念1.事务的基本概念2.事务的基本特性（1）原子性（Atomicity）（2）一致性（Consistcncy）（3）隔离性（Isolation）（4）持久性（Durability）11.1事务3.事务的分类（1）自动提交事务（2）显式事务（3）隐式事务（4）分布式事务11.1事务4.事务的必要性【例11-1】假设一个银行的数据库中，有一张账户表，保存着两张借记卡账户A和B，并且要求这两张借记卡账户部不能透支，即两个账户的余额不能小于零。A账户和B账户的余额都是1000元，A向B转账200元，则需要6个步骤：1）从账户A中读取余额为1000，即A=1000。2）账户A的余额减去200，即A=A-200。3）账户A的余额写入为800。4）从账户B中读取余额为l000，即B=1000。5）账户B的余额加上200，即B=B+200。6）账户B的余额写入为1200。11.1事务对应以上6个步骤理来解事务的4个属性，具体如下。1）原子性2）一致性3）隔离性4）持久性11.1事务11.1.2事务的基本操作1.设置事物的自动提交模式（1）查看系统变量@@autocommit的值SHOWVARIABLESLIKE'autocommit';11.1事务（2）设置事务的自动提交模式语句SETAUTOCOMMIT=0|1|ON|OFF;2.开启事务STARTTRANSACTION|BEGINWORK;3.提交（结束）事务COMMIT;11.1事务【例11-2】在studentinfo数据库中新建一个银行表bank，表列的定义包括账户编号id（INT，主键）、账户名name（VARCHAR(20)）和账户余额money（DECIMAL(10,2)）。1）USEstudentinfo;CREATETABLEbank(idINTPRIMARYKEY,nameVARCHAR(20),moneyDECIMAL(10,2));2）INSERTINTObank(id,name,money)VALUES(101,'张三',1000),(102,'李四',1000),(103,'王五',1000);3）SELECT*FROMbank;11.1事务【例11-3】在bank表中，通过事务把张三的300元转给李四，模拟自动回滚事务。1）STARTTRANSACTION;2）UPDATEbankSETmoney=money-300WHEREname='张三';3）UPDATEbankSETmoney=money+300WHEREname='李四';4）SELECT*FROMbank;

11.1事务5）6）SELECT*FROMbank;

11.1事务【例11-4】在bank表中，通过事务把张三的300元转给李四，完成提交事务。1）2）COMMIT;3）SELECT*FROMbank;11.1事务4.回滚（撤销）事务ROLLBACK;【例11-5】在bank表中，通过事务把王五的200元转给李四，然后回滚所有事物。1）STARTTRANSACTION;2）UPDATEbankSETmoney=money-200WHEREname='王五';3）UPDATEbankSETmoney=money+200WHEREname='李四';11.1事务4）SELECT*FROMbank;

5）ROLLBACK;6）SELECT*FROMbank;11.1事务11.1.3设置事务的保存点1.设置事务保存点SAVEPOINTidentifier;2.回滚到一个事务保存点ROLLBACKTOSAVEPOINTidentifier;3.删除已命名的保存点RELEASESAVEPOINTidentifier;11.1事务【例11-6】在bank表中，设置事务的保存点。1）STARTTRANSACTION;2）UPDATEbankSETmoney=money-500WHEREname='张三';SELECT*FROMbank;3）SAVEPOINTs1;11.1事务4）UPDATEbankSETmoney=money-100WHEREname='张三';SELECT*FROMbank;5）ROLLBACKTOSAVEPOINTs1;SELECT*FROMbank;11.1事务6）ROLLBACK;SELECT*FROMbank;

8）COMMIT;11.1事务11.1.4事务的隔离级别MySQL的4种隔离级别1）READUNCOMMITTED（读未提交）2）READCOMMITTED（读已提交）3）REPEATABLEREAD（可重复读）4）SERIALIZABLE（可串行化）11.1事务2.查看隔离级（1）查看全局隔离级SELECT@@global.transaction_isolation;（2）查看当前会话中的隔离级别SELECT@@session.transaction_isolation;11.1事务（3）查看下一个事务的隔离级别SELECT@@transaction_isolation;11.1事务3.设置隔离级别SET[{SESSION|GLOBAL}]TRANSACTIONISOLATIONLEVEL参数值;【例11-7】将当前会话事务的隔离级别修改为READUNCOMMITTED，然后将当前会话的事务级别修改为默认的REPEATABLEREAD。1）SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;SELECT@@session.transaction_isolation;11.1事务2）SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SELECT@@session.transaction_isolation;11.1事务4.定义事务的访问模式（1）设置只读事务SET[{SESSION|GLOBAL}]TRANSACTIONREADONLY;（2）恢复成读写事务SET[{SESSION|GLOBAL}]TRANSACTIONREADWRITE;11.1事务5.事务的隔离级别示例（1）读取未提交（READUNCOMMITTED）-“脏读”问题【例11-8】脏读问题演示示例。王五转账给李四100元货款，李四看到货款到账后给王五发货，王五撤销了转账。1）#客户端A，李四所在的客户端SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;2）USEstudentinfo;SELECT*FROMbankWHEREname='李四';11.1事务11.1事务3）#客户端B，王五所在的客户端USEstudentinfo;STARTTRANSACTION;SAVEPOINTs1; #创建转账前的事务保存点UPDATEbankSETmoney=money-100WHEREname='王五';UPDATEbankSETmoney=money+100WHEREname='李四';#为了读者看到数据的全貌，同时显示二者的记录SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事务11.1事务4）#为了读者看到数据的全貌，同时显示二者的记录SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事务5）ROLLBACKTOSAVEPOINTs1;COMMIT;SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事务6）SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事务（2）读已提交（READCOMMITTED）-不可重复读问题、幻读问题【例11-9】不可重复读问题演示示例。王五取出资金500元，资金管理者在同一个事务的两个时刻读出了不同的数据。1）#客户端A，资金管理者SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;11.1事务2）USEstudentinfo;STARTTRANSACTION;SELECT*FROMbank;SELECTSUM(money)FROMbank;11.1事务3）#客户端B，王五所在的客户端USEstudentinfo;STARTTRANSACTION;UPDATEbankSETmoney=money-500WHEREname='王五';COMMIT;11.1事务4）SELECT*FROMbankt;SELECTSUM(money)FROMbank;11.1事务【例11-10】幻读问题演示示例。在客户端A中登录资金管理者。在客户端B中操作新增账户丁一，存款3000元。1）#客户端A，资金管理者SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;2）USEstudentinfo;SELECTCOUNT(*)AS账户数,SUM(money)AS余额总数FROMbank;11.1事务3）USEstudentinfo;STARTTRANSACTION;INSERTINTObank(id,name,money)VALUES(105,'丁一',3000);COMMIT;SELECT*FROMbank;11.1事务4）SELECTCOUNT(*)AS账户数,SUM(money)AS余额总数FROMbank;11.1事务（3）可重复读（REPEATABLEREAD）【例11-11】可重复读演示示例。在客户端A登录资金管理者，在客户端B中操作账户李四，李四取款1000元。1）#客户端A，资金管理者SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;2）USEstudentinfo;SELECTCOUNT(*)AS账户数,SUM(money)AS余额总数FROMbank;SELECT*FROMbank;11.1事务3）#客户端B，李四取款1000元USEstudentinfo;STARTTRANSACTION;UPDATEbankSETmoney=money-1000WHEREname='李四';SELECT*FROMbank;11.1事务4）SELECTCOUNT(*)AS账户数,SUM(money)AS余额总数FROMbank;SELECT*FROMbank;11.1事务5）COMMIT;6）SELECTCOUNT(*)AS账户数,SUM(money)AS余额总数FROMbank;SELECT*FROMbank;11.1事务【例11-12】在可重复读隔离级别下演示幻读问题的示例。在客户端A中登录资金管理者，在客户端B中操作删除账户丁一。1）SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;2）USEstudentinfo;SELECT*FROMbank;11.1事务3）USEstudentinfo;STARTTRANSACTION;DELETEFROMbankWHEREname='丁一';SELECT*FROMbank;11.1事务4）SELECT*FROMbank;11.1事务5）COMMIT;6）SELECT*FROMbank;11.1事务（4）SERIALIZABLE（可串行化）-可串行化问题【例11-13】在可串行化隔离级别下数据库运行过程的示例。在客户端A登录张三，设置未可串行化，查询张三记录；在客户端B登录李四，李四给