在SQL Server 2005中解决死锁

数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。
将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。
use Northwind
begin tran
insert into Orders(CustomerId) values('ALFKI')
waitfor delay '00:00:05'
select * from Orders where CustomerId = 'ALFKI'
commit
print 'end tran'
SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print 'end tran'语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。
现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径：
下面利用的try ... catch来解决死锁。
SET XACT_ABORT ON
declare @r int
set @r = 1
while @r <= 3
begin
begin tran

begin try
insert into Orders(CustomerId) values('ALFKI')
waitfor delay '00:00:05'
select * from Orders where CustomerId = 'ALFKI'

commit
break
end try

begin catch
rollback
waitfor delay '00:00:03'
set @r = @r 1
continue
end catch
end
解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。
但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样:
declare @r int
set @r = 1
while @r <= 3
begin
begin tran

begin try
insert into Orders(CustomerId) values('ALFKI')
waitfor delay '00:00:05'
select * from Orders where CustomerId = 'ALFKI'

11.1. 行列转换--普通

假设有张学生成绩表(CJ)如下

Name Subject Result

张三 语文 80

张三 数学 90

张三 物理 85

李四 语文 85

李四 数学 92

李四 物理 82

想变成

姓名 语文 数学 物理

张三 80 90 85

李四 85 92 82

declare @sql varchar(4000)

set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'

from (select distinct Subject from CJ) as a

select @sql = @sql+' from test group by name'

exec(@sql)

11.2. 行列转换--合并

有表A,

id pid

1 1

1 2

1 3

2 1

2 2

3 1

如何化成表B:

id pid

1 1,2,3

2 1,2

3 1

创建一个合并的函数

create function fmerg(@id int)

returns varchar(8000)

as

begin

declare @str varchar(8000)

set @str=''
创建通知处理程序
　　现在，您可以创建一个通知处理程序，它将借助于上面介绍的 sendNotification
过程向客户端发送更改通知。来看一看“清单 2”中的 PL/SQL 过程 orders_nf_callback。
　　清单 2.
处理对 OE.ORDERS 表所做更改的通知的通知处理程序

