MySQL Rollback

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
markdrury
Posts: 2
Joined: Mon 19 Jan 2009 12:02

MySQL Rollback

Post by markdrury » Thu 05 Dec 2013 17:02

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
Devart Team
Posts: 2138
Joined: Wed 02 Nov 2011 09:44

Re: MySQL Rollback

Post by Pinturiccio » Mon 09 Dec 2013 15:53

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
markdrury wrote: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.

Post Reply