Keep a transaction open on SQL Server with connection closed

Question

On SQL Server, is it possible to begin a transaction but intentionally orphan it from an open connection yet keep it from rolling back?

The use-case it for a REST service.

I'd like to be able to link a series of HTTP requests to work under a transaction, which can be done if the service is stateful; i.e. there's a single REST API server holding a connection open (map HTTP header value to a named connection), but a flawed idea in a non-sticky farm of web servers.

If the DB supported the notion of something like named/leased transactions, kinda like a named mutex, this could be done.

I appreciate there are other RESTful designs for atomic data mutations.

Thanks.

Answer 1

No. A transaction lives and dies with the session it's created in, and a session lives and dies with its connection. You can keep a transaction open for as long as you like -- but only by also keeping the session, and thereby the connection open. If the session is closed before the transaction commits, it automatically rolls back. Which is a good thing, in general, because transactions tend to use pessimistic locking. You don't want to keep those locks around for longer than necessary.

While there is such a thing as a distributed transaction that you can enlist in even if the current connection did not begin the transaction, this will still not do what you want for the scenario of multiple distributed nodes performing actions in succession to complete a transaction on one database. Specifically, you'd still need to have one "master" node to keep the transaction alive and decide it should finally commit now, and you need a way to make nodes aware of the transaction so they can enlist. I don't recommend you actually go this way, as it's much more complicated than tailoring a solution to your specific scenario (typically, accumulating modifications in their own table and committing them as a batch when they're complete, which can be done in one transaction).

Answer 2

You could use a queue-oriented design, where the application simply adds to the queue, while SQL server agent 'pop's the queue and executes.