《数据库原理及应用》第2版 课件 第11章 事务及其并发控制

《数据库原理及应用》数据库虚拟教研室制作数据库目录01

数据一致性问题02事务概念03JDBC事务04事务的特性05事务的并发问题06事务的隔离级别07事务并发控制第11讲事务及其并发控制数据一致性问题数据一致性是指关联数据之间的逻辑关系是否正确和完整，当数据库中的数据从一个状态转变到另一个状态时，要能保证数据前后的一致性。数据库系统中的一系列约束可以解决部分一致性需求，如主键约束、外键约束、唯一约束、非空约束和检查约束等。在数据库中，一个动作是指一个增、删、改语句，当有多个动作去完成一个任务时（如银行转帐），如果不加约束就可能产生数据不一致性问题。数据不一致性举例

【例】：银行财务转账，A账户转600元到B账户。A账户B账户600400160010001000A账户B账户60040022004001600第一次转帐第二次转帐数据不一致性举例DELIMITER$$CREATEPROCEDUREp_transfer(INfrom_accountINT,INto_accountINT,INamountDECIMAL(10,2))BEGINUPDATEaccountSETbalance=balance+amountWHEREaccount_id=to_account;UPDATEaccountSETbalance=balance-amountWHEREaccount_id=from_account;END$$

CREATETABLEaccount(account_idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,account_nameVARCHAR(20)NOTNULL,balanceDECIMAL(10,2)DEFAULT'0.00'CHECK(balance>=0))AUTO_INCREMENT=202201;

【例11.1】某银行数据库（bank）中的账户表（account），其中账户余额（balance）字段值不能为负数。要求设计一个存储过程p_transfer用于不同客户间的转账交易，以保证账户数据的一致性。事务概念事务（Transaction）是指一个动作或一系列动作的组合，这些动作被当作一个整体或一个逻辑单元来处理，这些动作要么都执行，要么都不执行。系统变量AUTOCOMMIT控制事务的提交方式，ON为自动提交，即每执行完一个动作（一个动作开启一个事务），系统自动提交；OFF为手动提交，即必须用COMMIT或ROLLBACK结束当前事务，开始事务：使用BEGIN或STARTTRANSACTION命令开启事务。提交事务(COMMIT)：这些动作全部成功地执行，即此时整个活动序列的所有执行结果都被保存到数据库中。回滚事务(ROLLBACK)：这些动作都不执行并恢复到执行前的状态。解决数据不一致性举例DELIMITER$$DROPPROCEDUREIFEXISTSp_transfer$$CREATEPROCEDUREp_transfer(INfrom_accountINT,INto_accountINT,INamountDECIMAL(10,2))BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONROLLBACK;STARTTRANSACTION;UPDATEaccountSETbalance=balance+amountWHEREaccount_id=to_account;UPDATEaccountSETbalance=balance-amountWHEREaccount_id=from_account;COMMIT;END$$

【例11.2】采用MySQL中的事务解决【例11.1】中的转账问题，即在存储过程p_transfer中加入事务控制。事务的提交与回退【例11.3】：只撤消事务的一部分updateaccountsetbalance=balance-10000whereaccountID='A';savepointt;updateaccountsetbalance=balance+10000whereaccountID='B';rollbacktot;ROLLBACK说明：

在尚未对数据库提交的时候，可以用事务回退命令ROLLBACK，将数据库回退到上次COMMIT后的状态。一旦事务已经提交，就不能再使用事务回退命令进行回退了。事务回退将回退整个事务，如果只需回退事务的一部分时，可以使用保存点，将整个事务划分为若干部分，这样就可以回退部分事务了。JDBC事务

实际上，一个Java应用系统，通常分前端与后端两个部分，前端和后端都可以实现事务管理，应用系统对事务的控制通常放在前端来实现。如果前端使用JAVA编程，前端事务就称为JDBC事务。数据库客户端工具（SQLyog）DBA数据库开发工具（Java）程序员JDBCmysql存储过程JDBC事务JDBCConnection接口提供了相应的方法来管理事务:publicvoidsetAutoCommit(boolean)//设置提交方式publicbooleangetAutoCommit()//获取当前的提交方式publicvoidcommit()//提交publicvoidrollback()//回滚JDBC事务

