# Managing Distributed Transactions in PostgreSQL and Golang using two phase commit

If you're building a distributed system with PostgreSQL as the database backend, you might have encountered issues with managing transactions across multiple nodes. When a transaction spans multiple databases, ensuring atomicity and consistency can be a challenge. That's where [`gosql2pc`](https://github.com/gosom/gosql2pc) comes in.

[`gosql2pc`](https://github.com/gosom/gosql2pc) is a Golang library for implementing 2 phase commit transactions in PostgreSQL, ensuring atomicity and consistency across distributed systems. With `gosql2pc`, you can manage transactions across multiple databases, ensuring that all changes are committed or rolled back atomically.

## Getting Started

To get started with `gosql2pc`, you need to have a working knowledge of Golang and PostgreSQL. You also need to have PostgreSQL installed, as `gosql2pc` relies on the PostgreSQL 2-phase commit protocol.

Once you have PostgreSQL installed, you can install `gosql2pc` using the following command:

```bash
go get github.com/gosom/gosql2pc
```

Then, use the library's API to create participants for your distributed transaction.

Here's an example of using gosql2pc to create a simple distributed transaction that inserts a new user and order into two separate databases:

```go
// Create the participants for the 2 phase commit
p1 := twophase.NewParticipant(db1, func(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, "INSERT INTO users (id, name) VALUES ($1, $2)", userID, name)
    return err
})

p2 := twophase.NewParticipant(db2, func(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, "INSERT INTO orders (id, user_id, amount) VALUES ($1, $2, $3)", orderID, userID, amount)
    return err
})

// setup the parameters for the transaction
params := twophase.Params{
    Participants: []gosql2pc.Participant{p1, p2},
}

// run the transaction
if err := twophase.Do(context.Background(), params); err != nil {
    panic(err)
}
```

You can find more examples in the library's [`example`](https://github.com/gosom/gosql2pc/tree/main/example) directory.

**Notes**

It's worth noting that distributed transactions can be tricky to manage, and gosql2pc is no exception. PostgreSQL has disabled prepared transactions by default for a good reason, and enabling them can lead to orphaned transactions and data inconsistencies if not monitored carefully.

Nevertheless, gosql2pc provides a useful tool for simplifying the implementation of distributed transactions. If you find a bug or want to suggest a new feature, contributions are always welcome.

To learn more about gosql2pc, including how to enable prepared transactions and monitor for orphaned transactions, check out the library's [README](https://github.com/gosom/gosql2pc) file and the accompanying blog posts.