T-SQL笔记3：事务、锁定和并发

本章摘要

1：事务

   1.1：显式事务

   1.2：使用DBCC OPENTRAN显示最早的活动事务

2：锁定

   2.1：查看锁的活动

3：并发影响及隔离级别

   3.1：并发影响

   3.2：隔离级别

4：阻塞

   4.1：找到并解决阻塞进程

   4.2：设定阻塞时间

5：死锁

1：事务

     事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。SQL Server 以下列事务模式运行。

     自动提交事务：每条单独的语句都是一个事务。

     显式事务：每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。（是最推荐使用的模式）

     隐式事务：在前一个事务完成时新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。（一般不建议使用）

1.1：显式事务

     显式事务命令如下：

BEGIN TRANSACTION：设置一个显式事务的起点。

ROLLBACK TRANSACTION：恢复由一个事务修改的原始数据，使数据回到事务开始时的状态，并释放事务占据的资源。

COMMIT TRANSACTION：如果没有遇到错误则结束事务并永久实现修改，并释放事务占据的资源。

BEGIN DISTRIBUTED TRANSACTION：分布式事务起点。

SAVE TRANSACTION：在事务内部设置一个保存点，允许我们定义一个事务取消后能返回的位置。

@@TRANCOUNT：返回连接的活动事务数量。BEGIN TRANSACTION将@@TRANCOUNT加1，ROLLBACK TRANSACTION和COMMIT TRANSACTION将@@TRANCOUNT减1。

1.2：使用DBCC OPENTRAN显示最早的活动事务

     如果在指定数据库内存在最早的活动事务和最早的分布式和非分布式复制事务，则显示与之有关的信息。

DBCC OPENTRAN [         ( [ database_name | database_id | 0 ] ) ]     { [ WITH TABLERESULTS ]       [ , [ NO_INFOMSGS ] ]     } ]

2：锁定

     当多个用户或应用程序同时访问同一数据时，锁定可防止这些用户或应用程序同时对数据进行更改。

     在较小粒度（如行级）上锁定会提高并发性，但是如果锁定许多行，则必须持有更多的锁。在较大粒度（如表级）上锁定会降低并发性，因为锁定整个表会限制其他事务对该表任何部分的访问。但是，在表级锁定中，持有的锁较少。

     默认情况下，SQL Server Compact Edition 对数据页使用行级锁定，对索引页使用页级锁定。

     下表显示了 SQL Server Compact Edition 可以锁定的资源：

RID：行标识符。用于锁定表内的单个行。

PAG：数据页或索引页。

TAB：整个表，包括所有数据和索引

MD：表的元数据。用于保护表架构

DB：数据库

     下表显示了数据库引擎使用的资源锁模式：

共享 (S)：用于不更改或不更新数据的读取操作，如 SELECT 语句。

更新 (U)：用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

排他 (X)：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。

意向：用于建立锁的层次结构。意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。

架构：在执行依赖于表架构的操作时使用。架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

大容量更新 (BU)：在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。

键范围：当使用可序列化事务隔离级别时保护查询读取的行的范围。确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。

2.1：查看锁的活动

      示例：

新建一个查询编辑器，键入并运行：

BEGIN TRAN
SELECT ProductID, DocumentID, ModifiedDate
FROM Production.ProductDocument
WITH(TABLOCKX)

打开第二个查询编辑器，键入并运行：

SELECT request_session_id sessionid,
    resource_type type,
    resource_database_id dbid,
    OBJECT_NAME(resource_associated_entity_id) objectname,
    request_mode rmode,
    request_status rstatus
FROM sys.dm_tran_locks

得到结果：

      在这个示例中，首先启动一个新的事务，并使用TABLOCKX对表放置了排它锁。第二个查询编辑器中运行的就是查看该数据库中活动锁的列表。第三行就是在ProductDocument上的排它锁。

3：并发影响及隔离级别

3.1：并发影响

     并发影响所产生的影响主要有以下四类：

丢失更新

     当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将覆盖由其他事务所做的更新，这将导致数据丢失。

     例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

未提交的依赖关系（脏读）

     当第二个事务选择其他事务正在更新的行时，会发生未提交的依赖关系问题。第二个事务正在读取的数据还没有提交并且可能由更新此行的事务所更改。

     例如，一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该副本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应视为从未存在过。如果在第一个编辑人员保存最终更改并提交事务之前，任何人都不能读取更改的文档，则可以避免此问题。

