Distributed transactions

greenspun.com : LUSENET : DBAzine : One Thread

Distributed transactions with 2PC have always puzzled me. It is common knowledge that there is a prepare phase and a commit phase. I want to know what exactly happens in a prepare phase and commit phase and in what sequence.

Suppose a distributed transaction coordinator has to invoke a stored procedure on my Oracle database (which acts as a RM). The stored procedure has a SQL statement to insert a row in a table.

a) At what point the code to insert the row is executed ? At what point the locks are acquired ? Prepare phase or commit phase? b) What happens if my code implicitly performs a commit after insert and the distributed transaction coordinator decides to rollback the transaction since the other non-Oracle database voted with a no? c) Does Oracle(as a RM) actually track the various phases of a distributed transaction as it progresses from prepare to commit ? If yes, in which data dictionary view? d) At what point Oracle (as RM) will mark a transaction as in-doubt? Is it after it sent a "PREPARED" response but never hears back from the TPM? How long would Oracle wait to hear back from TPM?

Lot of questions, but I feel that certain parts of Oracle functionality are not completely covered in the documentation and Metalink resources. Thanks...

-- Arun Gupta (c-agupta@state.pa.us), November 24, 2003

Moderation questions? read the FAQ