How to handle if connection goes down during transaction?

How to handle if connection goes down during transaction?

Postby m_dirk » Fri 16 Mar 2007 10:16

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.
m_dirk
 
Posts: 9
Joined: Fri 24 Nov 2006 09:25

Postby Alexey » Fri 16 Mar 2007 14:57

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
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby m_dirk » Fri 16 Mar 2007 15:35

Alexey wrote: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?
m_dirk
 
Posts: 9
Joined: Fri 24 Nov 2006 09:25

Postby Alexey » Fri 16 Mar 2007 15:53

If connection locked anything and then closed, locked rows should have been released. This is PostgreSQL server's responsibility.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby m_dirk » Mon 19 Mar 2007 08:51

Alexey wrote: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!
m_dirk
 
Posts: 9
Joined: Fri 24 Nov 2006 09:25

Postby Alexey » Tue 20 Mar 2007 07:07

Have you resolved this problem?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby m_dirk » Tue 20 Mar 2007 08:43

Alexey wrote: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.
m_dirk
 
Posts: 9
Joined: Fri 24 Nov 2006 09:25

Postby Alexey » Tue 20 Mar 2007 08:58

OK, please let me know your progress on this issue.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL