Solved

2 Phase Commit Transaction in SQL Server

Posted on 2014-10-15
2
170 Views
Last Modified: 2014-10-17
What is a 2 phase commit transaction? And is there such a thing in SQL Server?
0
Comment
Question by:metro156
2 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 40383260
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
1.Prepare Phase :: Global coordinator asks participants to prepare
2.Commit Phase :: Commit all participants to coordinator to Prepared, Read only or abort Reply

Here is an architecture that involves sql server
http://msdn.microsoft.com/en-ca/library/aa754091(v=bts.10).aspx
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40383264
2-phase commit is if two (or more, theoretically) databases have had recoverable resources modified during a transaction.  SQL Server absolutely uses 2-phase commit, because there's no real way otherwise to guarantee "all or none" transactions across all resources, which is required by the relational model.  It's most significant with linked servers and other external resources but a version of it is also required if you modify recoverable resources in 2 or more local dbs.

For example, say you are transferring money value from a local db to a remote db.  You never want the money to disappear; likewise, you never want the same money to appear in both locations.  2-phase commit insures that the money is adjusted in both places or neither place, never just one or the other.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question