user3181689
user3181689

Reputation: 241

use execSQL or COMMIT to conclude a transaction

What would be the right way to conclude a transaction ? :

Query.SQL.Add('START TRANSACTION;');
-----
-----
Query.SQL.Add('COMMIT;');
Query.ExecSQL;

or

Query.SQL.Add('START TRANSACTION;');
-----
-----
Query.ExecSQL; 
Query.SQL.Add('COMMIT;');

edit : Or this way :

.......
.......
try
if not Database1.InTransaction then
Database1.StartTransaction;
Query.ExecSQL;
Database1.Commit;
except  on E : Exception do begin
Database1.Rollback;

Upvotes: 0

Views: 2786

Answers (1)

Gianluca Colombo
Gianluca Colombo

Reputation: 829

Normally you have to control the transaction under try except to prevent errors from insert or update statements.. so I suggest to use start transaction on tConnection and not on tquery under try except

try
  AdoConnection1.BeginTrans;
  ----
  ----
  query1.ExecSql;
  AdoConnection1.CommitTrans;
except
  AdoConnection1.RollbackTrans;
  ----- 
  // manage the exception after rollback
  ----- 
end;

Note: It's very important to place RollbackTrans at the first line in the exception management, because any other code as showmessage or anything else could lock a part of database tables for other users

Upvotes: 7

Related Questions