【例11.4】：用JDBC事务来实现银行财务转账。try{conn.setAutoCommit(false);stmt=conn.createStatement();Stringsql1="updateaccountsetbalance=balance-600whereaccount_id=202201";stmt.executeUpdate(sql1);Stringsql2="updateaccountsetbalance=balance+600whereaccount_id=202202";stmt.executeUpdate(sql2);

mit();}catch(SQLExceptione){ e.printStackTrace();try{

conn.rollback();}} JDBC事务思考题：在数据库应用系统中，事务处理放在前端和后端，哪个更好？答：从数据库系统的可移植性来说，放在前端比放在后端好。因为后端一般通过数据库编程来进行控制，而不同工具如MySQL、SQLServer、Orale等编程语法差别较大，可移植性差。事务的特性①原子性（Atomicity）:事务是一个不可分隔的整体，事务中的语句要么都执行，要么都不执行；②一致性（Consistency）：事务执行的结果必须使数据库从一个一致性状态变为另外一个一致性状态；③隔离性（Isolation）：指并发事务之间的干扰性，有具体的隔离级别决定；④持续性（Durability）：事务对数据库的更改是永久的，能够保证已经成功提交的事务持久化到磁盘中。事务并发引发的数据不一致性并发操作：在多用户共享系统中，如果多个用户同时对同一个数据进行操作，称为多事务并发操作。A事务B事务（1）丢失更新（3）不可重复读（2）读“脏”数据（4）幻读事务并发引发的数据不一致性（1）丢失更新事务并发引发的数据不一致性（2）读“脏”数据事务的并发引发的数据不一致性（3）不可重复读事务并发引发的数据不一致性（4）幻读事务隔离级别事务隔离级别（TransactionIsolationLevel）：指一个事务对并行的另一个事务的隔离程度。为避免并发事务执行中可能出现丢失更新、脏读、不可重复读和幻读等现象，数据库系统提供了不同级别的事务隔离，防止事务之间的相互影响：（1）读未提交（READUNCOMMITTED）；（2）读已提交（READCOMMITTED）；（3）可重复读（REPEATABLEREAD）；（4）串行化（SERIALIZBALE）。设置事务隔离级别：SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVEL事务隔离级别举例--创建u1,u2两个用户CREATEUSERu1IDENTIFIEDBY'u1';CREATEUSERu2IDENTIFIEDBY'u2';--修改两个用户的加密插件ALTERUSERu1IDENTIFIEDWITH'mysql_native_password'BY'u1';--给两个用户授予bank.account操作的所有权限GRANTALLPRIVILEGESONbank.accountTOu1;GRANTALLPRIVILEGESONbank.accountTOu2;--用这两个用户创建两个新连接创建两个新用户，并创建两个新连接，为模拟并发事务做好准备工作。【例11.5】account表中A账户的余额初值设为1000。事务T2的隔离级别设置为读未提交，开启两个事务，按表中的时间顺序执行语句，观察事务T2两次读取的余额。事务隔离级别举例【例11.6】account表中A账户的余额初值设为100。事务T2的隔离级别设置为读已提交，开启两个事务，按表中的时间顺序执行语句，观察事务T2两次读取的余额。事务隔离级别举例【例11.7】account表中A账户的余额初值设为100。事务T2的隔离级别设置为可重复读，开启两个事务，按表中的时间顺序执行语句，观察事务T2两次读取的余额。事务隔离级别举例【例11.8】account表中A账户的余额初值设为100。事务T2的隔离级别设置为可重复读，开启两个事务，按表中的时间顺序执行语句，观察事务T2两次读取的记录。事务隔离级别举例【例11.9】account表中A账户的余额初值设为100。事务T2的隔离级别设置为串行化，开启两个事务，按表中的时间顺序执行语句，观察事务T2读取的记录。事务隔离级别举例事务隔离级别与数据不一致性事务隔离级别思考题：不同的事务隔离级别在数据库系统该如何选择？答：数据库管理系统一般都把事务的隔离级别设置为ReadCommitted;MySQL使用ReadCommited隔离级别进行主从复制时会出现数据不一致问题，因此MySQL将RepeatableRead作为默认的隔离级别。事务并发控制事务的并发控制是指在数据库管理系统中，为了保证多个事务同时执行时数据的一致性和完整性而采取的技术手段，使用不同的隔离级别来控制，实现这些隔离级别的主要技术手段包括封锁机制、多版本并发控制等。封锁：封锁是最传统的也是最广泛使用的并发控制技术。它通过在数据项上加锁来控制事务对数据的访问，确保事务的隔离性。多版本并发控制：通过维护数据的多个版本来实现并发控制，每个事务看到的是一个特定时间点的数据快照。封锁封锁机制用于管理对共享资源的并发访问，实现数据的完整性和一致性。他是数据库系统区别于文件系统的一个关键特性。数据库系统中常见的锁按照封锁粒度划分为：表级锁、页级锁、行级锁。（1）表级和页级锁的主要特点：开销小、加锁快，不会出现死锁，但由于其封锁粒度大，发生锁冲突的概率较高，抗并发能力较弱。（2）行级锁的主要特点：开销大、加锁慢，会出现死锁，但由于其封锁粒度小，发生锁冲突的概率较低，抗并发能力较强。行锁又分为读锁和写锁。行锁读锁，又称共享锁（SharedLock，S锁）：封锁粒度是一行或多行，多个不同的事务对同一个资源可加多个S锁。如果事务T1对记录R加上了S锁，则执行原则如下：①其他事务只能对记录R加S锁，不能再加其他锁；②对于被加上S锁的数据，用户只能进行读操作，不能进行写操作；③如果要修改数据，必须等S锁释放。设置S锁的语法格式：SELECT字段名列表FROM表名WHERE条件LOCKINSHAREMODE;行锁【例11.10】以账户表account表为例，事务T1、T2同时对记录“A”加S锁，按表11.7中的时间顺序执行语句，验证S锁的执行原则。行锁写锁，又称排他锁（ExclusiveLock，X锁）：封锁粒度是一行或者多行。如果事务T1对记录R加上了X锁，则执行原则如下：①事务T1可以对记录R进行读操作和写操作；②其他事务不能对记录R施加任何类型的锁，而且无法进行增加、删除、修改操作，直到事务T1对记录R加的X锁被释放操作；设置X锁的语法格式：SELECT字段名列表FROM表名WHERE条件FORUPDATE;行锁【例11.11】以账户表account表为例，事务T1对记录“A”加X锁，事务T2对记录“A”加S锁，然后事务T1修改该记录。按表中的时间顺序执行语句，验证S锁的执行原则。封锁协议多个事务并发执行，若不加控制，可能会产生数据不一致性问题。为了解决这些问题，在运用X锁和S锁对数据对象加锁时，还需要约定一些规则。例如，何时申请X锁或S锁、持锁时间、何时释放等，这些规则称为封锁协议，下面介绍三级封锁协议：一级封锁协议：事务T在更新数据R之前必须先对其加X锁，直到事务结束才释放。一级封锁协议可防止“丢失更新”。为了说明方便，做如下约定：使用LockX(A)表示对数据对象A加X锁,使用LockS(A)表示对数据对象A加S锁，使用Unlock(A)表示释放数据对象A上的封锁,使用Read(A)表示读取数据对象A的值，使用Update(A)表示更新数据对象A的值。封锁协议【例11.12】以账户表account表为例，事务T1、T2同时对记录A的余额（balance=1000）进行更新，使用一级封锁协议防止“丢失更新”。封锁协议二级封锁协议是指在一级封锁协议的基础上，增加事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。二级封锁协议除了防止“丢失更新”，还可进一步防止“脏读”。【例11.13】以账户表account表为例，事务T1对记录A的余额进行更新，然后再撤销，事务T2对读取记录A的余额，使用二级封锁协议防止“脏读”。封锁协议三级封锁协议是指在一级封锁协议的基础上，增加事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。三级封锁协议除了防止“丢失更新”和“脏读”外，还进一步防止了“不可重复读”。【例11.14】以账户表account表为例，事务T1对记录A的余额进行更新，然后再撤销，事务T2读取记录A的余额，使用三级封锁协议可进一步防止“不可重复读”。封锁协议在三级封锁协议中，不能避免事务在表中插入记录，所以它可能会出现“幻读”。上述三级封锁协议的主要区别在于分别约定了什么操作需要申请封锁，以及何时释放锁。三级封锁协议使事务达到的一致性级别是不同的，不同封锁协议所对应的事务隔离级别也不同。MVCC机制问题：封锁机制在并发控制中的效率如何？

