Recent

Author Topic: What is the purpose of SQLTransaction.Action  (Read 523 times)

mirce.vladimirov

  • Full Member
  • ***
  • Posts: 233
What is the purpose of SQLTransaction.Action
« on: April 28, 2021, 12:31:37 pm »
I'm using Lazarus for 10 years now, my current version is 1.4.4 and so far I've used mostly MySQL/MariaDB, my usual code to connect to a database server looks like :
Code: Pascal  [Select][+][-]
  1.   MySQL50Connection1.Connected:=false;
  2.   MySQL50Connection1.HostName:=myipaddress;
  3.   MySQL50Connection1.UserName:=myusername;
  4.   MySQL50Connection1.Password:=mypassword;
  5.   MySQL50Connection1.Database:=mydatabase;
  6.   MySQL50Connection1.Connected:=true;
  7.  

and the code to insert a recod would be:
Code: Pascal  [Select][+][-]
  1.   sqlquery1.close;
  2.   sqlquery1.sql.clear;
  3.   sqlquery1.sql.add('insert into mytable (field1, field2) values ("abc", "defg") ');
  4.   sqlquery1.execsql;
  5.  
  6.   SQLTransaction1.CommitRetaining;
  7.  

In the ObjectInspector, under the SQLTransaction's  properties I can see a property "Action" and possible values are : caCommit, caCommitRetaining, caRollback, CaRollbackRetaining, caNone.
So far I have never pay any anytention on this and always left it to its defaults. 

What can it be used for?
Is there documetation on this? I've done some search but could not find anything.
« Last Edit: April 28, 2021, 12:35:49 pm by mirce.vladimirov »

PierceNg

  • Full Member
  • ***
  • Posts: 110
Re: What is the purpose of SQLTransaction.Action
« Reply #1 on: April 28, 2021, 01:13:24 pm »
Based on packages/fcl-db/src/sqldb.pp (starting line 2362 in FPC trunk), the value specifies the action to take when executing EndTransaction.

Code: Pascal  [Select][+][-]
  1. procedure TSQLTransaction.EndTransaction;
  2.  
  3. begin
  4.   Case Action of
  5.     caCommit, caCommitRetaining :
  6.       Commit;
  7.     caNone,
  8.     caRollback, caRollbackRetaining :
  9.       if not (stoUseImplicit in Options) then
  10.         RollBack
  11.       else
  12.         CloseTrans;
  13.   end;
  14. end;

tonyw

  • Full Member
  • ***
  • Posts: 198
    • MWA Software
Re: What is the purpose of SQLTransaction.Action
« Reply #2 on: April 28, 2021, 04:09:02 pm »
MySQL has traditionally been rather weak at transaction control. It has got better over the years, but I am still unsure as to how strongly transactions are policed in MySQL.

A DBMS with strong transaction control, such as Firebird or Oracle demands that all database SQL actions (Select/update/Insert/Delete) take place within a transaction. Transactions are isolated from each other with various options. At one extreme each transaction sees a separate snapshot of a database that is created when the transaction starts and is only merged back into the main database when the transaction is "Commited" (the commit action). Alternatively, if you rollback a transaction, your snapshot is discarded and the updates are lost. Database locks can be used to stop transactions making conflicting changes.

You can have other variations such as ReadCommitted transactions which can "see" committed updates from other transactions. A single application can have more than one transaction open at any one time, each looking at a different snapshot of the database.

Variations such as "CommitRetaining" refer to what happens after you commit a transaction. Commit and Rollback termainte a transaction and you have to start a new transaction in order to continue. The "Retaining" variants Commit or Rollback the changes while leaving the transaction open and it continues to be available for use.

You may also come across "2 phase commits" which allow for safe transaction co-ordination across multiple databases.

 

TinyPortal © 2005-2018