从零开始学SQL_Server——第9章__事务和锁

1、第第9 9章章 事务和锁事务和锁SQL Server数据库有四个显著特征，即安全性、完整数据库有四个显著特征，即安全性、完整性、并发性和监测性。数据的完整性是通过一系列逻辑来保性、并发性和监测性。数据的完整性是通过一系列逻辑来保障的，这些逻辑分为三个方面，即实体完整性、域完整性和障的，这些逻辑分为三个方面，即实体完整性、域完整性和参考完整性。为了保证数据库数据的完整性，参考完整性。为了保证数据库数据的完整性，SQL Server提提供了多种保证机制，如约束、触发器、事务和锁管理等。事供了多种保证机制，如约束、触发器、事务和锁管理等。事务管理主要是为了保证一批相关数据库数据的操作能全部被务管理主

2、要是为了保证一批相关数据库数据的操作能全部被完成，从而保证数据的完整性。锁机制主要是处理多个活动完成，从而保证数据的完整性。锁机制主要是处理多个活动事务的并发。锁是一个非常重要的概念，它是用来控制多个事务的并发。锁是一个非常重要的概念，它是用来控制多个用户对同一数据进行的操作，通过使用锁机制，解决了数据用户对同一数据进行的操作，通过使用锁机制，解决了数据库的并发性问题。本章内容主要包括：库的并发性问题。本章内容主要包括：事务管理的常用语句事务管理的常用语句事物管理的类型事物管理的类型锁的类型锁的类型9.1 9.1 事务管理事务管理事务是事务是SQL Server的基本工作单元，事务内的语句被作

3、为的基本工作单元，事务内的语句被作为一个整体执行，从而保证数据的可恢复性和一致性。一个整体执行，从而保证数据的可恢复性和一致性。SQL Server的事务由两种类型，分别是隐式事务和显式事务。隐式的事务由两种类型，分别是隐式事务和显式事务。隐式事务是根据事务是根据T-SQL语句自身的特点形成的事务，比如插入（语句自身的特点形成的事务，比如插入（INSERT）、删除（）、删除（DELETE）和更新（）和更新（UPDATE）等，这些）等，这些语句的执行默认为一个完整的事务，显式事务是用户使用语句语句的执行默认为一个完整的事务，显式事务是用户使用语句定义的。定义的。9.1.1 9.1.1 事务的原理

4、事务的原理使用使用UPDATE来对数据库进行更新时，一次只能操作一来对数据库进行更新时，一次只能操作一个表。但事实当应用要求对多个表的数据进行更新操作时，个表。但事实当应用要求对多个表的数据进行更新操作时，就又可能带来数据库操作的不一致问题。为此要将整个过程就又可能带来数据库操作的不一致问题。为此要将整个过程设置为要么完成操作，要么一步也不完成，这就是数据库中设置为要么完成操作，要么一步也不完成，这就是数据库中的事物。的事物。1事务的内涵事务的内涵2事务的属性事务的属性3事务日志事务日志4建立事务应遵循的原则建立事务应遵循的原则9.1.2 9.1.2 事务管理的常用语句事务管理的常用语句SQL

5、 Server事务管理的语句包括如下几条：事务管理的语句包括如下几条：BEGIN TRANSACTIONCOMMIT TRANSACTIONROLLBACK TRANSACTIONSAVE TRANSACTIONBEGIN TRANCATION用来建立一个事务，用来建立一个事务，COMMIT TRANSACTION表示提交事务，表示提交事务，ROLLBACK TRANSACTION用来执行事务失败时的回滚操作，用来执行事务失败时的回滚操作，SAVE TRANSACTION表示保存事务。其中，表示保存事务。其中，BEGIN TRANSACTION和和COMMIT TRANSACTION应该同时用

6、应该同时用用，用来标识事务的开始和结束。用，用来标识事务的开始和结束。9.1.3 9.1.3 事务恢复和检查点机制事务恢复和检查点机制虽然现在计算机的软硬件系统和网络环境的可靠性已经虽然现在计算机的软硬件系统和网络环境的可靠性已经有了很大的提高，当仍然做不到万无一失，各种人为的操作有了很大的提高，当仍然做不到万无一失，各种人为的操作失误、病毒的破坏等都对数据库系统造成了很大的威胁。失误、病毒的破坏等都对数据库系统造成了很大的威胁。SQL Server实例在事务日志总存储足够的信息来恢复和撤销构成事实例在事务日志总存储足够的信息来恢复和撤销构成事务的数据修改。务的数据修改。为了减少频繁对磁盘读写

7、操作，提高系统运行的速度，为了减少频繁对磁盘读写操作，提高系统运行的速度，SQL Server在内存中建立了一个缓冲区，来存放被操作的数在内存中建立了一个缓冲区，来存放被操作的数据，等出现检查点的时候才将缓冲区中的数据写回磁盘。在据，等出现检查点的时候才将缓冲区中的数据写回磁盘。在检索数据时，先将数据读到缓冲区；修改数据时也是先将数检索数据时，先将数据读到缓冲区；修改数据时也是先将数据读到缓冲区，再对缓冲区内的数据进行修改。缓冲区中未据读到缓冲区，再对缓冲区内的数据进行修改。缓冲区中未写回磁盘的数据页面，叫做脏页。写回磁盘的数据页面，叫做脏页。9.1.4 9.1.4 事务的类型事务的类型SQL

8、 Server事务的类型包括以下几种：事务的类型包括以下几种：自动处理事务：自动处理事务：隐式事务：隐式事务：用户定义事务：用户定义事务：分布式事务：分布式事务：1显式事务显式事务2隐式事务隐式事务3自动事务自动事务9.1.4 9.1.4 事务的类型事务的类型9.1.4 9.1.4 事务的类型事务的类型9.1.5 9.1.5 事务的应用案例事务的应用案例事物和后面要提到的锁在数据更新时要保证数据的一事物和后面要提到的锁在数据更新时要保证数据的一致性，在致性，在SQL Server2005中是一个难点，为了使读者更加清中是一个难点，为了使读者更加清楚的认识事物的应用，本小节中，将使用一个综合性的

9、实例楚的认识事物的应用，本小节中，将使用一个综合性的实例来展示事务的强大处理功能，使读者在具体应用中对内容得来展示事务的强大处理功能，使读者在具体应用中对内容得以掌握。以掌握。1选择选择4门课程（最高限选门课程（最高限选5门）门）2验证所选课程验证所选课程3选择选择6门课程（最高限选门课程（最高限选5门）门）4验证所选课程验证所选课程9.1.5 9.1.5 事务的应用案例事务的应用案例9.1.5 9.1.5 事务的应用案例事务的应用案例9.2 9.2 锁锁事务是事务是SQL Server的基本工作单元，事务内的语句被作的基本工作单元，事务内的语句被作为一个整体执行，从而保证数据的可恢复性和一致

10、性。为一个整体执行，从而保证数据的可恢复性和一致性。SQL Server的事务由两种类型，分别是隐式事务和显式事务。隐式的事务由两种类型，分别是隐式事务和显式事务。隐式事务是根据事务是根据T-SQL语句自身的特点形成的事务，比如插入（语句自身的特点形成的事务，比如插入（INSERT）、删除（）、删除（DELETE）和更新（）和更新（UPDATE）等，这些）等，这些语句的执行默认为一个完整的事务，显式事务是用户使用语句语句的执行默认为一个完整的事务，显式事务是用户使用语句定义的。定义的。 9.2.1 9.2.1 锁的内涵与作用锁的内涵与作用数据库开发过程中，对数据的并发操作是经常发生的，数据库开

11、发过程中，对数据的并发操作是经常发生的，而对数据的并发操作会带来如下数据不一致的问题：而对数据的并发操作会带来如下数据不一致的问题：1丢失更新丢失更新2脏读脏读3不可重复读不可重复读4幻读幻读 9.2.2 9.2.2 可锁定资源与锁的类型可锁定资源与锁的类型SQL Server 2005具有多粒度锁定，允许一个事务锁定具有多粒度锁定，允许一个事务锁定不同类型的资源。为了使锁定的成本减至最少，不同类型的资源。为了使锁定的成本减至最少，SQL Server自动将资源锁定在适合任务的级别。锁定在较小的粒度（例自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为

12、如果锁定了许如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。锁定在较大的粒度（例如表）多行，则需要控制更多的锁。锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其他事务就并发而言是相当昂贵的，因为锁定整个表限制了其他事务对表中任意部分进行访问，但要求的开销较低，因为需要维对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。下面将分别介绍可锁定资源的类型和锁的类型护的锁较少。下面将分别介绍可锁定资源的类型和锁的类型和相容性。和相容性。1可锁定资源的类型可锁定资源的类型2锁的类型与相容性锁的类型与相容性3查看系统锁查看系统锁9.2.3

13、 9.2.3 死锁死锁所谓死锁所谓死锁: 是指两个或两个以上的进程在执行过程中，是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。生了死锁，这些永远在互相等待的进程称为死锁进程。1死锁的成因死锁的成因2死锁的排查死锁的排查3减少死锁的策略减少死锁的策略9.2.4 9.2.4 锁的应用案例锁的应用案例锁要考虑的情况比较多，为了使前面讲到的事物和锁锁要考虑的

14、情况比较多，为了使前面讲到的事物和锁的应用彻底理解，本小节中，将使用几个实例来对锁可能出的应用彻底理解，本小节中，将使用几个实例来对锁可能出现的几种情况作具体分析，使读者在了解锁的同时更加深刻现的几种情况作具体分析，使读者在了解锁的同时更加深刻的理解事物的使用，从而达到融会贯通的作用。的理解事物的使用，从而达到融会贯通的作用。1锁定表的某一行锁定表的某一行2锁定数据库的单个表锁定数据库的单个表3排它锁排它锁4共享锁共享锁5死锁死锁9.2.4 9.2.4 锁的应用案例锁的应用案例9.2.4 9.2.4 锁的应用案例锁的应用案例9.2.4 9.2.4 锁的应用案例锁的应用案例9.3 9.3 小结小结SQL Server提供了多种保证机制以确保数据库数据的提供了多种保证机制以确保数据库数据的完整性，比如约束、触发器、事务和锁管理等。事务和锁是完整性，比如约束、触发器、事务和锁管理等。事务和锁是两个紧密联系的概念。事务包括一系列的操作，这些操作要两个紧密联系的概念。事务包括一系列的操作，这些操作要么全部成功，要么全部失败。么全部成功，要么全部失败。SQL Server通过支持事务机制通过支持事务机制管理多个事务，保证事务的一致性。事务使用锁，防止其他管理多个事务，保证事务的一致性。事务使用锁，防止其他