How to Implement Two-Phase Commit in Python So Two Databases Commit or Abort Together

Imagine a customer moves $200 from checking to savings, and those two accounts live in two separate databases, each owned by its own service, each with its own perfectly good transaction manager. Debiting checking is a clean, local, all-or-nothing operation. Crediting savings is too. The problem is the operation as a whole: nothing about either database alone can guarantee that both halves happen, or that neither does. If your process is interrupted between the two calls, you can end up with money gone from checking that never arrived in savings, and no single database’s transaction log will ever show you did anything wrong, because from each database’s own point of view, it did exactly what it was told.

Two-phase commit, usually shortened to 2PC, is the classic protocol for closing that gap. It lets a group of independent databases agree, as a single unit, to either all commit or all abort, even though none of them can see what the others are doing. In this tutorial you will build a real 2PC coordinator and a set of participants from scratch in Python, watch the naive approach lose money for real, then reproduce 2PC’s own famous weakness (the “blocking problem”) and fix the part of it that can be fixed with a durable decision log. Every command and every number below comes from code that was actually run in this sandbox, not paraphrased from documentation.

What You Will Build, and Why This Problem Is Real

Start with a term you already know: a database transaction. When you run BEGIN, some writes, then COMMIT, the database guarantees that either every one of those writes lands or none of them do, even if the machine loses power halfway through. That guarantee is scoped to one database. It says nothing about a second, unrelated database that you also happen to be writing to in the same business operation.

Two-phase commit extends that same all-or-nothing guarantee across multiple databases by introducing two new roles and a two-step conversation between them:

• Participant (also called a resource manager, or a cohort): one of the databases involved. Each participant can do everything a normal database can do, plus one new trick: it can get a write ready to commit without actually committing it yet, in a way that survives a restart.
• Coordinator (also called a transaction manager): a single component that drives the whole protocol. It talks to every participant and makes the final call: commit, or abort.

The conversation has two phases, and this is where the protocol gets its name. According to Wikipedia’s description of the protocol, phase one is “the commit-request phase (or voting phase), in which a coordinator process attempts to prepare all the transaction’s participating processes… to vote” yes or no on whether they can commit, and phase two is “the commit phase, in which, based on voting of the participants, the coordinator decides whether to commit… or abort the transaction… and notifies the result to all the participants.”

That is the whole idea in one sentence: ask everyone if they can do their part, and only if every single answer is yes does anyone actually do it. This tutorial builds that idea as working code, using SQLite databases standing in for what would, in a real system, be two entirely separate services.

Prerequisites

• Python 3.10 or newer (built and tested here on Python 3.13.14, using only the standard library: sqlite3 and os, no packages to install for the core protocol)
• pytest for the verification suite at the end (pip install pytest)
• Basic SQL: you should know what SELECT, UPDATE, BEGIN, COMMIT, and ROLLBACK do
• No admin rights, no cloud account, and no server needed. Everything in this tutorial runs against local SQLite files.

Step 1: See the Problem Without Two-Phase Commit

Before building the fix, reproduce the failure it fixes. Two SQLite files stand in for Checking and Savings, each with its own connection, each committed independently, one after the other:

DB_A = "checking.db"
DB_B = "savings.db"

def naive_transfer(amount, simulate_crash_between_writes):
    print(f"Before: checking={get_balance(DB_A, 'alice')}, savings={get_balance(DB_B, 'alice')}")

    conn_a = sqlite3.connect(DB_A)
    conn_a.execute(
        "UPDATE accounts SET balance = balance - ? WHERE name = 'alice'", (amount,)
    )
    conn_a.commit()
    conn_a.close()
    print(f"Step 1 committed: checking is now {get_balance(DB_A, 'alice')}")

    if simulate_crash_between_writes:
        raise RuntimeError("process crashed after step 1, before step 2")

    conn_b = sqlite3.connect(DB_B)
    conn_b.execute(
        "UPDATE accounts SET balance = balance + ? WHERE name = 'alice'", (amount,)
    )
    conn_b.commit()
    conn_b.close()
    print(f"Step 2 committed: savings is now {get_balance(DB_B, 'alice')}")

The raise RuntimeError stands in for a process crash, a deploy restart, or a dropped network connection landing at the worst possible moment: right after the first commit has already landed on disk, right before the second one starts. Seed checking with $500 and savings with $100, then try to move $200 and let it crash mid-flight:

$ python demo1_naive_dual_write.py
Before: checking=500, savings=100
Step 1 committed: checking is now 300
CRASH: process crashed after step 1, before step 2

After crash: checking=300, savings=100, total=400
Expected total if nothing had happened: 600
$200 is simply gone: debited from checking, never credited to savings.

