Begin/Commit Transaction

[ConvertFromOldNGs]

by Richard Smith >> Tue, 1 Jun 1999 0:44:37 GMT

Something we discovered recently is the large overhead in doing abegin /commit transaction. The following method illustrates quite clearly.

test();

vars
dat : RJSData; // a persistent with one attribute (id : Integer)
i : Integer;
begin


i := 1;

beginTransaction;

while i <= 1000 do
i := i + 1;
// beginTransaction;
create dat;
dat.id := i;
// commitTransaction;
endwhile;

commitTransaction;

end;


With the begin/commit inside the while loop this took about 20-25 seconds to execute and resulted in 4000-4500k being written to the logfile.

Moving the begin/commit to outside the while loop reduced the time to 1 second and only 200-250k was written to the logfile.

The main point to consider with transactions is "What should be undone if the application fails?". This would effect if you ideally want thebegin /commit inside or outside the loop but the above test shows that there
is a large overhead if this is within the loop.

[ConvertFromOldNGs]

by Dean Cooper >> Tue, 1 Jun 1999 2:09:19 GMT

Something we discovered recently is the large overhead in doing a begin/commit transaction. The following method illustrates quite clearly.

The "large overhead" is incurred only if you begin/commit transactions more often that you need to. This is a transaction processing/application design issue; not a Jade issue. With *any* database that implements transactions, there is a cost associated with commiting a transaction. In Jade, a commitTransaction must ensure that all objects modified in the transaction are resident on the server and then written to the log file. The write to the log file is forced (for database integrity and recovery purposes, we must guarantee the transaction has been written to the log on disk) and synchronous (the process cannot continue until the write has completed). Obviously, whenever you introduce disk IO, there is a performance cost. Other transaction processing systems have exactly the same issue.

The main point to consider with transactions is "What should be undone if the application fails?".

You need to consider what operations to "package" in a transaction. Affecting this decision will be recovery granularity (ie: how many operations do you mind losing if the transaction fails?) and performance. It would be very unusual to have a highly-used routine that updates many objects (such as the example of 1000 creates) commit after every update; if only because of the performance cost.

Dean.

[ConvertFromOldNGs]

by Richard Mitchell >> Tue, 1 Jun 1999 2:14:44 GMT

It is generally bad practice to code multiple begin/commitTransaction pairs in a single method execution (e.g. in a buttonOK method or a TcpIpConnection::readNotify), partly for the reason that you have discovered, but more importantly for data integrity.

Let's say you have coded multiple BT/CT pairs, and the second or subsequent transaction encounters an exception. If your exception handler does an abortTransaction, it only aborts the current uncommitted transaction. This leaves one or more committed transactions in the database. These committed transactions COULD have a dependency on the data that was aborted.

Generally, you should make stringent efforts to commit an atomic BT/CT pair that commits all updates for a particular "macro-transaction" (e.g. bOK_click) in one database transaction. This is also the most efficient way to write your code !

Cheers ......... Richard

[ConvertFromOldNGs]

by Richard Smith >> Wed, 2 Jun 1999 2:23:30 GMT

My initial comment was not intended as a critisism of Jade but rather as an example of a coding issue that can cause larger than necessary logfiles and add additional processing time.

We had a situation where the begin/commit were not being used for data integrity (as this was not critical) but were placed around the creation/updating/deletion of persistent objects (of which there were a large number). The developer had chosen to put the begin/commit transaction as close as possible to the actual create/update/delete. This caused the large amount of logging, which was not noticed in development but became very obvious in test when the transaction volume went up. The problem was eventually traced with the help of Jade Support who provided analaysis of the database log file. This was able to quickly highlight the area of code to look at.


I agree entirely with Dean and Richard M. that the begin/commit should be around a macro-transaction to ensure data integrity.

[ConvertFromOldNGs]

by Hayden >> Thu, 3 Jun 1999 13:04:20 GMT

I too discovered this obvious (and quite normal, and fully accepted) overhead when writing conversion scripts from the legacy system.

I started with the Begin/Commit around the while loop (for 145,000 customers), and that wasn't good. I then threw in a BT/CT for each repetition - again, slow.

My solution was to commit every n loops - like this:
begin
Transaction;
while blah blah blah do
i := i + 1;

yadda yadda yadda create obj save info

if i mod 250 = 0 then
commitTransaction;
beginTransaction;
endif;
endwhile;
commitTransaction;

The value to mod depends on the size of the impending object, but this has proved to be a pretty good method for ensuring optimal time for a mass import of data.

Hayden McInnes
Focus Business Solutions
http://www.focussoft.co.nz

[ConvertFromOldNGs]

by Craig Shearer >> Fri, 4 Jun 1999 2:02:49 GMT