MySQL Rollback

[markdrury]

Hi.

This may seem a silly question....if we issue a roll back command and this fails for some reason would we potentially see part of the transaction leading up to when the error occurred, I'm not thinking a syntax error,more of loss of connection on a WiFi device.

I took your example from here :


Public Sub RunMySqlTransaction(myConnString As String)
Dim myConnection As New MySqlConnection(myConnString)
myConnection.Open()

Dim myCommand As New MySqlCommand()
myConnection.BeginTransaction()
Try
myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')"
myCommand.ExecuteNonQuery()
myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(60, 'PRODUCTION')"
myCommand.ExecuteNonQuery()
myConnection.Commit()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
myConnection.Rollback()
Console.WriteLine(e.ToString())
Console.WriteLine("Neither record was written to database.")
Finally
myConnection.Close()
End Try
End Sub

if the myConnection.Rollback() were to fail could that potentially leave one of those two rows in the DB ???

Thanks in advance

[Pinturiccio]

Previously dotConnect for MySQL had the following behavior: if the Rollback method was not called, the Commit method was called when closing a connection. This behavior was changed in the 8.1.45 version. Since the 8.1.45 version, if the Commit method was not called, the records will not be inserted to the database. For more information, please refer to http://www.devart.com/dotconnect/mysql/ ... story.html

if the myConnection.Rollback() were to fail could that potentially leave one of those two rows in the DB ???

If you are using dotConnect for MySQL 8.1.45 or newer, then the answer is "no". In earlier versions such behavior is possible.