Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

2 Phase Commit Transaction in SQL Server

What is a 2 phase commit transaction? And is there such a thing in SQL Server?
0
metro156
Asked:
metro156
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Scott PletcherSenior DBACommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now