Both databases are individually perfectly consistent. Checking’s own transaction log shows a clean, valid debit. Savings was never touched, and its own log is equally clean. Neither database did anything wrong. The bug lives entirely in the gap between them, in the fact that nothing durable recorded “these two writes were supposed to happen together.” That gap is exactly what two-phase commit exists to close.

Step 2: Build a Participant That Can Prepare Without Committing

The core new capability a 2PC participant needs is the ability to stage a write, durably enough to survive a crash, without making it visible or final yet. SQLite gives you this almost for free through an open transaction:

class Participant:
    def __init__(self, name, db_path):
        self.name = name
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path, isolation_level=None)
        self._in_prepared_state = False

    def prepare(self, account, delta):
        self.conn.execute("BEGIN IMMEDIATE")
        row = self.conn.execute(
            "SELECT balance FROM accounts WHERE name = ?", (account,)
        ).fetchone()
        if row is None:
            self.conn.execute("ROLLBACK")
            raise VoteNo(f"{self.name}: no such account {account!r}")
        new_balance = row[0] + delta
        if new_balance < 0:
            self.conn.execute("ROLLBACK")
            raise VoteNo(f"{self.name}: insufficient funds for {account!r}")
        self.conn.execute(
            "UPDATE accounts SET balance = ? WHERE name = ?", (new_balance, account)
        )
        self._in_prepared_state = True
        return True

    def commit(self):
        if not self._in_prepared_state:
            raise RuntimeError(f"{self.name}: commit() called without a prior prepare()")
        self.conn.execute("COMMIT")
        self._in_prepared_state = False

    def abort(self):
        if not self._in_prepared_state:
            return
        self.conn.execute("ROLLBACK")
        self._in_prepared_state = False

isolation_level=None turns off Python’s sqlite3 module’s own implicit transaction management, so nothing commits or rolls back on your behalf. You are in full control of exactly when the write becomes real.

prepare() does the UPDATE and then deliberately stops, without calling COMMIT. From the point of view of this one connection, the new balance is already sitting there. From the point of view of everyone else, nothing has changed yet, because SQLite will not make an uncommitted write visible to another connection. commit() and abort() are the only two ways this half-finished state can be resolved.

Why BEGIN IMMEDIATE, and Not Plain BEGIN

Ordinary BEGIN in SQLite is lazy: it does not actually take a write lock until the first write statement runs. BEGIN IMMEDIATE grabs SQLite’s RESERVED lock right away. SQLite’s own locking documentation defines it precisely: “A RESERVED lock means that the process is planning on writing to the database file at some point in the future but that it is currently just reading from the file. Only a single RESERVED lock may be active at one time…” That single detail, that at most one connection can hold a RESERVED lock, is what will make the blocking problem in Step 7 concrete and visible instead of theoretical.

Step 3: The Coordinator’s Voting Phase

The coordinator’s job in phase one is simple to state: ask every participant to prepare, and stop at the first no.

votes_ok = []
try:
    for p in participants:
        p.prepare(account, delta_by_participant[p.name])
        votes_ok.append(p)
except VoteNo as exc:
    for p in votes_ok:
        p.abort()
    self._write_log(txn_id, "ABORT")
    return "ABORT", str(exc)

Notice what happens on a no vote: everyone who already said yes, and is therefore sitting there holding a staged write, has to be told to abort too. A transfer only makes sense as a matched pair. If checking cannot afford the debit, the fact that savings was perfectly happy to accept the credit is irrelevant; savings’ own staged write has to be thrown away along with checking’s.

Step 4: Make the Decision Durable Before Telling Anyone

If every vote came back yes, the coordinator’s decision is COMMIT. The critical detail is what happens next, and in what order:

def _write_log(self, txn_id, decision):
    with open(self.log_path, "a", encoding="utf-8") as f:
        f.write(f"{txn_id}\t{decision}\n")
        f.flush()
        os.fsync(f.fileno())

# ... inside run(), after all votes are in:
self._write_log(txn_id, "COMMIT")
for p in participants:
    p.commit()

The decision is written to a plain append-only log file, and flush() plus os.fsync() forces it out of the operating system’s write cache and onto the physical disk before the function returns. Only after that line finishes does the coordinator start telling participants to commit.

That single _write_log() call is the most important line in this entire protocol. It is sometimes called the transaction’s commit point: the instant it durably lands, the transaction’s fate is permanently decided, even if every participant has not heard about it yet and even if the coordinator itself dies one line later. Step 8 shows exactly why that matters.

Step 5: Run the Happy Path

With both pieces built, wire them together and run a real transfer where nothing goes wrong:

coordinator = Coordinator(LOG_PATH)
decision, reason = coordinator.run(
    txn_id="txn-001",
    participants=[checking, savings],
    account="alice",
    delta_by_participant={"checking": -200, "savings": +200},
)

