Shared transaction across multiple connections, or ReadUncommitted in PostgreSQL

Question

I want to open several connections within a single transaction scope, so that each connection could see the changes done by the previous ones.

I need this for tests - real code writes to the database, and testing code verifies the data was actually inserted/updated. In the end I rollback transaction scope so that the real database is not affected.

This approach works fine in SQL Server, but doesn't seem to work in PostgreSQL (I use 9.3 with Npgsql provider), below is a small example.

---

Here's the helper to run arbitrary query within a transaction scope

private void RunQuery(string query, Action<IDataReader> process)
{
    using (var connection = new NpgsqlConnection(Config.ConnectionString)) {
        connection.Open();
        connection.EnlistTransaction(Transaction.Current);

        using (var command = connection.CreateCommand()) {
            command.CommandText = query;
            using (var reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    process(reader);
                }
            }
        }
    }
}

..and here's the test code - it inserts into users table and then checks whether the user was actually inserted:

using (var scope = new TransactionScope()) {

    //"tested scenario"
    int id = 0;
    RunQuery("INSERT INTO users (name) VALUES ('foo') RETURNING id;", reader => {
        id = (int)reader.GetValue(0);
    });

    //checking
    int id2 = 0;
    RunQuery("SELECT id, name FROM users WHERE id=" + id, reader => {
        id2 = (int)reader.GetValue(0);
    });    
    Assert.That(id2, Is.Not.EqualTo(0));
}

The test above fails on Postgres as id2 is always zero. I tried TransactionScope constructor with TransactionOptions.ReadUncommitted but it doesn't seem to help. Note that if I run this against SQL Server (change NpgsqlConnection to SqlConection, use SCOPE_IDENTITY to retrieve the id) then everything works just fine and id2 is not zero.

_{As you may expect, selects within the same connection work for Postgres, but I don't need that, my goal is to use multiple connections on a shared transaction scope. I also don't need multithreading, those connections happen sequentially.}

Answer 1

First a disclaimer: while I know a bit about postgresql, I know very little about .NET.

I suspect you are conflating two related but separate concepts - that of Distributed Transactions and the level of transaction isolation that exists.

According to the .NET Documentation, EnlistTransaction adds the connection into a distributed transaction. A distributed transaction is described as follows

A distributed transaction is a transaction that affects several resources. For a distributed transaction to commit, all participants must guarantee that any change to data will be permanent. Changes must persist despite system crashes or other unforeseen events. If even a single participant fails to make this guarantee, the entire transaction fails, and any changes to data within the scope of the transaction are rolled back.

In a database, such transactions are implemented by a two-phase commit process amongst what are actually separate transactions in the database. All of the participating transactions are progressed to the end of the first phase by executing PREPARE TRANSACTION. Once they are all in this state, then they can be fully committed by executing COMMIT PREPARED. If any of them fails during PREPARE TRANSACTION, then they can all be rolled back by ROLLBACK PREPARED. This guarantees that either they are all committed, or they are all rolled back.

When using middleware such as that provided by .NET, you do not see any of these details: the framework handles the two-phase commit for you.

So, you might be wondering what this has to do with the fact that you are not seeing changes made in one part of this distributed transaction reflected in another. The answer is probably nothing. The two transactions are actually completely separate - in fact it is possible for them to be on completely separate databases.

What you are trying to achieve - to be able to see changes made in one transaction from another prior to commit - is related to the level of transaction isolation.

The bad news for you is that it sounds like the isolation level you would like to have is 'read uncommitted', which is not supported in postgresql.

Maybe you need to describe what you are trying to achieve, at a higher level - it is likely there is another (maybe better) way to achieve it.