不一致的分析（不可重复读）

     当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未提交的依赖关系类似，因为其他事务也是正在更改第二个事务正在读取的数据。但是，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。此外，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都被其他事务更改，因此我们称之为“不可重复读”。

     例如，编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果在编辑人员完成最后一次读取文档之前，作者不能更改文档，则可以避免此问题。

幻读

     当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻读问题。由于其他事务的删除操作，事务第一次读取的行的范围显示有一行不再存在于第二次或后续读取内容中。同样，由于其他事务的插入操作，事务第二次或后续读取的内容显示有一行并不存在于原始读取内容中。

     例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主副本时，发现作者已将未编辑的新材料添加到该文档中。与不可重复读的情况相似，如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免此问题。

3.2：隔离级别

     ISO 标准定义了下列隔离级别，SQL Server 数据库引擎支持所有这些隔离级别：

• 未提交读（READ UNCOMMITTED）（隔离事务的最低级别，只能保证不读取物理上损坏的数据）
• 已提交读（READ COMMITTED）（数据库引擎的默认级别）
• 可重复读（REPEATABLE READ）
• 可序列化（SERIALIZABLE）（隔离事务的最高级别，事务之间完全隔离）
• 快照（SNAPSHOT）

     下表显示了不同隔离级别导致的并发副作用。

隔离级别      脏读 不可重复读 幻读

未提交读       是   是            是

已提交读       否   是            是

可重复读       否   否            是

快照              否   否            否

可序列化       否   否            否

     使用SET TRANSACTION ISOLATION LEVEL { | }可以设置隔离级别。

4：阻塞

    首先罗列一些发生阻塞的原因：

    a、如果没有适当索引，阻塞问题会加剧。在一个没有索引的表上的过量的行锁会导致SQL SERVER得到一个表锁，从而阻塞其它事务。

    b、应用程序打开一个事务后，保持事务打开，然后要求用户进行反馈或者交互。通常是让最终用户在GUI上输入数据而保持事务打开的时候发生。保持打开的话，任何事务引用的资源会被锁占据。

    c、事务BEGIN后查询的数据可能在事务开始之前被引用。

    d、查询不适当地使用锁提示。例如，应用程序仅使用很少的行，但却使用了一个表锁提示。

    e、应用程序使用长时间运行的事务，在一个事务中更新了很多行或很多表（把大量更新的事务变成较小更新的事务能帮助改善并发性）。

4.1：找到并解决阻塞进程

    以一个示例来阐述此问题：

    在第一个查询编辑器中运行下列代码来创建一个阻塞的进程：

BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity=406
WHERE ProductID=1 AND LocationID=1

    在第二个查询编辑器中运行下列代码来创建一个阻塞的进程：

BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity=400
WHERE ProductID=1 AND LocationID=1

    发现第二个编辑器中的命令永远不成功。这是因为第一个连接的事务永不提交，第二个连接只能无限期排队。

    在第三个查询编辑器中运行：

SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL

    这个时候，可以查到被阻滞的线程：

    调用命令：

    KILL 54

    完成杀掉阻塞进行，可以看到第二个编辑器中的命令已经执行成功了。

4.2：设定阻塞时间

    可以通过使用SET LOCK_TIMEOUT timearg来设定阻塞时间。

5：死锁

    首先模拟一个死锁。

    在第一个窗口运行：

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

WHILE 1=1
BEGIN
BEGIN TRAN
    UPDATE Purchasing.Vendor
    SET CreditRating=1
    WHERE VendorID=2
    UPDATE Purchasing.Vendor
    SET CreditRating=2
    WHERE VendorID=1
COMMIT TRAN
END

    在第二个窗口运行：

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

WHILE 1=1
BEGIN
BEGIN TRAN
    UPDATE Purchasing.Vendor
    SET CreditRating=2
    WHERE VendorID=1
    UPDATE Purchasing.Vendor
    SET CreditRating=1
    WHERE VendorID=2
COMMIT TRAN
END

    运行几秒钟后，检查两个窗口，其中一个窗口会出现：

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    在第三个窗口中运行：

DBCC TRACEON(1222, -1)
GO
DBCC TRACESTATUS

    使用1222标志位，将会在日志中记录被锁资源和类型的信息。这样我们就能分析出错的地方，找到解决方案。

本文基于 Creative Commons Attribution 2.5 China Mainland License发布，欢迎转载，演绎或用于商业目的，但是必须保留本文的署名 http://www.cnblogs.com/luminji（包含链接）。如您有任何疑问或者授权方面的协商，请给我留言。