Options for Non-Locking Messaging from within a SQL Server Transaction

Question

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:

1. Be readable by an arbitrary client program from within a database transaction,
2. Not add extra locking burden on the database, and
3. 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.

Answer 1

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.

Answer 2

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.

Answer 3

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.]