How to correctly use TMyConnection.StartTransaction and TMyConnection.Rollback to rollback a MySQL transaction

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
docH
Posts: 59
Joined: Sun 22 Dec 2013 15:18

How to correctly use TMyConnection.StartTransaction and TMyConnection.Rollback to rollback a MySQL transaction

Post by docH » Wed 18 Apr 2018 20:41

I need to make exactly two inserts into a MySQL InnoDB table using a transaction.
If either the first or the second INSERT fails I want to rollback the whole transaction (and let the user know).
Using the code in the example 1 below the two inserts work OK.

However, if I run the code again but deliberately cause the first insert to fail by forcing a duplicate key error - see example 2 where I've changed the sql to 'INSERT INTO table1 (memberid, thedate, amount,subsID ) values (21, '2018-04-18', 50.5, 500); (where 500 is the subsID generated last time)
then the 'except' block is called and I get the duplicate key error message BUT the insert partly goes ahead initially and is not rolled back. Instead I get a new record inserted with the memberID in place and all other fields set to null.
(Although this is only visible in an open session in my DBMS. If I reconnect the DBMS then there is no partially inserted record there and the rollback seems to have happened)

Is my code the correct way to do this? if not, how should I correctly code this so that either both or neither of the inserts happens?
The PK of the table is a combination of memberid, thedate and an autoincrement field, subsID


Example 1;

Code: Select all

Procedure transfer; 
var 
  MyConnection1 : TMyConnection;
  sql : string;
begin
sql :=  'INSERT INTO table1 (memberid, thedate, amount) values (21, '2018-04-18', 50.5); '   
       +'INSERT INTO table1 (memberid,thedate, amount) values (35, '2018-04-18', -50.5); '  ;
try
   begin
   MyConnection1.StartTransaction;
   MyConnection1.ExecSQL(sql);
   MyConnection1.Commit;
   end
except
on E : Exception do
     begin
     MyConnection1.Rollback;
      showmessage ('Exception class name = '+E.ClassName+ slinebreak
                      +  'Exception message = '+E.Message);
      end  //on E
end;//try


Example 2;

Code: Select all

Procedure transfer2; 
var 
  MyConnection1 : TMyConnection;
  sql : string;
begin
sql := 'INSERT INTO table1 (memberid, thedate, amount, subsID) values (21, '2018-04-18', 50.5, 500); '   
      +'INSERT INTO table1 (memberid,thedate, amount) values (35, '2018-04-18', -50.5); '  ;
try
   begin
   MyConnection1.StartTransaction;
   MyConnection1.ExecSQL(sql);
   MyConnection1.Commit;
   end
except
on E : Exception do
     begin
     MyConnection1.Rollback;
      showmessage ('Exception class name = '+E.ClassName+ slinebreak
                      +  'Exception message = '+E.Message);
      end  //on E
end;//try

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to correctly use TMyConnection.StartTransaction and TMyConnection.Rollback to rollback a MySQL transaction

Post by ViktorV » Thu 19 Apr 2018 12:58

If, when reconnecting to DBMS, you see that the record is not inserted - Rollback worked successfully. You can call the TMyQuery.Refresh method to test successful execution of the Rollback method without reconnecting to DBMS.

docH
Posts: 59
Joined: Sun 22 Dec 2013 15:18

Re: How to correctly use TMyConnection.StartTransaction and TMyConnection.Rollback to rollback a MySQL transaction

Post by docH » Thu 19 Apr 2018 14:48

Ok, thank you, Does that mean that the code structure I am using (with try - except and rollback in the except is correct way to do it?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to correctly use TMyConnection.StartTransaction and TMyConnection.Rollback to rollback a MySQL transaction

Post by ViktorV » Thu 19 Apr 2018 15:04

Yes, you are using the correct code to solve your task. The only point is, execute the MyConnection1.StartTransaction method before try.

Post Reply