封锁机制的优势在于其能够直接控制数据的访问权限，确保数据的一致性和完整性。然而，在高并发环境下，封锁机制容易导致性能瓶颈、锁争用和死锁等并发问题，尤其是一个对象的更新和读取操作不允许同时进行，会直接影响系统效率。

MVCC机制是一种更为现代的并发控制方式，对同一个记录多次更新将会形成一个版本链，它通过版本链来实现读写操作的并发执行。在MVCC机制下，每个事务都会看到一个独立的数据快照，读操作可以直接基于快照读取数据，而不需要等待写操作完成或获取锁。MVCC原理2.活跃事务及版本链活跃事务是指已经开始但还没有结束（Commit/Rollback）的事务。表中的每条记录某时刻都可能存在0至多个活跃事务。系统为活跃事务分配事务号（trx_id），且事务号由系统变量控制，按递增顺序产生。1.记录中的隐藏列每张表的聚簇索引记录中都包含两个隐藏列:trx_id：每次更新记录的事务号。roll_pointer：每次对某条聚簇索引记录进行更新时，都会把旧的版本写入到undo日志中，然后将日志地址保存在隐藏列中，这个隐藏列就相当于一个指针，可以通过它来找到该记录更新前的信息。对同一个记录多次更新将会形成一个版本链。MVCC原理【例11.15】以账户表account表为例，假设account表中初始有3条记录，数据如表所示。假设T10、T20两个事务分别对A记录进行更新操作，更新流程如下表所示，画出A记录的版本链。MVCC原理t1:T10和T20事务开始前，当前没有任何事务在更新A记录,A记录上的活跃事务为0，t2:T10和T20对A记录执行了更新操作，T10和T20都为A记录上的活跃事务。t3:T10再次更新了A记录，A记录版本链如图所示。MVCC原理t4:T10执行了提交（Commit）操作，此时，A记录上的活跃事务只有T20。t5:

T20更新了A记录，A记录上的活跃事务为T20，记录版本链如图所示。t6:

T20执行了提交（Commit）操作，此时，A记录上没有活跃事务。MVCC原理3.ReadViewReadView又称读视图或快照，是一个事务在执行快照读时所依据的数据版本集合。当一个事务开始时，它会根据当前系统中存在的所有活跃事务创建一个读视图。这个读视图决定了事务能看到哪些数据版本。ReadView中主要包含3个重要的参数：m_ids[]：表示在生成ReadView时当前系统中活跃事务的id列表。min_trx_id：表示在生成ReadView时当前系统中活跃事务中最小的id，也就是m_ids[]中的最小值。max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。MVCC原理4.多版本并发控制算法当事务执行快照读（如SELECT操作）时，数据库管理系统会生成一个当前的快照，其中记录和维护了当前活跃事务的id及相关信息，通过比较数据记录的事务版本号和ReadView中的事务号，可以确定哪些版本的数据对当前事务是可见的。MVCC原理5.

MVCC在不同隔离级别中的应用READUNCOMMITTED和SERIALIZABLE隔离级别的事务，通过封锁来实现控制，不受MVCC机制控制。READCOMMITTED和REPEATABLEREAD隔离级别的事务，使用封锁来更新记录，使用MVCC机制能够确保在记录正在被更新时