SQL2008中SQL应用之－阻塞(Blocking)应用分析

SQL Server在处理并发请求时，可能会出现阻塞（Blocking）的情况。阻塞是指，一个事务（Transaction）正在访问某个资源（如表、行、页），而另一个事务需要访问同一资源，但此时资源已被锁定，因此需要等待前一个事务完成后才能访问。在这个过程中，后续的事务被堵塞，无法执行。如果阻塞的时间过长，可能会影响系统的响应性能甚至导致死锁。因此，对阻塞的分析和解决是非常重要的。

以下是一个完整的攻略：

概述

1. 了解阻塞的定义和产生原因
2. 了解诊断和分析阻塞的工具和技术
3. 实际操作和示例演示

阻塞的定义

阻塞是指一个事务正在访问某个资源，而另一个事务需要访问同一资源，但此时资源已被锁定，因此需要等待前一个事务完成后才能访问。

阻塞产生的原因

阻塞的主要原因是由不同的事务所持有的锁所导致的。在不同的事务中，会有多个锁，如共享锁、排他锁等，这些锁的优先级不同，也会引起阻塞。

诊断和分析阻塞

1. 查看系统中正在运行的进程和会话。可以使用以下语句：

sql
SELECT spid, program_name, status, loginame, hostname, db_name(dbid) as database_name
FROM master..sysprocesses
WHERE dbid > 0
AND spid <> @@spid
AND blocked <> 0;

这些字段可以帮助我们了解到正在运行的进程和会话的详细信息，以及哪些被阻塞等信息。

1. 查看当前的锁信息。可以使用以下语句：

sql
SELECT * FROM sys.dm_tran_locks;

这个语句可以帮助我们了解到当前所有的锁的详细信息，包括锁类型、锁模式、对象名称等信息。

1. 查看阻塞链（Blocking Chain）。可以使用以下语句：

sql
SELECT
spid,
blocked,
waittype,
waittime,
lastwaittype,
cpu,
physical_io,
memusage,
loginame,
program_name
FROM
master..sysprocesses
WHERE
blocked > 0

这个语句可以帮助我们了解到阻塞的进程和会话，以及哪些进程和会话被阻塞。

示例演示

以下是两个关于阻塞的示例：

1. 示例1

假设我们有以下两个事务：

```sql
-- 事务1
BEGIN TRAN
UPDATE dbo.Customer SET FirstName = 'John' WHERE CustomerID = 1
WAITFOR DELAY '00:00:10'
COMMIT

-- 事务2
UPDATE dbo.Customer SET FirstName = 'Tom' WHERE CustomerID = 1
```

这个示例中，事务1会持有一个排他锁，并阻塞事务2的更新操作。我们可以使用上述的诊断和分析方法，找到事务1阻塞事务2的原因。

1. 示例2

假设我们有以下两个事务：

```sql
-- 事务1
BEGIN TRAN
UPDATE dbo.Customer SET FirstName = 'John' WHERE CustomerID = 1
WAITFOR DELAY '00:00:10'
COMMIT

-- 事务2
UPDATE dbo.Customer SET LastName = 'Smith' WHERE CustomerID = 1
```

这个示例中，事务1会持有一个排他锁，但它并不会阻塞事务2的更新操作，因为事务2更新的是不同的列。我们可以通过查询系统视图和锁信息，确定哪些锁被持有，以及它们的模式和优先级。

以上是一个完整的关于阻塞分析的攻略，我们可以通过系统视图和锁信息，找到阻塞产生的原因，并进行相应的解决方案。