The send that never arrives

Here is a problem with a deceptively obvious solution.

A stored procedure is halfway through a transaction and needs something from outside the database — an HTTP response from a tax authority, a document rendered, a file fetched. The engine cannot make that call itself, and on Linux it does not even have the option of reaching through to a COM object to do it. So it asks a worker process and waits for the answer.

That is a queue. Queues are well understood. SQL Server has one built in, with message types and contracts and a SEND and a RECEIVE. Half an hour of reading and you would write this:

Send the request. Wait to receive the reply. Carry on.

That implementation does not work. Not intermittently, not under load — it fails every single time, on every platform, and the reason is one sentence long.

A message sent inside an open transaction has not been sent.

Service Broker is transactional, which is the entire reason to use it. A SEND is not delivered when you execute it; it is delivered when the transaction that executed it commits. That is the correct behaviour and it is why you can enqueue work and update a row and be certain the two agree.

But every TSQL.APP action script runs inside a transaction. So the request sits undelivered in the sending transaction, the worker never sees it, the reply never comes, and the WAITFOR waits out its timeout and gives up. The obvious implementation is guaranteed to time out one hundred per cent of the time, which is at least an honest failure mode — a bug that never works is much cheaper than one that usually does.

The way out#

The request has to leave through a transaction that is willing to commit immediately, which means it cannot be the caller's transaction. So it goes out through a second connection — to the same server, back into the same database:

sqlCopy
IF @@TRANCOUNT > 0
BEGIN
    SET @sql = N'EXEC LinkedProjServer.' + QUOTENAME(DB_NAME())
             + N'.dbo.sp_api_broker_request_linked_server @id=@id, @wait=@wait, @idout=@idout OUT'
    ...
    SELECT @dataout = json_out FROM api_broker_request (NOLOCK) WHERE id = @idout
END

A linked server pointing at itself. It looks like a mistake and it is the only honest way to express what is needed: a connection whose transaction lifetime is not the caller's. The send commits there, immediately, while the caller's transaction is still open and untouched.

The part that surprises people#

Look at the last line of that block, because it is where the real mechanism lives.

The request goes out through the queue and the answer comes back through a table.

There is no RECEIVE in the caller's path at all. The worker does its job and writes the reply into a correlation table keyed by the request id, and the caller reads it from there. The queue carries the request; a row carries the response.

Once you see it, a second thing falls out of it, and this is the part worth knowing if you ever write one of these calls yourself.

This is why an "asynchronous" call still blocks. The wait parameter is not a timeout on some background operation you can forget about — it holds the connection, and its open transaction, for up to that many milliseconds while the answer is polled for. Ask for thirty seconds and a slow remote endpoint can hold a transaction open for thirty seconds. Ask for zero and you get genuine fire-and-forget: the work is queued, nothing waits, and the answer is collected later or not at all.

Most of the sharp edges people hit with this are really that one fact wearing a disguise. A screen that feels slow is usually a script doing three fetches in a row with a generous wait on each, inside a transaction, while a row somewhere stays locked.

The constraint that comes with it#

Every design decision costs something, and this one has a bill attached that is worth naming, because the alternative is discovering it yourself.

A procedure that runs under owner impersonation cannot use a linked server without a login mapping. The engine refuses, and it is right to refuse — impersonation means the caller's identity has been deliberately replaced, and there is then no identity to present to the far end.

So impersonation and this loopback are mutually exclusive. The framework needs the loopback, and it therefore does not impersonate: the request path runs as a single database login throughout, and authorisation is done in session context, which the engine clears between pooled requests. Permissions are decided by the framework's own role model rather than by the connection's identity.

That is a real trade and not a free one. It means you cannot bolt WITH EXECUTE AS OWNER onto a procedure in the request path and expect it to keep working, and the failure — if you do — will point you at the queue rather than at the impersonation, because from the caller's point of view what happened is simply that the worker never answered.

The general shape of this is the thing to take away. Asynchronous IO from inside a transactional engine is not a queue with a callback. It is a queue for the outbound half, a separate transaction to get it out of the door, and a table for the reply — and the reason it is built that way has nothing to do with which operating system it is running on. That is why it moved to Linux without being redesigned.

There is documentation and a programming reference online, and you can always book a demo.