MySQL Rollback

MySQL Rollback

Postby 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
markdrury
 
Posts: 2
Joined: Mon 19 Jan 2009 12:02

Re: MySQL Rollback

Postby 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/revision_history.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.
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL