第12章 事务和锁

第12章事务和锁教学提示：本节从最简单的T-SQL的语法入手由浅入深地讲解事务、锁等，它们是灵活应用T-SQL语句的关键，在程序设计和开发中起着重要的作用。教学目标：通过本节的学习，理解事务、锁等基本原理；能对事务、锁进行简单地运行。数据库是可供多个用户共享的信息资源。允许多个用户同时使用数据库的系统成为多用户数据库系统。当多个用户并发地存取数据库时，就会产生多个事务同时存取同一数据的情况。数据库的并发控制就是控制数据库，防止多用户并发使用数据库时造成数据错误和程序运行错误，保证数据的完整性。事务是多用户系统的一个数据操作基本单元。封锁是使事务对它要操作的数据有一定的控制能力。事务定义：是用户定义的一个数据库操作序列，这些操作要么全做、要么全不做，是构成单一逻辑工作的操作单元。事务的组成:在关系数据库中，一个事务可以是一条SQL语句、一组SQL语句或整个程序。事务和程序是两个概念。一般地讲，一个程序中包含多个事务。12.1事务事务具有4个特性：原子性、一致性、隔离性和持久性。(简记为：ACID)(1)原子性(Atomicity)：事务中包括的所有操作要么都做，要么都不做。也就是说，事务是作为一个整体单位被处理，不可以被分割。例如：银行转帐(2)一致性(Consistency)：事务执行的结果必须使数据库处于一个一致性状态。当数据库中只包含成功事务提交的结果时，就说数据库处于一致性状态。如果故障时有未完成事务，需要撤销（由DBMS完整性控制子系统负责处理）。事务的特性事务具有4个特性：原子性、一致性、隔离性和持久性。(简记为：ACID)(3)隔离性(Isolation)：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。(4)持久性(Durability)：也称：持续性、永久性(Permanence)，指一个事务一旦提交，它对数据库中数据的改变就是永久性的，接下来的其他操作或故障不应该对其执行结果有任何影响。事务的特性事务的管理在SQLServer2008中，对事务的管理包含3个方面：事务控制语句：控制事务执行的语句。包括将一系列操作定义为一个工作单元来处理；锁机制：封锁正被一个事务修改的数据，防止其他用户访问到“不一致”数据；事务日志：使事务具有可恢复性。事务的提交与回滚：事务的开始与结束可以由用户显式定义。如果用户没有显式地定义事务，则由DBMS按默认自动划分事务。在SQL语言中，定义事务的语句有三条：BEGINTRANSACTION；COMMIT；ROLLBACK；事务通常是以BEGINTRANSACTION开始，以COMMIT提交或ROLLBACK回滚结束。每个事务结束，系统都要检查数据的一致性约束。1.事务控制语句1.事务控制语句BEGINTRANSACTION[tran_name]标识一个用户定义的事务的开始。tran_name为事务的名字，标识一个事务开始。COMMITTRANSACTION[tran_name]表示提交事务中的一切操作，结束一个用户定义的事务，使得对数据库的改变生效；ROLLBACKTRANSACTION[tran_name|save_name]回退一个事务到事务的开头或一个保存点。表示要撤销该事务已做的操作，回滚到事务开始前或保存点前的状态；SAVETRANSACTIONsave_name在事务中设置一个保存点，名字为save_name，它可以使一个事务内的部分操作回退。其中，TRANSACTION可简写为TRAN。2.两个可用于事务管理的全局变量两个可用于事务管理的全局变量是@@error和@@rowcount。@@error给出最近一次执行的出错语句引发的错误号，@@error为0表示未出错。@@rowcount给出受事务中已执行语句所影响的数据行数。3.事务控制语句的使用BEGINTRANA组语句序列SAVETRANsave_pointB组语句序列IF@@error0ROLLBACKTRANsave_point/*仅回退B组语句序列*/COMMITTRAN/*提交A组语句，且若未回退B组语句，则提交B组语句*/案例【例12-1】使用事务向表AdminTb中插入数据USELibraryGOBEGINTRANtran_instINSERTINTOAdminTb(Admin,Psw)VALUES('WANG1','ABC')SAVETRANint_pointINSERTINTOAdminTb(Admin,Psw)VALUES('WANG2','123')GO案例GOINSERTINTOAdminTb(Admin)VALUES('WANG3')GOIF@@ERROR0ROLLBACKTRANint_pointGOCOMMITTRANtran_instGO运行结果，消息框中显示“一行受影响”，？？？创建数据库：createdatabase；修改数据库：alterdatabase；删除数据库：dropdatabase；备份数据库：dumpdatabase、backupdatabase；还原数据库：loaddatabase、restoredatabase；日志备份：dumptransaction、backuplog；日志还原：loadtransaction、restorelog；配置：reconfigure；磁盘初始化：diskinit；统计：updatestatistics；显示或设置数据库选项：sp_dboption。4.事务中不可使用的语句（不能够撤消）5.事务回滚机制如果服务器错误使事务无法成功完成，Server将自动回滚该事务，并释放该事务占用的所有资源。如果客户端与Server的网络连接中断了，则网络会通知Server并回滚该连接的所有未完成事务。如果客户端应用程序失败或客户计算机崩溃或重启，也会中断该连接，同Server回滚该连接的所有未完成事务。5.事务回滚机制如果客户从应用程序注销，所有未完成的事务也会被回滚。如果批处理中出现运行时语句错误，那么Server中默认的行为将是只回滚产生该错误的语句。实训准备:以显式事务设计以下事务【例】事务：从[杨百万]账户转给[邱发财]账户8万元。1创建银行账户表createtable银行账户表(账号char(6),账户nchar(10),存款余额money)2.给账户插入信息，如：insert银行账户表(账号,账户,存款余额)values('100001','杨百万',1000000)insert银行账户表(账号,账户,存款余额)values('100002','李有财',80000)insert银行账户表(账号,账户,存款余额)values('100003','邱发财',10)实训准备:以显式事务设计以下事务3.转账（Update操作）update银行账户表set存款余额=存款余额-80000where账号='100001'update银行账户表set存款余额=存款余额+80000where账号='100003'4.设计事务要求：控制转账金额一致（即转入与转出的金额一致）；如果账户余额不够，回滚。12.2锁开发问题：如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题，这些问题包括以下几种情况。1)丢失或覆盖更新2)读脏数据3)不可重复读4)幻觉读并发操作带来的数据不一致问题：并发访问数据库时，如不加以约束控制，就会造成错误存取数据，破坏数据库的一致性。示例：飞机票订票系统机票数：A＝25甲操作事务：T1乙操作事务：T2并发操作可能带来数据不一致的三种情况：时间T1事务T2事务数据库中的A值0251Read(A)2Read(A)3A:=A-14Write(A)5246A:=A-17Write(A)8241．丢失数据事务T1和T2读入同一数据，T2的提交结果破坏了T1提交的结果，导致T1修改的丢失。一级锁协议2．读“脏”数据事务T1读数据，并对其进行修改，未提交时，事务T2又读了同一个数据，由于某种原因T1被撤销，已修改的值恢复成原来的值，结果形成T2读的数据与数据库中的数据不一致，这就是读了“脏”数据，即不正确数据。时间T1事务T2事务数据库中的A值0251Read(A)2A:=A-103Write(A)4155Read(A)6Rollback25二级锁协议3．不可重复读（读过时数据）事务T1和T2读入同一数据，T1处理完更新了数据库（提交了），T2因某种原因未及时处理，持有T1修改前的数据(旧数据)，造成不一致。时间T1事务T2事务数据库中的A值0251Read(A)2Read(A)3A:=A-104Write(A)515三级锁协议4．幻读事务T1删除或修改的记录正在被事务T2读取的数据行范围内。幻觉读是指当事务不是独立执行时发生的一种现象。例如：事务A读取book1表中书的编号为YBZT0001～YBZT0010的多条记录，读取之后，另一事务B将book1表中编号为YBZT0002的记录删除，这时事务A中读到的数据仍包含编号为YBZT0002记录。例如：第一个事务对一个表中的数据进行了修改，这种修改涉及表中的所有数据行；同时，第二个事务也修改了这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。12.2锁为防止出现上述数据不一致的情况，解决的方法：1.采用封锁机制2.使并发的事务串行化，使各事务都按某种次序来进行，这样就会免除相互干扰12.2锁锁是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障。应用程序一般不直接请求锁。锁由数据库引擎的一个部件（称为“锁管理器”）在内部管理。当数据库引擎实例处理T-SQL语句时，数据库引擎查询处理器会决定将要访问哪些资源。查询处理器根据访问类型和事务隔离级别设置来确定保护每一资源所需的锁的类型。然后，查询处理器将向锁管理器请求适当的锁。如果与其他事务所持有的锁不会发生冲突，锁管理器将授予该锁。12.2.1锁的模式MicrosoftSQLServer数据库引擎使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式，见表12-1。P183页表12-1锁的模式几种常用的锁模式1．共享锁（S锁）又称读锁。如果事务T对数据对象A加上共享锁(S锁)，其他事务对A只能再加S锁，不能加X锁，直到事务T释放A上的S锁为止。如果没有其他事务加S锁，T可以继续加X锁，反之，如果有其他事务加S锁，T不可以继续加X锁。用于不更改或不更新数据的操作(只读操作)，如SELECT语句；共享锁允许并发事务读取(SELECT)一个资源。几种常用的锁模式2．排他锁（X锁）也称为独占锁或写锁。一旦事务T对数据对象A加上排他锁(X锁)，则只允许T读取和修改A，其他任何事务既不能读取和修改A，也不能再对A加任何类型的锁，直到T释放A上的锁为止。用于数据修改的操作有很多，例如，INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新，可以使用排他锁。几种常用的锁模式3．更新锁用于可更新的资源中，防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。一次只有一个事务可以获得资源的更新锁。如果事务修改资源，则更新锁转换为排他锁。否则，锁转换为共享锁。死锁封锁机制的引入能解决并发用户的数据不一致性问题，但也会引起事务间的死锁问题。死锁的主要原因是由于两个或更多的事务竞争资源而直接或间接地相互等待而造成的。死锁分类：1.活锁2.死锁加锁出现的问题及解决方法1．活锁如果事务T1封锁了数据R，T2事务又请求封锁R，于是T2等待。T3也请求封锁R，当T1释放了R上的封锁之后系统首先批准了T3的要求，T2仍然等待。然后T4又请求封锁R，当T3释放了R上的封锁之后系统又批准了T4的请求，……，T2有可能永远等待。解决活锁问题