How to handle if connection goes down during transaction?

[m_dirk]

Hello,

I'm wondering what is the best practice when it comes to performing a transaction when you've got a chance the connection will fail?

Consider the following example:

Code: Select all

' This example is taken from the pgsqlnet.chm file

Public Sub RunPgSqlTransaction(ByVal myConnString As String)

  Dim pgConnection As New PgSqlConnection(myConnString)
  pgConnection.Open()
  
  Dim pgCommand As New PgSqlCommand
  ' I need to explicitly set the connection property of the pgCommand object, otherwise, 
  ' the pgCommand.ExecuteNonQuery will throw a "ConnectionNotInit" exception.
  ' The connection property is not set in the example.
  pgCommand.Connection = pgConnection

  Dim myTrans As PgSqlTransaction

  ' Start a local transaction
  myTrans = pgConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
  ' Assign transaction object for a pending local transaction
  pgCommand.Transaction = myTrans
  pgCommand.Connection = pgConnection
  Try
    pgCommand.CommandText = 
    pgCommand.ExecuteNonQuery()

    ' For testing purposes I've put a Messagebox.Show here which will suspend the thread.
    ' When this box shows, I will disable the WIFI connection, simulating a broken connection.
    ' At this point, the first row will be locked.
    Messagebox.Show("Close down WIFI connection...")

    ' Now the connection is down, the next statements will throw an exception.
    ' But how can one perform a rollback, if there isn't a connection?
    ' Should I try to re-establish the connection in the catch block?
    ' Or should I use a different approach?

    pgCommand.CommandText = 
    pgCommand.ExecuteNonQuery()
    myTrans.Commit()
    Console.WriteLine("Both records are written to database.")
  Catch e As Exception
    ' Try to re-establish connection here?
    myTrans.Rollback()
    Console.WriteLine(e.ToString())
    Console.WriteLine("Neither record was written to database.")
  Finally
    pgConnection.Close()
  End Try
End Sub

I've put my ideas in some comments in the code.
Since I do not know a way to execute the update and insert in one query other than a transaction, I'm kinda stuck here.
Any useful suggestions would be greatly appreciated.

[Alexey]

If you physically drop the connection, then calling RollBack() method will raise an exception as well. So you should modify your Catch clause like this:

Code: Select all

        Catch e As PgSqlException
            ' Try to re-establish connection here? 
            If IsFatalError(e) Then
                Console.WriteLine(e.ToString())
                Console.WriteLine("Connection is dead.")
            ElseIf Not IsFatalError(e) Then
                myTrans.Rollback()
                Console.WriteLine(e.ToString())
                Console.WriteLine("Neither record was written to database.")
            End If

Code: Select all

    Const ConnectionFailureCode As String = "08006"

    Shared Function IsFatalError(ByVal exception As PgSqlException) As Boolean
        If exception.ErrorCode = ConnectionFailureCode Then
            Return True
        End If
        Return False
    End Function

[m_dirk]

If you physically drop the connection, then calling RollBack() method will raise an exception as well. So you should modify your Catch clause like this (...)

Thanks for the code, however your solution leaves me with a locked row. Can it be unlocked from our code? Or even better, can the lock be prevented?

[Alexey]

If connection locked anything and then closed, locked rows should have been released. This is PostgreSQL server's responsibility.

[m_dirk]

If connection locked anything and then closed, locked rows should have been released. This is PostgreSQL server's responsibility.

I was not aware of this rather crucial point. Thank you for your help Alexey!

[Alexey]

Have you resolved this problem?

[m_dirk]

Have you resolved this problem?

Not yet, some other projects have my attention and I need to look up the Postgres documentation to check its configuration, because our server does not release the lock when the transaction fails.

[Alexey]

OK, please let me know your progress on this issue.