$ python demo2_happy_path.py
Before: checking=500, savings=100
Coordinator decision: COMMIT
After:  checking=300, savings=300, total=600

$600 in, $600 out, split correctly between the two accounts, verified by opening two brand new connections after the fact rather than trusting the in-memory objects that did the writing.

Step 6: Run the Abort Path

Now try to move $200 out of an account that only has $50 in it. Checking’s prepare() will notice the balance would go negative and raise VoteNo, even though savings, asked first, already said yes and staged its own credit:

$ python demo3_abort_on_no.py
Before: checking=50, savings=100
Coordinator decision: ABORT (checking: insufficient funds for 'bob')
After:  checking=50, savings=100, total=150
Both balances are untouched: the savings side's staged +200 write was rolled back.

This is the payoff of the two-phase design: one participant’s honest “I can’t do this” travels back through the coordinator and unwinds a write that a different, entirely willing participant had already staged. Neither database had to know about the other’s internal state to make that happen correctly.

Step 7: What Happens When the Coordinator Crashes (The Blocking Problem)

Now break the coordinator itself. Every participant votes yes, but the coordinator crashes immediately afterward, before writing anything to the log at all:

try:
    coordinator.run(
        txn_id="txn-003",
        participants=[checking, savings],
        account="carol",
        delta_by_participant={"checking": -200, "savings": +200},
        crash_point="before_log",
    )
except CoordinatorCrash as exc:
    print(f"COORDINATOR CRASHED: {exc}")

$ python demo4_blocking_problem.py
COORDINATOR CRASHED: txn-003: coordinator crashed after collecting votes, before writing a decision to the durable log

Log file exists: False
checking._in_prepared_state: True
savings._in_prepared_state: True

Both participants are still sitting there mid-transaction, each holding a staged write and a RESERVED lock, with nobody left to tell them what to do. This is not a bug in this particular implementation. Wikipedia’s own entry on the protocol lists it as the algorithm’s central, unavoidable weakness: “The greatest disadvantage of the two-phase commit protocol is that it is a blocking protocol. If the coordinator fails permanently, some participants will never resolve their transactions: After a participant has sent an agreement message… it will block until a commit or rollback is received.” A participant that already voted yes cannot safely guess. Committing on its own could be wrong if another participant actually voted no. Aborting on its own could be wrong if everyone else already committed. So it does neither, and it waits.

Two Observers, Two Different Answers

This is a real, easy-to-miss trap when you go looking for proof that the write is really stuck. Ask checking for carol’s balance through its own connection, the one still holding the open transaction, and compare that to a completely fresh connection to the same file:

print(f"checking's own connection: {checking.get_balance('carol')}")
outside = Participant("checking", CHECKING_DB)
print(f"a fresh, separate connection: {outside.get_balance('carol')}")

check.get_balance() on checking's own (still-open) connection: 300  <- this is the PENDING value, not committed
A fresh, separate connection's get_balance(): 500  <- this is what everyone else actually sees

300, not 500. A connection always sees its own uncommitted writes; that is ordinary read-your-own-writes behavior, not evidence the change is durable. If you only ever check through the connection that made the change, you can convince yourself a two-phase commit succeeded when it is, in fact, still hanging open. The fresh connection is the one telling the truth, and it still shows the pre-transfer balance of 500.

Now prove the lock is not just an in-memory flag by having that same fresh connection try to write to the row:

blocked_conn = sqlite3.connect(CHECKING_DB, timeout=0.5)
blocked_conn.execute("BEGIN IMMEDIATE")
blocked_conn.execute("UPDATE accounts SET balance = balance - 1 WHERE name = 'carol'")
blocked_conn.execute("COMMIT")

  OperationalError: database is locked
  This is the blocking problem made concrete: carol's checking row
  is unwritable by anyone until the coordinator comes back and
  resolves this transaction one way or the other.

That is a real sqlite3.OperationalError, raised by SQLite’s own locking layer, not a simulation. Until something resolves this transaction, carol’s row is off-limits to the rest of the system.

Step 8: Recovering an In-Doubt Transaction From the Log

Run the same crash again, but this time let the coordinator get one line further: past the durable log write, and only then crash, before it can deliver the decision to either participant.

try:
    coordinator.run(
        txn_id="txn-004",
        participants=[checking, savings],
        account="dave",
        delta_by_participant={"checking": -200, "savings": +200},
        crash_point="after_log",
    )
except CoordinatorCrash as exc:
    print(f"COORDINATOR CRASHED: {exc}")

$ python demo5_recovery.py
COORDINATOR CRASHED: txn-004: coordinator crashed after logging COMMIT, before delivering it to any participant

Log file exists: True
Log contents: txn-004	COMMIT
checking._in_prepared_state (still holding its lock): True

Both participants are still stuck, exactly like Step 7. The difference is on disk, not in memory: the log already says COMMIT. A brand new Coordinator object, standing in for the process restarting after a crash or a redeploy, reads that log and finishes the job it never got to announce:

recovering_coordinator = Coordinator(LOG_PATH)
decision = recovering_coordinator.recover(
    txn_id="txn-004", in_doubt_participants=[checking, savings]
)

Recovery decision read from the log: COMMIT
Final: checking=300, savings=300, total=600

The transaction completes correctly, even though the original coordinator process is gone forever. This is the entire reason Step 4’s os.fsync() exists: it is what turns an in-memory decision that dies with the process into a durable fact that any future process can read back and act on. Compare this to Step 7, where the crash landed one step earlier, before that fsync ever ran: there, no amount of restarting the coordinator can recover the decision, because the decision was never actually made durable. The only safe default in that case is to abort, since nothing on disk anywhere can prove a commit was ever decided.

Common Mistakes

Trusting the connection that made the write

As shown in Step 7, reading a value back through the same connection that just wrote it tells you nothing about whether anyone else can see it yet. Always verify durability through a separate connection or a separate process.

Skipping the fsync, or writing the decision after telling participants

If you write the log without os.fsync(), the write can sit in the operating system’s page cache and vanish on a real crash, silently reopening the exact in-doubt window the log exists to close. If you tell participants to commit before the log write finishes, a crash in between can leave one participant committed and the log still empty, which is worse than either Step 7 or Step 8: recovery would read “no decision” and default to abort, while a participant may have already committed. The order in Step 4, log first, deliver second, is not a stylistic choice.

Leaving a prepared transaction open too long

This is not just a toy-implementation risk. PostgreSQL’s own documentation for its real PREPARE TRANSACTION command warns: “It is unwise to leave transactions in the prepared state for a long time… the transaction continues to hold whatever locks it held.” A stuck 2PC participant is not a passive, harmless waiting state; it is an active lock that can block other work, degrade a database’s ability to reclaim storage, and in PostgreSQL’s case can even force a shutdown in extreme scenarios. A production coordinator needs a recovery process that runs promptly, not eventually.

Confusing 2PC with 2PL

Two-phase commit and two-phase locking (2PL) are two completely different concepts that happen to share a name pattern. 2PC is what this tutorial built: an atomic-commitment protocol across multiple databases. 2PL is a concurrency-control technique for deciding when a single database can safely acquire and release locks. Wikipedia’s own article on the protocol includes an explicit disambiguation for exactly this reason.

Verify Everything Works End to End

Every behavior demonstrated above is also pinned down as an automated test, so a future change cannot silently break the blocking guarantee or the recovery path:

$ python -m pytest test_two_phase_commit.py -v
test_two_phase_commit.py::test_happy_path_commits_both PASSED
test_two_phase_commit.py::test_insufficient_funds_aborts_both PASSED
test_two_phase_commit.py::test_unknown_account_votes_no PASSED
test_two_phase_commit.py::test_crash_before_log_leaves_participants_locked PASSED
test_two_phase_commit.py::test_crash_after_log_is_recoverable PASSED
test_two_phase_commit.py::test_recovery_with_missing_log_entry_defaults_to_abort PASSED
test_two_phase_commit.py::test_commit_without_prepare_raises PASSED

7 passed in 0.21s

To confirm the whole system for yourself: run demo1 and watch the naive version lose $200, run demo2 and demo3 to see the coordinator commit and abort correctly, then run demo4 followed immediately by demo5 to see the same crash produce two different outcomes depending on exactly one thing: whether the decision reached durable storage before the crash happened. If all of that matches what is printed above, your implementation is behaving exactly like a real two-phase commit protocol should.

Next Steps

Two-phase commit gives you a strong guarantee (every participant either commits or none do) at a real cost: a coordinator crash at the wrong moment can leave part of your system holding locks it cannot release on its own. That tradeoff is exactly why most modern distributed systems reach for a different pattern instead. If you have not already, read this site’s own tutorial on the Transactional Outbox pattern: it solves the same dual-write problem this tutorial opened with, but by trading 2PC’s synchronous, blocking guarantee for an asynchronous, retry-based one that can never lock a remote system indefinitely. Once you have built both, you will be able to explain precisely which real-world situations call for each one.

The durable log built in Step 4 is a small, single-purpose version of a much bigger idea covered in this site’s write-ahead log tutorial: write your intent durably before you act on it. If you want to go deeper into how a real, production-grade database exposes two-phase commit at the SQL level, including how it survives its own restart, read PostgreSQL’s documentation for PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED, and look up the X/Open XA standard that most real transaction managers implement.