Обсуждение: Recovering from failed transaction
I feel dumb asking this question, but I can't seem to find the answer online. I'm running serializable transactions, and so naturally, they will sometimes fail with the error "could not serialize access due to concurrent update." But then I try to issue a ROLLBACK so I can continue using the connection, and I get error 25P02: "current transaction is aborted, commands ignored until end of transaction block." ...doesn't "ROLLBACK" end a transaction block? What does Postgres want here? How can I retry without closing the connection altogether? --Brian
On Mon, Mar 10, 2014 at 1:41 PM, Brian Crowell <brian@fluggo.com> wrote:
I feel dumb asking this question, but I can't seem to find the answer online.
I'm running serializable transactions, and so naturally, they will
sometimes fail with the error "could not serialize access due to
concurrent update."
But then I try to issue a ROLLBACK so I can continue using the
connection, and I get error 25P02: "current transaction is aborted,
commands ignored until end of transaction block."
...doesn't "ROLLBACK" end a transaction block? What does Postgres want
here? How can I retry without closing the connection altogether?
What tool are you using to connect to the database? Perhaps it is confusing the error response to some earlier statement in the stream with the response for the rollback. Or maybe it is mangling the "ROLLBACK;" into some form the database doesn't recognize. Look in the postgres log files to see what the events look like from PostgreSQL's perspective.
Cheers,
Jeff
On Mon, Mar 10, 2014 at 4:16 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > ... Or maybe it is mangling the "ROLLBACK;" into some form > the database doesn't recognize. Look in the postgres log files to see what > the events look like from PostgreSQL's perspective. Well that's the clue I needed. I was misinterpreting Postgres's log file; it was complaining about the "SET statement_timeout" statement Npgsql was slipping ahead of my ROLLBACK. Apparently I need to do transactions with Npgsql's transaction class. --Brian
Hi Brian!
I got a patch to fix this. Unfortunately, I'm having some problems with github at this moment and I couldn't push it to create a pull request.
Would you mind to patch a local copy of Npgsql code and give it a try?
Here is the patch:
diff --git a/Npgsql/Npgsql/NpgsqlConnector.cs b/Npgsql/Npgsql/NpgsqlConnector.cs
index eb7da15..5e090be 100644
--- a/Npgsql/Npgsql/NpgsqlConnector.cs
+++ b/Npgsql/Npgsql/NpgsqlConnector.cs
@@ -427,7 +427,7 @@ internal void ReleaseResources()
internal void ReleaseWithDiscard()
{
- NpgsqlCommand.ExecuteBlind(this, NpgsqlQuery.DiscardAll, 60);
+ NpgsqlCommand.ExecuteBlind(this, NpgsqlQuery.DiscardAll);
// The initial connection parameters will be restored via IsValid()
}
@@ -828,7 +828,7 @@ internal void Open()
initQueries = sbInitQueries.ToString();
- NpgsqlCommand.ExecuteBlind(this, initQueries, 60);
+ NpgsqlCommand.ExecuteBlind(this, initQueries);
// Make a shallow copy of the type mapping that the connector will
// It is possible that the connector may add types to its privateY
Thanks in advance and sorry for this problem.
On Mon, Mar 10, 2014 at 6:49 PM, Brian Crowell <brian@fluggo.com> wrote:
On Mon, Mar 10, 2014 at 4:16 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> ... Or maybe it is mangling the "ROLLBACK;" into some form> the database doesn't recognize. Look in the postgres log files to see whatWell that's the clue I needed. I was misinterpreting Postgres's log
> the events look like from PostgreSQL's perspective.
file; it was complaining about the "SET statement_timeout" statement
Npgsql was slipping ahead of my ROLLBACK. Apparently I need to do
transactions with Npgsql's transaction class.
--Brian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Regards,
Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior