What is the purpose of SQLTransaction.Action

Forum > Databases

(1/1)

mirce.vladimirov:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- MySQL50Connection1.Connected:=false; MySQL50Connection1.HostName:=myipaddress; MySQL50Connection1.UserName:=myusername; MySQL50Connection1.Password:=mypassword; MySQL50Connection1.Database:=mydatabase; MySQL50Connection1.Connected:=true;
and the code to insert a recod would be:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- sqlquery1.close; sqlquery1.sql.clear; sqlquery1.sql.add('insert into mytable (field1, field2) values ("abc", "defg") '); sqlquery1.execsql; SQLTransaction1.CommitRetaining;
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.

PierceNg:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TSQLTransaction.EndTransaction; begin Case Action of caCommit, caCommitRetaining : Commit; caNone, caRollback, caRollbackRetaining : if not (stoUseImplicit in Options) then RollBack else CloseTrans; end;end;

tonyw:
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.

Navigation

[0] Message Index