I have a situation where I need to use distributed transactions (TransactionScope) but am having some problems which I have isolated in the samples below.
I have two tables: asset and asset_type. asset has a foreign key, asset_type_id, which references asset.
There is a situation where, within a single distributed transaction, I will insert a record into asset_type followed by an insert into asset referencing the asset_type record just inserted.
If I do these operations using a raw connection, everything works fine. If I do them using a DbContext, I get a referential integrity exception during the second insert. If I remove the TransactionScope the DbContext example works fine.
I have included dbMonitor logs and one thing that jumps out at me in the DbContext version is that it appears the connection is opened twice and a transaction is started twice. I'm just guessing, but perhaps the second insert is occurring in a different transaction so doesn't have access to the first insert.
I don't know if this is your issue or a problem with Entity Framework or a problem with the way I'm using it. Any insight would be appreciated.
This works as expected:
Code: Select all
var connectionString = "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw";
using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
using (var conn = new PgSqlConnection(connectionString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id";
var assetTypeId = (int) cmd.ExecuteScalar();
cmd.CommandText = string.Format("INSERT INTO overview.asset "
+ "(asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) "
+ "VALUES ({0}, 'mid', TRUE, TRUE, FALSE, 0, 0 ) "
+ "RETURNING id ", assetTypeId);
var assetId = (int) cmd.ExecuteScalar();
trans.Complete();
}
}
This fails with a referential integrity exception:
Code: Select all
using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted}))
{
using (var context = new TestContext(new PgSqlConnection(connectionString), true))
{
var assetTypeId = context.Database
.SqlQuery("INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id")
.Single();
var assetId = context.Database
.SqlQuery(string.Format("INSERT INTO overview.asset "
+ "(asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) "
+ "VALUES ({0}, 'mid', TRUE, TRUE, FALSE, 0, 0 ) "
+ "RETURNING id ", assetTypeId))
.Single();
}
trans.Complete();
}
TestContext doesn't do anything except remove any initialization and expose the constructor I want to use:
Code: Select all
internal class TestContext : DbContext
{
static TestContext()
{
Database.SetInitializer(null);
}
public TestContext(DbConnection existingConnection, bool contextOwnsConnection)
: base(existingConnection, contextOwnsConnection)
{
}
}
Log for DbConnection example:
Code: Select all
7/15/2011 4:47:11 PM n/a dotConnect for PostgreSQL monitoring is started Complete
7/15/2011 4:47:11 PM 0.000 Creating pool manager Complete
7/15/2011 4:47:11 PM 0.016 Creating pool with connections string: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
7/15/2011 4:47:11 PM 0.000 Creating object Complete
7/15/2011 4:47:11 PM 0.156 Open connection: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw;" Complete
7/15/2011 4:47:11 PM 0.000 Connect: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
7/15/2011 4:47:11 PM 0.016 Execute: SELECT version() Complete
7/15/2011 4:47:11 PM 0.000 Execute: show integer_datetimes Complete
7/15/2011 4:47:11 PM 0.000 Execute: SET autocommit=true Complete
7/15/2011 4:47:11 PM 0.000 Execute: SET datestyle=ISO Complete
7/15/2011 4:47:11 PM 0.015 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
7/15/2011 4:47:11 PM 0.000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
7/15/2011 4:47:11 PM 0.000 Execute: BEGIN Complete
7/15/2011 4:47:11 PM 0.000 Prepare: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
7/15/2011 4:47:11 PM 0.016 Execute: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
7/15/2011 4:47:11 PM 0.000 Prepare: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id Complete
7/15/2011 4:47:11 PM 0.031 Execute: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id Complete
7/15/2011 4:47:11 PM 0.000 Execute: PREPARE TRANSACTION 'a44d0fbb-4921-4065-b200-c640755a88c3' Complete
7/15/2011 4:47:11 PM 0.015 Execute: COMMIT PREPARED 'a44d0fbb-4921-4065-b200-c640755a88c3' Complete
7/15/2011 4:47:11 PM 0.000 Execute: COMMIT Complete
7/15/2011 4:47:11 PM 0.141 Connection is returned to pool. Pool has 1 connection(s). Complete
Code: Select all
7/15/2011 4:48:37 PM n/a dotConnect for PostgreSQL monitoring is started Complete
7/15/2011 4:48:37 PM 0.015 Creating pool manager Complete
7/15/2011 4:48:37 PM 0.000 Creating pool with connections string: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
7/15/2011 4:48:37 PM 0.016 Creating object Complete
7/15/2011 4:48:38 PM 0.172 Open connection: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw;" Complete
7/15/2011 4:48:38 PM 0.000 Connect: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
7/15/2011 4:48:38 PM 0.016 Execute: SELECT version() Complete
7/15/2011 4:48:38 PM 0.000 Execute: show integer_datetimes Complete
7/15/2011 4:48:38 PM 0.000 Execute: SET autocommit=true Complete
7/15/2011 4:48:38 PM 0.000 Execute: SET datestyle=ISO Complete
7/15/2011 4:48:38 PM 0.016 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
7/15/2011 4:48:38 PM 0.000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
7/15/2011 4:48:38 PM 0.016 Execute: BEGIN Complete
7/15/2011 4:48:38 PM 0.016 Prepare: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
7/15/2011 4:48:38 PM 0.000 Execute: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
7/15/2011 4:48:38 PM 0.062 Open connection: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;" Complete
7/15/2011 4:48:38 PM 0.000 Connect: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
7/15/2011 4:48:38 PM 0.000 Execute: SELECT version() Complete
7/15/2011 4:48:38 PM 0.000 Execute: show integer_datetimes Complete
7/15/2011 4:48:38 PM 0.000 Execute: SET autocommit=true Complete
7/15/2011 4:48:38 PM 0.000 Execute: SET datestyle=ISO Complete
7/15/2011 4:48:38 PM 0.000 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
7/15/2011 4:48:38 PM 0.000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
7/15/2011 4:48:38 PM 0.000 Execute: BEGIN Complete
7/15/2011 4:48:38 PM 0.000 Prepare: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id Complete
7/15/2011 4:48:38 PM 0.063 Execute: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id Error