Shalex:
The problem is not what it seems. This is partly because of the way the Devart library is reporting the error that occurs in Postgres.
To try to figure out what is happening, we prepared three unit tests. Each one creates an Entity Framework object context, then inserts a single row into one of our tables using the object context. It then calls context.SaveChanges().
In the first test, we used a TransactionScope to surround the insert. Here is the code for this test:
Code: Select all
[TestCategory( "PubSubBufferPostrgres" ), TestMethod, Timeout( 600000 )]
public void TestPostgresConcurrancy_TransactionScope() {
using ( CarSystemEntities context = new CarSystemEntities() )
using ( TransactionScope tran = new TransactionScope() ) {
CarSystem.List l = new CarSystem.List();
l.ListId = Guid.NewGuid();
l.ListName = "test";
l.ListTypeId = 1;
l.DomainId = 1;
context.AddToLists( l );
context.SaveChanges( System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave );
tran.Complete();
}
}
In the second, we created a DbTransaction using the EntityConnection in the context. Here's that code:
Code: Select all
[TestCategory( "PubSubBufferPostrgres" ), TestMethod, Timeout( 600000 )]
public void TestPostgresConcurrancy_DbTransaction() {
using ( CarSystemEntities context = new CarSystemEntities() ) {
if ( context.Connection.State != ConnectionState.Open ) {
context.Connection.Open();
}
using ( DbTransaction tran = context.Connection.BeginTransaction() ) {
CarSystem.List l = new CarSystem.List();
l.ListId = Guid.NewGuid();
l.ListName = "test";
l.ListTypeId = 1;
l.DomainId = 1;
context.AddToLists( l );
context.SaveChanges( System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave );
tran.Commit();
}
}
}
In the third, we created a PgSqlTransaction by referencing the StoreConnection in the EntityConnection. Here's that code:
Code: Select all
[TestCategory( "PubSubBufferPostrgres" ), TestMethod, Timeout( 600000 )]
public void TestPostgresConcurrancy_PgSqlTransaction() {
using ( CarSystemEntities context = new CarSystemEntities() ) {
PgSqlConnection connection = ( (EntityConnection) context.Connection ).StoreConnection as PgSqlConnection;
if ( connection.State != ConnectionState.Open ) {
connection.Open();
}
using ( PgSqlTransaction tran = connection.BeginTransaction() ) {
CarSystem.List l = new CarSystem.List();
l.ListId = Guid.NewGuid();
l.ListName = "test";
l.ListTypeId = 1;
l.DomainId = 1;
context.AddToLists( l );
context.SaveChanges( System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave );
tran.Commit();
}
}
}
We turned on verbose logging on one of our servers. This let us see that the issue in the TransactionScope test was caused by our not having prepared transactions turned on at the server. We then turned on prepared transactions and ran our unit tests again.
3/15/2012: Edits begin:
Here are the log entries from the first test:
Code: Select all
TransactionScope: NO ERROR, data is written
Information,3/14/2012 5:24:35 PM,PostgreSQL,0,None,LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Information,3/14/2012 5:24:35 PM,PostgreSQL,0,None,LOG: statement: BEGIN
Information,3/14/2012 5:24:35 PM,PostgreSQL,0,None,"LOG: statement: SET search_path TO ""CarSystem"";
Information,3/14/2012 5:24:36 PM,PostgreSQL,0,None,"LOG: execute PRSTMT1639816908436919862/PORTAL1639816908436919862: INSERT INTO ""CarSystem"".""Lists""(""ListId"", ""ListName"", ""ListTypeId"", ""DomainId"", ""Geofencing"", ""Salt"", ""WhiteListAlarmClassId"", ""CreatedDate"", i_active, ""ModifyDate"", ""LastUpdate"") VALUES ($1, $2, $3, $4, NULL, NULL, NULL, $5, $6, NULL, NULL)
ETAIL: parameters: $1 = '8fb84653-f72a-416d-b634-31e4d25c3dae', $2 = 'test', $3 = '1', $4 = '1', $5 = '0001-01-01 00:03:58-04:56:02', $6 = '1'
Information,3/14/2012 5:24:36 PM,PostgreSQL,0,None,LOG: statement: PREPARE TRANSACTION 'bbdf3449-e299-4252-9755-9def4ef355f7'
Information,3/14/2012 5:24:36 PM,PostgreSQL,0,None,LOG: statement: COMMIT PREPARED 'bbdf3449-e299-4252-9755-9def4ef355f7'
Information,3/14/2012 5:24:36 PM,PostgreSQL,0,None,LOG: statement: COMMIT
Warning,3/14/2012 5:24:36 PM,PostgreSQL,0,None,WARNING: there is no transaction in progress
As you can see, a transaction is started, the insert occurs without error, a prepared tranaction is then begun and committed, and the original transaction committed. Postgres issues a warning, and Devart does not throw an exception.
Here is the log from the DbTransaction test:
Code: Select all
DbTransaction: NO ERROR, data is written
Information,3/14/2012 6:07:02 PM,PostgreSQL,0,None,"LOG: statement: SET search_path TO ""CarSystem"";
Information,3/14/2012 6:07:02 PM,PostgreSQL,0,None,LOG: execute PRSTMT1195684527439466064/PORTAL1195684527439466064: BEGIN
Information,3/14/2012 6:07:02 PM,PostgreSQL,0,None,"LOG: execute PRSTMT1582115622439466376/PORTAL1582115622439466376: INSERT INTO ""CarSystem"".""Lists""(""ListId"", ""ListName"", ""ListTypeId"", ""DomainId"", ""Geofencing"", ""Salt"", ""WhiteListAlarmClassId"", ""CreatedDate"", i_active, ""ModifyDate"", ""LastUpdate"") VALUES ($1, $2, $3, $4, NULL, NULL, NULL, $5, $6, NULL, NULL)
DETAIL: parameters: $1 = '775ab696-2504-4e82-b4d5-2de6a4a983b0', $2 = 'test', $3 = '1', $4 = '1', $5 = '0001-01-01 00:03:58-04:56:02', $6 = '1'
Information,3/14/2012 6:07:02 PM,PostgreSQL,0,None,LOG: execute PRSTMT869502152439466407/PORTAL869502152439466407: COMMIT
Information,3/14/2012 6:07:02 PM,PostgreSQL,0,None,LOG: statement: ROLLBACK
Information,3/14/2012 6:07:02 PM,PostgreSQL,0,None,"LOG: could not receive data from client: No connection could be made because the target machine actively refused it.
As you can see, in this case a transaction is started, the insert is made, the transaction is committed, and then a ROLLBACK is issues. Postgres then issues the error message, "could not receive data from client: No connection could be made because the target machine actively refused it."
Finally, here is the log from the last test using PgSqlTransaction:
Code: Select all
PgSQLTransaction: Error on commit: Connection must be opened, data is written
Information,3/14/2012 5:29:43 PM,PostgreSQL,0,None,"LOG: statement: SET search_path TO ""CarSystem"";
Information,3/14/2012 5:29:43 PM,PostgreSQL,0,None,LOG: execute PRSTMT1172646091437226700/PORTAL1172646091437226700: BEGIN
Information,3/14/2012 5:29:43 PM,PostgreSQL,0,None,LOG: execute PRSTMT1191317911437226997/PORTAL1191317911437226997: BEGIN
Warning,3/14/2012 5:29:43 PM,PostgreSQL,0,None,WARNING: there is already a transaction in progress
Information,3/14/2012 5:29:43 PM,PostgreSQL,0,None,"LOG: execute PRSTMT1584267521437227387/PORTAL1584267521437227387: INSERT INTO ""CarSystem"".""Lists""(""ListId"", ""ListName"", ""ListTypeId"", ""DomainId"", ""Geofencing"", ""Salt"", ""WhiteListAlarmClassId"", ""CreatedDate"", i_active, ""ModifyDate"", ""LastUpdate"")
VALUES ($1, $2, $3, $4, NULL, NULL, NULL, $5, $6, NULL, NULL)
DETAIL: parameters: $1 = '521d753d-f719-4aca-960d-edec36abbb6f', $2 = 'test', $3 = '1', $4 = '1', $5 = '0001-01-01 00:03:58-04:56:02', $6 = '1'
Information,3/14/2012 5:29:43 PM,PostgreSQL,0,None,LOG: execute PRSTMT925051539437227418/PORTAL925051539437227418: COMMIT
Information,3/14/2012 5:29:43 PM,PostgreSQL,0,None,LOG: statement: ROLLBACK
In this case, a transaction is started. The insert is made, and then the transaction is committed. This is followed with a ROLLBACK. On the C# side, we get a PgSqlException with the message reading "Connection must be opened."
It appears that in all cases the data is written to the database.
In the past, we've seen the DbTransaction case return "Connection must be opened" errors, put we were using PgSqlTransactions in some code that accesses the Large Objects table using the PgSqlLargeObject class. We've since removed those inner transactions, but these tests don't exercise that code. We'll have to do more testing.
Edits end;
We then decided to rewrite our software so it would use the TransactionScope, as this seemed to be the only case that would work. There turned out to be another issue with our database that prevented this from working.
It turns out that we have three different tables that have INSERT OR UPDATE triggers on them. The triggers use the Postgres NOTIFY construct to signal another process. The error we get back from the Devart library indicates that the transaction was aborted, which I guess it was, but the error reported by Postgres reads:
Code: Select all
ERROR: cannot PREPARE a transaction that has executed LISTEN, UNLISTEN or NOTIFY STATEMENT: PREPARE TRANSACTION '9a171233-8ce3-46de-babc-87ee537d7f0f'
No where was this information to be found except in the event log after we turned on verbose logging on the server.
So our questions for you now are:
1. Is there any way to create a TransactionScope that doesn't use prepared transctions? We don't need prepared transactions, we just need a normal transaction that is going to work and not lose data.
2. Can you fix the error reporting so it's clearer that the problem is that prepared transactions are turned off in that particular case?
Incidentally, it doesn't matter what is in the entity model. The problem is related to transaction handling and not the model itself.
Tony