10.3锁 (课件）-《网站数据库应用技术-SQL Server》同步教学（电子工业版）

游标、事务和锁 第十章 03 锁 锁 03 并发问题 1．丢失更新 当两个或多个事务选择同一行,并基于最初选定的值更新该行时,会发生丢失更新问题。每个事务都不知道其他事务的存在，最后的更新将重写由其他事务所做的更新，这将导致数据丢失。 锁 03 并发问题 2．未确认的相关性（脏读） 当一个事务正在访问数据，对数据进行了修改，而这种修改还没有提交到数据库中时，而另外一个事务也访问这个数据，并且使用了这个数据。因为这个数据是还没有提交的数据，那么第二个事务读到的这个数据就是脏数据,依据脏数据所做的操作可能是不正确的。 锁 03 并发问题 3．不一致的分析（非重复读） 在一个事务还没有结束时，另一个事务也访问同一数据，这样，在第一个事务中的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一致的。这样就使得第一个事务内两次读到的数据是不一致的，因此称为不可重复读取。 锁 03 并发问题 4．幻象读 当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻象读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。 锁 03 锁的类型 SQL Server数据库引擎使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。数据库引擎使用的资源锁模式如表10-3所示。 锁 03 锁的类型 1．共享锁 共享锁用于只读数据操作，它允许多个并发事务读取所锁定的资源，但禁止其他事务对锁定资源的进行修改操作。默认情况下，当读取数据的事务读完数据之后，会立即释放所占用的资源。一般而言，当使用SELECT语句访问数据时，系统自动对所访问的数据使用共享锁锁定。 锁 03 锁的类型 2．排他锁 对于那些修改数据的事务，如INSERT、UPDATE和 DELETE语句，系统自动在所修改的事务上放置排他锁。排他锁就是在同一时间内只允许一个事务访问一种资源，其他事务都不能在有排他锁的资源上访问。只有当产生排他锁的事务结束之后，排他锁锁定的资源才能被其他事务使用。 锁 03 锁的类型 3．修改锁 在修改操作的初始化阶段用于锁定可能被修改的资源，以防止使用共享锁可能产生的死锁现象。在使用共享锁时，一个事务对数据修改操作的执行过程如下：事务首先获得一-个共享锁，读取记录，然后将共享锁申请升级为排他锁，再执行数据修改操作。采用这种方式时，如果两个并发事务获得同一个资源的共享锁后都需要修改数据，则两者均申请将共享锁升级为排他锁，这时，双方都不再释放共享锁而一直等对方释放共享锁，从而造成死锁现象。为了避免这种现象的发生，一个数据修改事务在开始时直接申请修改锁，之后，在需要修改数据时，再将修改锁转换为排他锁，或者将修改锁转换为共享锁。 锁 03 锁的类型 4．意向锁 意向锁说明SQL Server有在该锁所锁定资源的低层资源上获得共享锁或排他锁的意向，在表上设置意向锁可以阻止其他事务获得该表的排他锁。意向锁又分为共享意向锁、排他意向锁和共享式排他意向锁三种。 （1）共享意向锁：说明一个事务准备在共享意向锁所锁定资源的低层上放置共享锁来读取资源数据。 （2）排他意向锁：说明一个事务准备在排他意向锁所锁定资源的低层资源上放置排他锁来修改其中的资源数据。 （3）共享式排他意向锁：说明当前事务允许其他事务使用共享锁来并发读取顶层资源，并准备在该资源的低层资源上放置排他意向锁。 锁 03 查看锁 在SQL Server 2019中使用sys.dm _tran locks动态管理视图可以返回有关当前活动的锁管理器资源的信息。同时，也可以使用系统存储过程sp_lock查看锁的信息。 使用系统存储过程sp_lock可以查看SQL Server系统或指定进程对资源的锁定情况，基本语句格式如下: 其中，spid1和 spid2为进程标识号。指定spid1、spid2参数时，SQL Server 显示这些进程的锁定情况，否则显示整个系统的锁使用情况。进程标识号为一个整数，可以使用系统存储过程sp_who检索当前启动的进程及各进程所对应的标识号。 锁 03 设置事务隔离级别 隔离是计算机安全学中的一种概念，其本质上是一种封锁机制。它是指自动数据处理系统中的用户和资源的相关牵制关系，即用户和进程彼此分开且与操作系统的保护控制也分开。在SQL Server中，隔离级别是指一个事务和其他事务的隔离程度，即指定了数据库如何保护锁定那些当前正在被其他用户或服务器请求使用的数据。指定事务的隔离级别与在SELECT语句中使用锁定选项来控制锁定方式具有相同的效果。SQL Server中有以下四种隔离级别。 （1） READ COMMITTED：在此隔离级别下，SELECT命令不会返回尚未提交的数据，也不能返回脏数据，它是SQL Server 默认的隔离级别。 （2）READ UNCOMMITTED：与READ COMMITTED隔离级别相反，它允许读取已经被其他用户修改但尚未提交确定的数据。 锁 03 设置事务隔离级别 （3）REPEATABLE READ：在此隔离级别下，用SELECT命令读取的数据在整个命令执行过程中不会被更改。此选项会影响系统的效能，非必要情况最好不要使用此隔离级别。 （4）SERIALIZABLE：在此隔离级别下，在整个SELECT命令执行的过程中设定的共享锁会一直存在。 隔离级别需要使用SET命令来设定，基本语句格式如下： 锁 03 死锁的处理 1．死锁 在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。死锁状态如图10-11所示。 （1）任务具有资源R的锁(由从指向的箭头指示)，并请求资源的锁(由从指向的箭头指示)。 （2）任务具有资源的锁(由从指向的箭头指示)，并请求资源Ri的锁（由从指向R的箭头指示)。 锁 03 死锁的处理 1．死锁 两个用户分别锁定一个资源,之后双方又都等待对方释放所锁定的资源，就产生了一个锁定请求环，从而出现死锁现象。死锁会造成资源的大量浪费，甚至会使系统崩溃。 在多用户环境下，数据库系统出现死锁现象是难免的。SQL Server数据库引擎自动检测SQL Server中的死锁循环，并选择一个会话作为死锁牺牲品，然后终止当前事务（出现错误）来打断死锁。 锁 03 死锁的处理 2．死锁检测 SQL Server能够自动定期搜索和处理死锁问题。SQL Server在每次定期搜索中标识所有等待锁定请求的会话，如果在下一次搜索中被标识进程仍处于等待状态，则SQL Server将开始递归死锁搜索。当递归死锁搜索检测到锁定请求环时，SQL Server根据各会话的死锁优先级选项等设置结束一个代价最低的事务。之后，SQL Server回滚被中断的事务，并向应用程序返回1205号错误，而未被中断的事务则继续运行。 锁 03 死锁的处理 3．死锁处理 【任务3-3】使用TRY…CATCH进行死锁处理。 （1）设计产生死锁的事务。将下面的SQL语句放在两个不同的连接中，并且在5秒内同时执行，将会发生死锁。 锁 03 死锁的处理 3．死锁处理 （2）SQL Server对死锁的自动处理。SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在上面两个连接的语句执行时，先执行查询的连接能够成功执行，运行结果如图10-12所示。后执行查询的连接被选为牺牲品，运行结果如图10-13所示。在被牺牲的连接中，“PRINT'事务结束'”语句将不会被运行。 锁 03 死锁的处理 3．死锁处理 （3）使用TRY…CATCH处理死锁。在SQL Server 2019中可以通过TRY…CATCH对异常进行捕获，也就提供了一条处理死锁的途径：程序员可以通过该语句捕获事务执行过程中的错误，根据设定的次数进行重试。 锁 03 死锁的处理 锁 03 死锁的处理 3．死锁处理 该事务中由于使用TRY…CATCH进行了错误捕获，因此在两个连接中执行该事务时，后执行的事务在遇到死锁时不会被选为牺牲品，而是在指定的延时后进行重试，等到另一进程释放资源后执行事务。运行结果如图10-14所示。 $$