CREATE OR REPLACE PROCEDURE orders_nf_callback (ntfnds IN SYS.CHNF$_DESC) IS 
tblname VARCHAR2(60); 
numtables NUMBER; 
event_type NUMBER; 
row_id VARCHAR2(20); 
numrows NUMBER; 
ord_id VARCHAR2(12); 
url VARCHAR2(256) := 'http://webserverhost/phpcache/dropResults.php?order_no='; 
BEGIN 
event_type := ntfnds.event_type; 
numtables := ntfnds.numtables; 
IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN 
FOR i IN 1..numtables LOOP 
tblname := ntfnds.table_desc_array(i).table_name; 
IF (bitand(ntfnds.table_desc_array(i).opflags,  
DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN 
numrows := ntfnds.table_desc_array(i).numrows; 
ELSE 
numrows :=0; 
END IF; 
IF (tblname = 'OE.ORDERS') THEN 
FOR j IN 1..numrows LOOP 
row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id; 
SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id; 
sendNotification(url, tblname, ord_id);  
END LOOP; 
END IF; 
END LOOP; 
END IF; 
COMMIT; 
END; 
/ 

　　如“清单 2”所示，此通知处理程序将 SYS.CHNF$_DESC
对象用作参数，然后使用它的属性获取该更改的详细信息。在该示例中，此通知处理程序将只处理数据库为响应对注册对象进行的 DML 或 DDL
更改（也就是说，仅当通知类型为 EVENT_OBJCHANGE
时）而发布的通知，并忽略有关其他数据库事件（如实例启动或实例关闭）的通知。从以上版本开始，处理程序可以处理针对 OE.ORDERS
表中每个受影响的行发出的更改通知。在本文后面的“将表添加到现有注册”部分中，您将向处理程序中添加几行代码，以便它可以处理针对 OE.ORDER_ITEMS
表中被修改的行发出的通知。
将表添加到现有注册
　　前一部分介绍了如何使用更改通知服务使数据库在注册对象（在以上示例中为 ORDERS
表）发生更改时发出通知。但从性能角度而言，客户端应用程序可能更希望缓存 ORDER_ITEMS 表而非 ORDERS
表本身的查询结果集，这是因为它在每次访问订单时，不得不从 ORDERS 表中只检索一行，但同时必须从 ORDER_ITEMS
表中检索多个行。在实际情况中，订单可能包含数十个甚至数百个订单项。
　　由于您已经对 ORDERS 表注册了查询，因此不必再创建一个注册来注册对
ORDER_ITEMS 表的查询了。相反，您可以使用现有注册。为此，您首先需要检索现有注册的 ID。可以执行以下查询来完成此工作：

SELECT regid, table_name FROM user_change_notification_regs;

　　结果可能如下所示：

REGID TABLE_NAME 
----- -------------- 
241 OE.ORDERS 

　　获取注册 ID 后，可以使用 DBMS_CHANGE_NOTIFICATION.ENABLE_REG
函数将一个新对象添加到该注册，如下所示：

DECLARE 
ord_id NUMBER; 
BEGIN 
DBMS_CHANGE_NOTIFICATION.ENABLE_REG(241); 
SELECT order_id INTO ord_id FROM order_items WHERE ROWNUM ＜ 2; 
DBMS_CHANGE_NOTIFICATION.REG_END; 
END; 
/ 

　　完成了！从现在开始，数据库将生成一个通知来响应对 ORDERS 和 ORDER_ITEMS 所做的任何更改，并调用
orders_nf_callback 过程来处理通知。因此，下一步就是编辑 orders_nf_callback，以便它可以处理因对 ORDER_ITEMS
表执行 DML 操作而生成的通知。但在重新创建 orders_nf_callback 过程之前，您需要创建以下将在更新过程中引用的表类型：

CREATE TYPE rdesc_tab AS TABLE OF SYS.CHNF$_RDESC;

　　然后，返回清单
2，在以下代码行之后：

IF (tblname = 'OE.ORDERS') THEN 
FOR j IN 1..numrows LOOP 
row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id; 
SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id; 
sendNotification(url, tblname, ord_id);  
END LOOP; 
END IF; 

　　插入以下代码：

IF (tblname = 'OE.ORDER_ITEMS') THEN 
     
　构建客户端 
　　现在，您已经针对 ORDERS 和 ORDER_ITEMS 
表创建了注册，下面我们将了解一下访问这些表中存储的订单及其订单项的客户端应用程序如何使用更改通知。为此，您可以构建一个 PHP 
应用程序，它将缓存针对以上表的查询结果，并采取相应的操作来响应有关对这些表所做更改的通知（从数据库服务器中收到这些通知）。一个简单的方法是使用 
PEAR::Cache_Lite 程序包，它为您提供了一个可靠的机制来使缓存数据保持最新状态。尤其是，您可以使用 Cache_Lite_Function 
类（PEAR::Cache_Lite 程序包的一部分），通过该类您可以缓存函数调用。
　　例如，您可以创建一个函数来执行下列任务：建立数据库连接、针对该数据库执行 select 
语句、获取检索结果并最终以数组形式返回结果。然后，您可以通过 Cache_Lite_Function 实例的 call 
方法缓存由该函数返回的结果数组，以便可以从本地缓存而不是从后端数据库读取这些数组，这样可以显著提高应用程序的性能。然后，在收到缓存数据更改的通知时，您将使用 
Cache_Lite_Function 实例的 drop 方法删除缓存中的过期数据。
　　回过头来看看本文的示例，您可能要创建两个函数，用于应用程序与数据库交互：第一个函数将查询 ORDERS 表并返回具有指定 ID 
的订单，而另一个函数将查询 ORDER_ITEMS 表并返回该订单的订单项。“清单 4”显示了包含 getOrderFields 函数（该函数接受订单 ID 
并返回一个包含所检索到订单的某些字段的关联数组）的 getOrderFields.php 脚本。
　　清单 4. 
获取指定订单的字段 
＜?php 
//File:getOrderFields.php 
require_once 'connect.php'; 
function getOrderFields($order_no) { 
if (!$rsConnection = GetConnection()){ 
return false; 
   } 
$strSQL = "SELECT TO_CHAR(ORDER_DATE) ORDER_DATE, CUSTOMER_ID,  
ORDER_TOTAL FROM ORDERS WHERE order_id =:order_no"; 
$rsStatement = oci_parse($rsConnection,$strSQL); 
oci_bind_by_name($rsStatement, ":order_no", $order_no, 12); 
if (!oci_execute($rsStatement)) { 
$err = oci_error(); 
print $err['message']; 
trigger_error('Query failed:' . $err['message']);