Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have some doubts related to transactions in SQL. I perform below steps.

  1. Connection 1 open for DB1 database
  2. Begin transaction for Connection 1
  3. Run the commands on Connection 1
  4. Connection 2 open for DB2 database
  5. Begin transaction for Connection 2
  6. Run the commands on Connection 2
  7. Commit transaction for Connection 2
  8. Commit transaction for Connection 1

If commit transaction on Connection 1 fails(rollback) then transaction on connection 2 should rollback. Is it possible? How it can be done?

share|improve this question
add comment

1 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).

share|improve this answer
3  
I would further add that MS DTC (Distributed Transaction Coordinator) is commonly used for distributing transactions. It also supports XA. –  Adam Haines Aug 8 '13 at 19:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.