SQL Server 事务内的非锁定消息传递选项
我维护一个基于 SQL Server 的应用程序，它要求我们向连接的客户端发送某些数据库更改的通知。这些客户端是胖客户端 C++ 应用程序，具有与服务器的高带宽连接。
到目前为止，我们一直在 SQL Server 中使用专用的消息传递表，该表通过相关表上的触发器进行更新。这样做的巨大缺点是，如果有人离开导致消息插入长时间打开的事务，则对该公共消息表的锁定会导致其他客户端（以及一般的消息传递）突然停止。
我们尝试使用扩展存储过程来与我们的应用程序进行直接套接字连接，但是当连接不可避免地出现问题时，这会导致类似的阻塞问题。
我真正在寻找的是 SQL Server（最好是 SQL Server 2000 及更高版本，但仅限 SQL Server 2008 也可以）内部的一种良好机制来触发某种消息。该消息必须：
- 可由数据库事务中的任意客户端程序读取，
- 不会给数据库增加额外的锁定负担，并且
- 速度快。
持久性/保证交付会非常好，但不是绝对必需的。
我看过 MSMQ 但我不完全理解文档。我知道 MSMQ 中有“消息事务”这样的东西，并且可以从扩展存储过程或 CLR 过程中发出消息，但是它的安全性和速度如何？这个上下文？
您能提供的任何建议将不胜感激。
编辑：为了澄清，我需要一种方法来发送到除调用应用程序之外的至少一个应用程序。这是通知/广播要求，因此下面的 RAISERROR 建议不合适。
I maintain a SQL Server-based application that requires us to send notifications of certain database changes to connected clients. These clients are fat-client C++ apps with high-bandwidth connectivity to the server.
Up until this point, we've been using a dedicated messaging table in SQL Server which is updated via triggers on the relevant tables. The huge drawback of this is that if someone leaves a transaction that causes a message insert open for extended periods of time, locking against that common messaging table causes other clients (and messaging in general) to come to a screeching halt.
We experimented with using an extended stored procedure to do direct socket connections to our apps, but this led to similar blocking problems when something inevitably went wrong with the connectivity.
What I'm really looking for is a good mechanism from within SQL Server (ideally SQL Server 2000 and up, but SQL Server 2008-only would also be OK) to fire off a message of some sort. The message must:
- Be readable by an arbitrary client program from within a database transaction,
- Not add extra locking burden on the database, and
- Be fast.
Persistence/guaranteed delivery would be very nice but not absolutely required.
I've looked at MSMQ but I don't fully understand the documentation. I understand that there is such a thing as a "message transaction" in MSMQ, and that it is possible to fire off messages from an extended stored procedure or CLR proc, but how bulletproof and fast is it in this context?
Any advice you can offer would be much appreciated.
Edit: To clarify, I need a way to send to at least one application other than the calling app. This is a notification/broadcast requirement, so the RAISERROR suggestion below is unsuitable.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
MSMQ 支持事务，这是事实，但使用 MSMQ 意味着您可以使用 MSQM 管理器将本地 SQL 事务注册到分布式事务中。这将是一个成熟的分布式事务，涉及 MSDTC 和两阶段提交。您的事务吞吐量将因此受到非常严重的性能影响。
我会在这里支持 Sung 并推荐 Service Broker。
Service Broker 将以非常高的吞吐量异步可靠地传递消息，以在同一数据库、同一实例或不同 SQL Server 实例中排队。 Service Broker 不能用于将通知直接传递到客户端应用程序（即套接字连接回调），而是将消息传递到队列（SQL Server 对象），客户端通过普通连接进行连接并发出 RECEIVE 语句以出队待处理的通知。 Service Broker API 完全是 Transact-SQL，因此您可以轻松地将其添加到触发器中。它的事务性完全包含在 SQL Server 数据库中，并且不会将本地事务提升为分布式事务，从而能够实现 每秒数千条消息的吞吐量。最大的缺点是它的学习曲线非常陡峭。
Service Broker 会在数据库中获取锁来进行操作，但如果使用正确，不会引起争用。当健忘的 Fred 保持交易开放并去吃午饭时，不会导致队列阻塞。他将阻止他使用的通道被重复使用（即 Service Broker 术语中的“对话”），但其他用户使用的其他通道保持打开状态。接收端（应用程序侦听传递的地方）根本不会阻塞。类似地，如果健忘的 Fred 运行一个应用程序并去吃午饭，同时他有一个未提交的事务使通知出队，这不会阻止其他应用程序接收通知的能力，也不会阻止更多通知入队的能力，包括 Fred 出队的通道。因此，应用程序不能阻止通知（触发器不能被慢速应用程序暂停），反之亦然。 Service Broker 的设计和实现是解耦的。
[全面披露：我是 Service Broker 团队的前成员。]
MSMQ supports transactions, true, but using MSMQ mean that you enroll your local SQL transaction in a distributed transaction with MSQM manager. This will be a fully fledged distributed transaction, with MSDTC involvement and twoo-phase commit. Your transactional throughput will take a very serious performance hit from this.
I will second Sung here and recommend Service Broker.
Service Broker will reliable deliver a message, asynchronously and with very high throughput, to queue in the same database, same instance or a different SQL Server instance. Service Broker cannot be used to deliver notification straight into a client application (ie. socket connect callback), but instead a message is delivered to a queue (a SQL Server object) and the client connects via normal connection and issues a RECEIVE statement to dequeue the pending notifications. The Service Broker API is entirely Transact-SQL so you can easily add it to triggers. Its transactional, entirely contained within a SQL Server database, and does not elevate local transactions to distributed transactions, thus being able to achieve throughput of thousands of messages per second. The biggest disadvantage is that it has a very steep learning curve.
Service Broker will take locks in the database to operate, but when used correctly, will not cause contention. When Forgetful Fred leaves his transaction open and goes to lunch it will not cause a queue blocking. He will block the channel he uses from being reused (ie. the 'conversation' in Service Broker terms) but other channels, used by other users, are left open. The receive side (where application listen for delivery) will not block at all. Similarly if Forgetful Fred runs an application and goes to lunch while he has an uncommitted transaction that dequeued a notification this will not block other applications ability to receive notifications, nor the ability for more notifications to be enqueued, including on the channel Fred dequeued from. So applications cannot block notifications (the triggers cannot be paused by slow apps) nor vice-versa. Service Broker design and implementation is decoupled.
[Full disclosure: I am a former member of the Service Broker team.]
SQL Server 2005 和 SQL Server 2005 2008 年提供了 Service Broker，这是一个消息队列系统。
您可以使用 Service Broker 激活，以便消息可以“被数据库事务中的任意客户端程序读取”。
而且，由于 Service Broker 是 SQL Server 数据库引擎的一部分，因此它“不会给数据库增加额外的锁定负担”并且应该“快速”（这完全取决于 Service Broker 的实现方式）
“持久性/保证交付将是非常好，但不是绝对必需的。”
它由 Service Broker 保证。
SQL Server 2005 & 2008 offers Service Broker, which is a message queueing system.
You can use Service Broker activation so that a message can "Be readable by an arbitrary client program from within a database transaction".
And also since Service Broker is part of SQL server database engine, it would "Not add extra locking burden on the database" and should "be fast" (this entirely depends on how Service Broker is implemented though)
"Persistence/guaranteed delivery would be very nice but not absolutely required."
It is guaranteed by Service Broker.
RAISERROR 怎么样？
http://technet.microsoft.com/en-us/library/ms178592。 aspx
您的客户端可以侦听该消息，并且您的存储过程/查询可以发出具有严重程度的消息，以便它不会被视为错误。
我相信这就是 SQL Management Studio 侦听 DBCC 操作等消息的方式。
How about RAISERROR?
http://technet.microsoft.com/en-us/library/ms178592.aspx
Your client can listen for the message, and your stored procedures/queries can raise a message with the severity such that it's not considered to be an error.
I believe this is how the SQL Management Studio listens for messages such as DBCC operations.