Multiple Joint Collaborative Transaction or ReadUncommitted in PostgreSQL

Question

I want to open several connections within the same transaction so that each connection can see the changes made by the previous ones.

I need this for tests - the real code is written to the database, and the test code checks to see if the data has really been inserted / updated. At the end, I discard the transaction transaction, so as not to affect the real database.

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

---

Here's a helper for starting an arbitrary request within a transaction

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 is the test code - it inserts into the users table and then checks if the user was really 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 does not work in Postgres, since id2 always zero. I tried the TransactionScope constructor with TransactionOptions.ReadUncommitted , but it doesn't seem to help. Note that if I run this for SQL Server (change NpgsqlConnection to SqlConection , use SCOPE_IDENTITY to extract the identifier), then everything will work fine, and id2 not zero.

_{As you expect, it selects connections for Postgres within the same work, but I do not need it, my goal is to use several connections in the general transaction area. I also do not need multithreading, these connections occur sequentially.}

Answer 1

First caveat: although I know a little about postgresql, I know very little about .NET.

I suspect that you are combining two related but different concepts - distributed transactions and the level of transaction isolation that exists.

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

A distributed transaction is a transaction that affects multiple Resources. For a distributed transaction to complete, all participants must ensure that any data change is permanent. Changes must be maintained despite system failures or other unforeseen events. If even one participant cannot fulfill this guarantee, the entire transaction fails, and any changes in the data within the transaction are rolled back.

In the database, such transactions are implemented through a two-phase commit process between those that are actually separate transactions in the database. All participating transactions advance to the end of the first stage by performing PREPARE TRANSACTION . When they are all in this state, they can be completely committed by doing COMMIT PREPARED . If any of them does not work during PREPARE TRANSACTION , then all of them can be dropped on ROLLBACK PREPARED . This ensures that either they are all perfect or they are all rolled back.

When using middleware such as .NET, you don't see any of these details: the framework handles two-phase commit for you.

So, you may be interested in the fact that this is due to the fact that you do not see changes made to one part of this distributed transaction, reflected in another. The answer is probably nothing. The two transactions are actually completely separated - in fact, they can be completely separated.

What you are trying to achieve - to see changes made in one transaction from another to commit, is related to the level of 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.

Perhaps you need to describe what you are trying to achieve at a higher level — there is probably another (possibly better) way to achieve it.