Extending Mark's answer...
When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released.
Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens (via KILL or client reboot etc) then the transactions+locks will be cleared. Note that sp_reset_connection won't or doesn't clear them, even though it is advertised to do so
This detritus from the abort will block other processes.
The way to make SQL Server clear transactions+locks on client timeout (strictly, ABORT events) is to use SET XACT_ABORT ON.
You can verify this be opening 2 query windows in SSMS:
Window 1:
In menu Query..Query Options set a timeout of 5 seconds then run this
BEGIN TRAN
UPDATE sometable WITH (TABLOCKX) SET foo = foo WHERE 1 = 0;
WAITFOR DELAY '00:00:10' -- just has to be longer then timeout
Window 2, this will wait forever (or hit your timeout)
SELECT * FROM sometable
SET XACT_ABORT ON has interesting side effects too:
- @@TRANCOUNT is set to zero on the implicit rollback but error 266 is suppressed (this happens if @@TRANCOUNT is different on entry and exit from a stored proc)
- XACT_STATE will be -1 (it's "doomed")
The combination of this means that you can't use SAVEPOINTS (although, I can't recall exact behaviour) for partial commits/rollbacks. Which suits me
SO links on SET XACT_ABORT:
On nested stored procs:
On sp_reset_connection:
http://msdn.microsoft.com/en-us/library/ms188716.aspx
Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.
Best Answer
Yes this can be done but you need a third party acting as a transaction coordinator. The standard protocol for this is called Two Phase Commit (2PC). This is usually done with a transaction manager acting as the coordinator.
This can also be generalized further to more than two databases. In fact it doesn't even have to be databases as the approach is generally applied to "transactional resources". Besides databases the other common transactional resource is a message queue. The usual example is to perform some work in a database (ex: update an account balance) and also send a message out on a message queue (ex: an outbound order).
For this to work your datasources need to support 2PC, usually via the XA protocol. How to do it depends on the specific type of datasource and programming language (ex: for Java you'd use an XADataSource).