第9章 事务与锁.ppt

第第9章章 事务与锁事务与锁掌握事务的使用方法掌握事务的使用方法了解锁的相关问题，如并发性、锁定级别、了解锁的相关问题，如并发性、锁定级别、死锁等死锁等 事务事务 事事务务的的基基本本概概念念:事事务务是是单单个个的的工工作作单单元元。如如果果某某一一事事务务成成功功，则则在在该该事事务务中中进进行行的的所所有有数数据据更更改改均均会会提提交交，成成为为数数据据库库中中的的永永久久组组成成部部分分。如如果果事事务务遇遇到到错错误误且且必必须取消或回滚，则所有数据更改均被清除。须取消或回滚，则所有数据更改均被清除。事事务务是是作作为为单单个个逻逻辑辑工工作作单单元元执执行行的的一一系系列列操操作作。一一个个逻逻辑辑工工作作单单元元必必须须有有四四个个属属性性，称称为为 ACIDACID（原原子子性性、一一致致性性、隔隔离离性性和和持持久久性性）属属性性，只只有有这这样样才才能能成成为为一一个事务个事务.事务的事务的ACIDACID属性属性 原子性原子性:事务必须是原子工作单元；对于其数据修改，要么全都事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行执行，要么全都不执行一致性一致性:事务在完成时，必须使所有的数据都保持一致状态。在相事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。据的完整性。事务结束时，所有的内部数据结构都必须是正确的。隔离性隔离性:由并发事务所作的修改必须与任何其它并发事务所作的由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，务修改它之前的状态，要么是另一事务修改它之后的状态，事务事务不会查看中间状态的数据不会查看中间状态的数据。这称为可串行性，因为它能够重新装。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。始事务执行的状态相同。持久性持久性:事务完成之后，它对于事务完成之后，它对于系统的影响是永久性的系统的影响是永久性的。该修改。该修改即使出现系统故障也将一直保持。即使出现系统故障也将一直保持。事务的事务的ACIDACID属性属性 SQL Server SQL Server 提供：提供：锁定设备，使事务相互隔离。锁定设备，使事务相互隔离。记录设备，保证事务的持久性。即使服务器硬件、操记录设备，保证事务的持久性。即使服务器硬件、操作系统或作系统或 SQL Server SQL Server 自身出现故障，自身出现故障，SQL Server SQL Server 也也可以在重新启动时使用事务日志，将所有未完成的事可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的位置。务自动地回滚到系统出现故障的位置。事务管理特性，强制保持事务的原子性和一致性。事事务管理特性，强制保持事务的原子性和一致性。事务启动之后，就必须成功完成，否则务启动之后，就必须成功完成，否则 SQL Server SQL Server 将将撤消该事务启动之后对数据所作的所有修改。撤消该事务启动之后对数据所作的所有修改。事务运行模式事务运行模式三种事务运行模式三种事务运行模式:自自动动提交事提交事务务:每条每条单单独的独的语语句都是一个事句都是一个事务务 显显式事式事务务:每个事每个事务务均以均以 BEGIN TRANSACTION BEGIN TRANSACTION 语语句句显显式式开始，以开始，以 COMMIT COMMIT 或或 ROLLBACK ROLLBACK 语语句句显显式式结结束。束。隐隐性事性事务务:在前一个事在前一个事务务完成完成时时新事新事务隐务隐式启式启动动，但每，但每个事个事务务仍以仍以 COMMIT COMMIT 或或 ROLLBACK ROLLBACK 语语句句显显式完成。式完成。显式事务操作显式事务操作 事务组织结构的一般形式如下：事务组织结构的一般形式如下：a a 定义一个事务的开始定义一个事务的开始 BEGIN TRANSACTION BEGIN TRANSACTION b b 提交一个事务提交一个事务 COMMIT TRANSACTIONCOMMIT TRANSACTION C C 回滚事务回滚事务 ROLLBACK TRANSACTIONROLLBACK TRANSACTION BEGIN TRANSACTIONBEGIN TRANSACTION 语法语法:BEGIN TRANSACTION BEGIN TRANSACTION transaction_nametransaction_name 功能功能:标记一个显式本地事务的标记一个显式本地事务的起始点起始点说明说明:BEGIN TRANSACTION BEGIN TRANSACTION 代表一点，如果遇上错误，在代表一点，如果遇上错误，在 BEGIN TRANSACTION BEGIN TRANSACTION 之后的所有数据改动都能进行回滚，以将数之后的所有数据改动都能进行回滚，以将数据返回到已知的一致状态。每个事务继续执行直到它无误地完成据返回到已知的一致状态。每个事务继续执行直到它无误地完成并且用并且用 COMMIT TRANSACTION COMMIT TRANSACTION 对数据库作永久的改动，或者遇上对数据库作永久的改动，或者遇上错误并且用错误并且用 ROLLBACK TRANSACTION ROLLBACK TRANSACTION 语句擦除所有改动。语句擦除所有改动。BEGIN BEGIN TRANSACTION TRANSACTION 为使用本语句的连接启动一个本地事务。根据当前为使用本语句的连接启动一个本地事务。根据当前事务隔离级别的设置情况，为支持该连接所发出的事务隔离级别的设置情况，为支持该连接所发出的 Transact-SQL Transact-SQL 语句而获取的语句而获取的许多资源被该事务锁定许多资源被该事务锁定，直到此事务以，直到此事务以 COMMIT COMMIT TRANSACTION TRANSACTION 或或 ROLLBACK TRANSACTION ROLLBACK TRANSACTION 语句完成。语句完成。长时间处于长时间处于等待处理状态的事务会阻止其他用户访问锁定的资源等待处理状态的事务会阻止其他用户访问锁定的资源。COMMIT TRANSACTIONCOMMIT TRANSACTION语法语法:COMMIT TRAN SACTION COMMIT TRAN SACTION transaction_nametransaction_name 功能功能:标志一个成功的隐性事务或用户定义事务的结束标志一个成功的隐性事务或用户定义事务的结束说明说明:不能在发出一个不能在发出一个 COMMIT TRANSACTION COMMIT TRANSACTION 语句之语句之后回滚事务后回滚事务，因为数据修改已经成为数据库的一个永，因为数据修改已经成为数据库的一个永久部分。如果久部分。如果 TRANCOUNT TRANCOUNT 为为 1 1，COMMIT COMMIT TRANSACTION TRANSACTION 使得自从事务开始以来所执行的所有数使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放连接占用的资源，据修改成为数据库的永久部分，释放连接占用的资源，并将并将 TRANCOUNT TRANCOUNT 减少到减少到 0 0。如果。如果 TRANCOUNT TRANCOUNT 大大于于 1 1，则，则 COMMIT TRANSACTION COMMIT TRANSACTION 使使 TRANCOUNT TRANCOUNT 按按 1 1 递减。递减。注注:TRANCOUNT:TRANCOUNT:返回当前连接的活动事务数返回当前连接的活动事务数COMMIT TRANSACTIONCOMMIT TRANSACTION 当在当在嵌套事务嵌套事务中使用时，内部事务的提交并不释放资中使用时，内部事务的提交并不释放资源或使其修改成为永久修改。只有在提交了源或使其修改成为永久修改。只有在提交了外部事务外部事务时，数据修改才具有永久性，而且资源才会被释放。时，数据修改才具有永久性，而且资源才会被释放。当当 TRANCOUNT TRANCOUNT 大于大于 1 1 时，每发出一个时，每发出一个 COMMIT COMMIT TRANSACTION TRANSACTION 命令就会使命令就会使 TRANCOUNT TRANCOUNT 按按 1 1 递减。递减。当当 TRANCOUNT TRANCOUNT 最终减少到最终减少到 0 0 时，将提交整个外部时，将提交整个外部事务。因为事务。因为 transaction_nametransaction_name 被被 SQL Server SQL Server 忽略，忽略，所以当存在仅将所以当存在仅将 TRANCOUNT TRANCOUNT 按按 1 1 递减的显著内部递减的显著内部事务时，发出一个引用外部事务名称的事务时，发出一个引用外部事务名称的 COMMIT COMMIT TRANSACTIONTRANSACTION。例例:嵌套事务嵌套事务.sqlsqlROLLBACK TRANSACTIONROLLBACK TRANSACTION 语法语法:ROLLBACK TRAN SACTION ROLLBACK TRAN SACTION transaction_nametransaction_name 功能功能:将显式事务或隐性事务回滚到事务的起点或事务将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点内的某个保存点说明说明:ROLLBACK TRANSACTION ROLLBACK TRANSACTION 清除自事务的起点或到某清除自事务的起点或到某个保存点所做的所有数据修改个保存点所做的所有数据修改.ROLLBACK ROLLBACK 还释放由事务还释放由事务控制的资源控制的资源.不带不带 transaction_nametransaction_name 的的 ROLLBACK ROLLBACK TRANSACTION TRANSACTION 回滚到事务的起点回滚到事务的起点.嵌套事务时，该语句将嵌套事务时，该语句将所有内层事务回滚到最远的所有内层事务回滚到最远的 BEGIN TRANSACTION BEGIN TRANSACTION 语句语句。在这两种情况下，在这两种情况下，ROLLBACK TRANSACTION ROLLBACK TRANSACTION 均将均将 TRANCOUNT TRANCOUNT 系统函数减为系统函数减为 0 0锁锁 并发控制并发控制:当许多人试图同时修改数据库内的数据时，当许多人试图同时修改数据库内的数据时，必须执行控制系统以使某个人所做的修改不会对他人必须执行控制系统以使某个人所做的修改不会对他人产生负面影响。这称为并发控制。产生负面影响。这称为并发控制。并发问题并发问题:如果没有锁定且多个用户同时访问一个数据如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发库，则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：生问题。并发问题包括：1.丢失或覆盖更新。丢失或覆盖更新。2.确认的相关性（确认的相关性（脏读脏读）。）。3.不一致的分析（不一致的分析（非重复读非重复读）。）。4.幻像读。幻像读。并发问题并发问题1.1.丢失更新丢失更新:当两个或多个事务选择同一行，然后基于最初选定的值当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。更新，这将导致数据丢失。2.2.未确认的相关性（脏读）未确认的相关性（脏读）当第二个事务选择其它事务正在更新的行时，会发生未当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。确认并且可能由更新此行的事务所更改。并发问题并发问题3.3.不一致的分析（非重复读）不一致的分析（非重复读）当第二个事务多次访问同一行而且每次读取不同的数据时，会发生当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其不一致的分析问题。不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。然而，在不一致它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，而且，不一致的分析涉及多次（两次或更多）读取同一行不一致的分析涉及多次（两次或更多）读取同一行，而且每，而且每次信息都由其它事务更改；因而该行被非重复读取。次信息都由其它事务更改；因而该行被非重复读取。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。取文档，则可以避免该问题。并发问题并发问题4.幻像读幻像读:当对某行执行插入或删除操作，而该行属于当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。续读显示有一行已不存在于原始读中。封锁机制封锁机制为防止查询上述的数据不一致情况，必须使各事务都为防止查询上述的数据不一致情况，必须使各事务都按照某种次序来进行，这种机制就是按照某种次序来进行，这种机制就是封锁封锁。Microsoft SQL Server 2000 具有具有多粒度锁定多粒度锁定，允许，允许一个事务锁定不同类型的资源一个事务锁定不同类型的资源,SQL Server 自动将资自动将资源锁定在适合任务的级别。参见表源锁定在适合任务的级别。参见表13-1SQL Server 使用不同的使用不同的锁模式锁模式锁定资源，这些锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。确定了并发事务访问资源的方式。锁模式锁模式锁模式锁模式描述描述共享共享(S)用于不更改或不更新数据的操作（只读操作），如用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。语句。更新更新(U)用于可更新的资源中。防止当多个会话在读取、锁定用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。以及随后可能进行的资源更新时发生常见形式的死锁。排它排它(X)用于数据修改操作，例如用于数据修改操作，例如 INSERT、UPDATE 或或 DELETE。确保不会同时对同一资源进行多重更新。确保不会同时对同一资源进行多重更新。共享锁共享锁共享共享(S)锁允许并发事务读取锁允许并发事务读取(SELECT)一个资源。一个资源。资资源上存在共享源上存在共享(S)锁时，任何其它事务都不能修改数锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享据。一旦已经读取数据，便立即释放资源上的共享(S)锁，锁，除非将事务隔离级别设置为可重复读或更高级别，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享或者在事务生存周期内用锁定提示保留共享(S)锁。锁。更新锁更新锁更新更新(U)锁可以防止通常形式的死锁。一般更新模式由一个事务锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享组成，此事务读取记录，获取资源（页或行）的共享(S)锁，然锁，然后修改行，此操作要求锁转换为排它后修改行，此操作要求锁转换为排它(X)锁。如果两个事务获得锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它试将锁转换为排它(X)锁。共享模式到排它锁的转换必须等待一锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它发生锁等待。第二个事务试图获取排它(X)锁以进行更新。由于锁以进行更新。由于两个事务都要转换为排它两个事务都要转换为排它(X)锁，并且锁，并且每个事务都等待另一个事每个事务都等待另一个事务释放共享模式锁，因此发生死锁务释放共享模式锁，因此发生死锁。若要避免这种潜在的死锁问题，请使用更新若要避免这种潜在的死锁问题，请使用更新(U)锁。锁。一次只有一一次只有一个事务可以获得资源的更新个事务可以获得资源的更新(U)锁锁。如果事务修改资源，则更新。如果事务修改资源，则更新(U)锁转换为排它锁转换为排它(X)锁。否则，锁转换为共享锁。锁。否则，锁转换为共享锁。排它锁排它锁排它排它(X)锁可以防止并发事务对资源进行访问。锁可以防止并发事务对资源进行访问。其它其它事务不能读取或修改排它事务不能读取或修改排它(X)锁锁定的数据锁锁定的数据。死锁死锁当某组资源的两个或多个线程之间有循环相关性时，当某组资源的两个或多个线程之间有循环相关性时，将发生死锁将发生死锁.例如，运行事务例如，运行事务 1 的线程的线程 T1 具有具有 Supplier 表上的排表上的排它锁。运行事务它锁。运行事务 2 的线程的线程 T2 具有具有 Part 表上的排它锁，表上的排它锁，并且之后需要并且之后需要 Supplier 表上的锁。事务表上的锁。事务 2 无法获得这无法获得这一锁，因为事务一锁，因为事务 1 已拥有它。事务已拥有它。事务 2 被阻塞，等待事被阻塞，等待事务务 1。然后，事务。然后，事务 1 需要需要 Part 表的锁，但无法获得锁，表的锁，但无法获得锁，因为事务因为事务 2 将它锁定了。事务在提交或回滚之前不能将它锁定了。事务在提交或回滚之前不能释放持有的锁。因为事务需要对方控制的锁才能继续释放持有的锁。因为事务需要对方控制的锁才能继续操作，所以它们不能提交或回滚。操作，所以它们不能提交或回滚。死锁死锁 结束死锁结束死锁在识别死锁后，在识别死锁后，SQL Server 通过自动选择可以打破死锁的线程通过自动选择可以打破死锁的线程（死锁牺牲品）来结束死锁。（死锁牺牲品）来结束死锁。SQL Server 回滚作为死锁牺牲品的回滚作为死锁牺牲品的事务，通知线程的应用程序（通过返回事务，通知线程的应用程序（通过返回 1205 号错误信息），取消号错误信息），取消线程的当前请求，然后允许不间断线程的事务继续进行。线程的当前请求，然后允许不间断线程的事务继续进行。SQL Server 通常选择运行撤消时花费最少的事务的线程作为死锁通常选择运行撤消时花费最少的事务的线程作为死锁牺牲品。另外，用户可以使用牺牲品。另外，用户可以使用 SET 语句将会话的语句将会话的 DEADLOCK_PRIORITY 设置为设置为 LOW。DEADLOCK_PRIORITY 选项控制在死锁情况下如何衡量会话选项控制在死锁情况下如何衡量会话的重要性。的重要性。如果会话的设置为如果会话的设置为 LOW，则当会话陷入死锁情况时则当会话陷入死锁情况时将成为首选牺牲品。将成为首选牺牲品。措施措施 按同一顺序访问对象。按同一顺序访问对象。避免事务中的用户交互。避免事务中的用户交互。保持事务简短并在一个批处理中。保持事务简短并在一个批处理中。使用低隔离级别。使用低隔离级别。使用绑定连接。使用绑定连接。实验实验