SQL Server Broker事务在有毒消息异常时完成 [英] SQL Server Broker Transaction Completed on Poison Message Exception
问题描述
我正在C#应用程序中的2008 R2中使用SQL Server Broker，并试图处理SQL Server检测到有害消息并禁用目标队列的情况.
在这种情况下，当我尝试接收消息时，将引发SqlException.那时，我正在使用的SqlTransaction似乎不再是可提交的.
我将使用此教程与我的C#代码一起演示.
首先使用教程中的T-SQL代码创建必要的服务代理对象，并发送一条消息，使其位于目标队列中.
CREATE MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
[//AWDB/1DBSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [//AWDB/1DBSample/SampleContract]
([//AWDB/1DBSample/RequestMessage]
SENT BY INITIATOR,
[//AWDB/1DBSample/ReplyMessage]
SENT BY TARGET
);
GO
CREATE QUEUE TargetQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/TargetService]
ON QUEUE TargetQueue1DB
([//AWDB/1DBSample/SampleContract]);
GO
CREATE QUEUE InitiatorQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/InitiatorService]
ON QUEUE InitiatorQueue1DB;
GO
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE
[//AWDB/1DBSample/InitiatorService]
TO SERVICE
N'//AWDB/1DBSample/TargetService'
ON CONTRACT
[//AWDB/1DBSample/SampleContract]
WITH
ENCRYPTION = OFF;
SELECT @RequestMsg =
N'<RequestMsg>Message for Target service.</RequestMsg>';
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO
接下来运行此C#代码，它是一个控制台应用程序.
using System.Data.SqlClient;
namespace ServerConsoleApplication
{
class Program
{
static SqlConnection conn = null;
static void Main(string[] args)
{
conn = new SqlConnection("connection string");
conn.Open();
Receive(); // 1
Receive(); // 2
Receive(); // 3
Receive(); // 4
Receive(); // 5
Receive(); // 6 - Poison Message exception invoked
conn.Close();
}
static void Receive()
{
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
using (SqlCommand waitCommand = conn.CreateCommand())
{
waitCommand.Transaction = tran;
waitCommand.CommandText = string.Format("WAITFOR (RECEIVE TOP (1) conversation_handle, convert(xml,message_body) FROM TargetQueue1DB), TIMEOUT 1000");
using (SqlDataReader reader = waitCommand.ExecuteReader())
{
}
}
// Rollback on purpose to cause the poison message
tran.Rollback();
}
catch (SqlException ex)
{
if (ex.Number == 9617)
{
// Re-Enable the queue
using (SqlCommand enableCmd = conn.CreateCommand())
{
enableCmd.Transaction = tran;
enableCmd.CommandText = string.Format(@"ALTER QUEUE TargetQueue1DB WITH STATUS = ON");
enableCmd.ExecuteNonQuery();
}
System.Data.SqlTypes.SqlGuid handle = System.Data.SqlTypes.SqlGuid.Null;
// Pull the poison message off the queue
using (SqlCommand waitCommand = conn.CreateCommand())
{
waitCommand.Transaction = tran;
waitCommand.CommandText = string.Format("WAITFOR (RECEIVE TOP (1) conversation_handle, convert(xml,message_body) FROM TargetQueue1DB), TIMEOUT 1000");
using (SqlDataReader reader = waitCommand.ExecuteReader())
{
while (reader.Read())
{
handle = reader.GetSqlGuid(0);
}
}
}
// End the conversation just for clean up
using (SqlCommand endCmd = conn.CreateCommand())
{
endCmd.Transaction = tran;
endCmd.CommandText = "End Conversation @handle";
endCmd.Parameters.Add("@handle", System.Data.SqlDbType.UniqueIdentifier);
endCmd.Parameters["@handle"].Value = handle;
endCmd.ExecuteNonQuery();
}
// Commit the transaction so the message is removed from queue.
tran.Commit();
}
}
}
}
}
}
上面的代码只是该行为的演示.当然，您通常不会像这样接收和调用回滚.
Receive方法接收消息并在事务上调用Rollback以刺激有害消息的行为.在第六次接收Receive的调用上抛出异常，因为按预期禁用了队列.
在这一点上，我想重新启用队列，拉出有害消息并结束对话(不需要结束).所有这些都有效，但是随后我提交事务，因为我真的希望该有害消息不在队列中.
结果
会在Commit调用上引发异常此SqlTransaction已完成；它不再可用.
在第六次接收调用时没有调用回滚或提交的情况下，该交易如何完成?
此外，如何删除TargetQueue1DB中的消息?我以为接收除非将它提交到事务中，否则不会将消息从队列中删除.但是，如果在调用该提交之前查看TargetQueue1DB，则该队列为空.
如果稍稍修改代码，以便在捕获SqlException时将waitCommand纳入范围，则将看到waitCommand实例的Connection和Transaction属性已设置为null.对我来说这是奇怪的行为.
SqlTransaction的客户端状态不一定反映服务器上的事务状态.考虑一下您捕获的异常是否为1205，死锁.在这种情况下，即使您在当前帧中有一个既未提交也未回滚的SqlTransaction对象，则该事务已在服务器中引发异常之前在服务器上回滚了./p>
在您的catch块中，您需要处理当前的事务对象并启动一个新的对象来执行错误处理逻辑. 该消息已删除，因为您执行了捕获处理逻辑，而没有在服务器上启动实际的事务.您使用了不再相关的过期 I am using SQL Server Broker in 2008 R2 in a C# application and am trying to handle the case where SQL Server has detected a poison message and has disabled the target queue. When this case occurs, an SqlException is thrown when I'm trying to receive a message. At that point, the SqlTransaction I'm using seems to no longer be committable. I'll use this tutorial to demonstrate along with my C# code. First use the T-SQL code from the tutorial to create the necessary service broker objects and send a message so that it's sitting in the Target queue. Next run this C# code which is a console application. The code above is just a demonstration of the behavior. Of course you wouldn't receive and call Rollback like this normally. The Receive method receives the message and calls Rollback on the transaction to stimulate the poison message behavior. On the sixth call to Receive an SQLException is thrown because the queue is disabled as expected. At this point I'd like to re-enable the queue, pull the poison message off and end the conversation (ending is not necessary). This all works, but then I Commit the transaction because I really want that poison message off the queue. Result
An exception is thrown on the Commit call stating This SqlTransaction has completed; it is no longer usable. How did this transaction get completed without a Rollback or Commit being called on that 6th invocation of Receive? Also, how did the message in TargetQueue1DB get removed? I thought the receive wouldn't remove the message off the queue unless it was in a transaction that got committed. However, if you look at the TargetQueue1DB before that commit is called, the queue is empty. If you modify the code a little so that the waitCommand is in scope when the SqlException is caught you will see the Connection and Transaction properties of the waitCommand instance have been set to null. Which is strange behavior to me. The client state of your SqlTransaction does not necessarily reflect the transaction state on the server. Consider if the exception you catch is 1205, deadlock. In such a case the transaction had already been rolled back on the server before the exception was raised in the server, even though you have a SqlTransaction object in your current frame that was neither committed nor rolled back. In your catch block you need to dispose of your current transaction object and start a new one to do your error handling logic. The message got removed because you executed your catch handling logic w/o an actual transaction being started on the server. You used the expired 这篇关于SQL Server Broker事务在有毒消息异常时完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！tran对象.您的RECEIVE立即生效(根本没有周边交易).CREATE MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
[//AWDB/1DBSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [//AWDB/1DBSample/SampleContract]
([//AWDB/1DBSample/RequestMessage]
SENT BY INITIATOR,
[//AWDB/1DBSample/ReplyMessage]
SENT BY TARGET
);
GO
CREATE QUEUE TargetQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/TargetService]
ON QUEUE TargetQueue1DB
([//AWDB/1DBSample/SampleContract]);
GO
CREATE QUEUE InitiatorQueue1DB;
CREATE SERVICE
[//AWDB/1DBSample/InitiatorService]
ON QUEUE InitiatorQueue1DB;
GO
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE
[//AWDB/1DBSample/InitiatorService]
TO SERVICE
N'//AWDB/1DBSample/TargetService'
ON CONTRACT
[//AWDB/1DBSample/SampleContract]
WITH
ENCRYPTION = OFF;
SELECT @RequestMsg =
N'<RequestMsg>Message for Target service.</RequestMsg>';
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE
[//AWDB/1DBSample/RequestMessage]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO
using System.Data.SqlClient;
namespace ServerConsoleApplication
{
class Program
{
static SqlConnection conn = null;
static void Main(string[] args)
{
conn = new SqlConnection("connection string");
conn.Open();
Receive(); // 1
Receive(); // 2
Receive(); // 3
Receive(); // 4
Receive(); // 5
Receive(); // 6 - Poison Message exception invoked
conn.Close();
}
static void Receive()
{
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
using (SqlCommand waitCommand = conn.CreateCommand())
{
waitCommand.Transaction = tran;
waitCommand.CommandText = string.Format("WAITFOR (RECEIVE TOP (1) conversation_handle, convert(xml,message_body) FROM TargetQueue1DB), TIMEOUT 1000");
using (SqlDataReader reader = waitCommand.ExecuteReader())
{
}
}
// Rollback on purpose to cause the poison message
tran.Rollback();
}
catch (SqlException ex)
{
if (ex.Number == 9617)
{
// Re-Enable the queue
using (SqlCommand enableCmd = conn.CreateCommand())
{
enableCmd.Transaction = tran;
enableCmd.CommandText = string.Format(@"ALTER QUEUE TargetQueue1DB WITH STATUS = ON");
enableCmd.ExecuteNonQuery();
}
System.Data.SqlTypes.SqlGuid handle = System.Data.SqlTypes.SqlGuid.Null;
// Pull the poison message off the queue
using (SqlCommand waitCommand = conn.CreateCommand())
{
waitCommand.Transaction = tran;
waitCommand.CommandText = string.Format("WAITFOR (RECEIVE TOP (1) conversation_handle, convert(xml,message_body) FROM TargetQueue1DB), TIMEOUT 1000");
using (SqlDataReader reader = waitCommand.ExecuteReader())
{
while (reader.Read())
{
handle = reader.GetSqlGuid(0);
}
}
}
// End the conversation just for clean up
using (SqlCommand endCmd = conn.CreateCommand())
{
endCmd.Transaction = tran;
endCmd.CommandText = "End Conversation @handle";
endCmd.Parameters.Add("@handle", System.Data.SqlDbType.UniqueIdentifier);
endCmd.Parameters["@handle"].Value = handle;
endCmd.ExecuteNonQuery();
}
// Commit the transaction so the message is removed from queue.
tran.Commit();
}
}
}
}
}
}
tran object that is no longer relevant. Your RECEIVE got committed immediately (no surrounding transaction at all).
