Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 18 total
Thread Database.AutoTransactions := True observation
Thu, Nov 3 2016 4:45 PMPermanent Link

Trinione

==========================================================
Note from Tim at Elevate Software: This post is so grossly incorrect that I'm putting this notice here to warn everyone to ignore its contents.  It is 100% supposition and is completely incorrect.  Please see my reply further down for more information.
==========================================================

Just a heads up for anyone else who may be wondering why records may be disappearing on them.

The Database.AutoTransactions setting is True by default, however I have found this to lead to undesierable results in the standard EWB/EDB/EWB Server setup. Those who use say RemObjects or Delphi modules won't have this issue I would think.

That setting with a long list of Deletes, Update, Insert results in a situation where even after using asyn(xx) to update a dataset, the process appears 'finished' in the browser interface. But, looking at the browser debug area it is still working away. I discovered this when I logged out and restarted an app and many records were completely out of whack. For that matter the EDB table lost it's index! Rather scary!!!

So, I am reverting to the not using AutoTransactions=True. Doing it 'manually' completes in less than 1 second, as compared to the over 60 seconds.

Disclaimer:
I may be not doing something right of course. SmileBut, the fact that it works fine quickly the 'manual' way as it did prior gives me comfort.
Thu, Nov 3 2016 8:40 PMPermanent Link

Raul

Globestar Systems

Team Elevate Team Elevate

On 11/3/2016 4:45 PM, Trinione wrote:
> That setting with a long list of Deletes, Update, Insert results in a situation where even after using asyn(xx) to update a dataset, the process appears 'finished' in the browser interface. But, looking at the browser debug area it is still working away. I discovered this when I logged out and restarted an app and many records were completely out of whack. For that matter the EDB table lost it's index! Rather scary!!!

How are you trigerring the long list of changes ? (In code or user
manually doing edits)

The only thing that AutoTransactions does is cause automatic
transactions for every row insert/update. Assuming there is no
transaction in progress prior you now end up with single change being
sent to server each time.

Normally in user driven apps this might not be a big deal but if you do
dataset edits in code - especially something that for example loops thru
records and then updates 1 field of every record or insert new record
you can quickly queue up a whole bunch of these transactions and even
with LAN connection there is some latency going on.

Your options are to :

(1) disable AutoTransactions and manage it yourself so you ensure
changes get sent in a single batch

(2) if you know you have number of changes happening start transaction
manually and commit at the end manually again.

AutoTransactions being true this still results in new auto transactions
but now they are nested and thus don't get sent to server on commit but
rather bundled together by EWB until final manual commit when
transaction level  reaches 0 and everything queued up is sent as single
batch.

The EDB table losing index is definitely weird and something Tim might
want to take a look but considering there is EWB Web server in between i
would suspect that was caused by something else and not actual
insert/edit activity.

Raul
Fri, Nov 4 2016 5:25 AMPermanent Link

Trinione

Raul wrote:
<< How are you trigerring the long list of changes ? (In code or user  manually doing edits) >>

One of the cases is say a data grid with a checkbox. Hitting a checkbox 'All' that sets the values of all rows checkbox to selected or unselected for example. Another example is removing all records from a dataset.


<< The only thing that AutoTransactions does is cause automatic transactions for every row insert/update.>>
<< Assuming there is no  transaction in progress prior you now end up with single change being
sent to server each time. >>

Exactly. And, this is the issue in a case where multiple records need processing in a loop.

while not ds.Eof do      // ds contains 300 records
 Delete;


<< Your options are to :
(1) disable AutoTransactions and manage it yourself so you ensure
changes get sent in a single batch
(2) if you know you have number of changes happening start transaction
manually and commit at the end manually again. >>

Yes. I have reverted my code to this.


<< AutoTransactions being true this still results in new auto transactions but now they are nested and thus don't get sent to server on commit but  rather bundled together by EWB until final manual commit when  transaction level  reaches 0 and everything queued up is sent as single batch. >>

Are you saying that I need to issue a Database.Commit statement with AutoTransaction=True? I will try that and compare. Doing it manually just feels safer to me.


<< The EDB table losing index is definitely weird and something Tim might  want to take a look but considering there is EWB Web server in between i would suspect that was caused by something else and not actual
insert/edit activity.>>

It definitely was the activity. Nothing else. The table showed the index in the 'Indexes' area, but not the 'Constratints' tab. The backup showed it was indeed done prior (else it would not have worked).
Fri, Nov 4 2016 5:41 AMPermanent Link

Trinione

<< AutoTransactions being true this still results in new auto transactions but now they are nested and thus don't get sent to server on commit but  rather bundled together by EWB until final manual commit when  transaction level  reaches 0 and everything queued up is sent as single batch. >>

<< Are you saying that I need to issue a Database.Commit statement with AutoTransaction=True? >>

Nope. As I thought. That's not the case.
Fri, Nov 4 2016 6:52 AMPermanent Link

Walter Matte

Tactical Business Corporation

Surely if you bring in a dataset into EWB with AutoTransaction = True and then go mass updating them in a loop to set a check box, then I would rethink what I was doing.

Now, with a better understanding of what AutoTransaction means and use it accordingly - you will have a tremendous amount of work done on your behalf by EWB.

What is your backend server?

Walter
Fri, Nov 4 2016 7:09 AMPermanent Link

Walter Matte

Tactical Business Corporation

Trinione wrote:

"even after using asyn(xx) to update a dataset"

Someone else can jump in here - but async does not wait for a trip back to the server to update a dataset and get a response back before continuing to the next statement in your EWB program.

Walter  
Fri, Nov 4 2016 7:33 AMPermanent Link

Raul

Globestar Systems

Team Elevate Team Elevate

On 11/4/2016 5:41 AM, Trinione wrote:
> << Are you saying that I need to issue a Database.Commit statement with AutoTransaction=True? >>
> Nope. As I thought. That's not the case.

No - i should have been more specific by saying you CAN issue it
manually still. This woudl allow keeping AutoTransaction=True and use
this feature but manually start/commit transactions in sections of code
that you know can result in many updates.

Option 1: You can go total manual transaction route where you manage it
and you set AutoTransaction=False. This way every dataset
edit/insert/delete you have to ensure you handle with a transaction.

Option 2: You can keep AutoTransaction=True and do something like this
by using nested transactions :

//starts (outer transaction)
Database.StartTransaction;   

//results in transaction each time but is nested so
//nothing i sent to server since outer transaction active

while not ds.Eof do
  Delete;          

//this commit outer transaction - data sent to Server
Database.Commit;

Just offering alternatives.

Raul
Fri, Nov 4 2016 9:06 AMPermanent Link

Trinione

Walter Matte wrote:
<< Someone else can jump in here - but async does not wait for a trip back to the server to update a dataset and get a response back before continuing to the next statement in your EWB program. >>

Correct.

I use that approach to provide a progress indicator thru the workflow. Considering how browsers work versus apps, this has been one area that is challenging to provide a good user experience.
Fri, Nov 4 2016 9:10 AMPermanent Link

Trinione

Raul:
Thanks. And, I reverted to the manual approach throughout. Just easier to manage all around for this particular app. Other apps may have an advantage with the setting to True.

I really wanted to alert other users of this data losing potential if the browser tab/window is closed or the app reloaded in it whilst the browser appears as if the job is done, but it is actually still running. As I said, this most definitely caused my database to get corrupted when it lost the primary index in the table and I had to recreate it.
Thu, Nov 10 2016 2:21 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

<< I really wanted to alert other users of this data losing potential if the browser tab/window is closed or the app reloaded in it whilst the browser appears as if the job is done, but it is actually still running. As I said, this most definitely caused my database to get corrupted when it lost the primary index in the table and I had to recreate it. >>

No, no, a thousand times, no.  There is no way that *anything* EWB does will corrupt an EDB database table or cause it "lose an index".  Please do not post such statements without knowing the full details of what you're discussing.

Here are the facts:

1) Dataset updates happen synchronously in real-time in the EWB client.  The resulting *transactions* that may occur if AutoTransactions=True will happen *asynchronously*.  However, they will still occur *in the order in which they were initiated* because EWB queues up such transactions in the order in which they occur.  They are then fed to the back-end web server, one-at-a-time.  This is just how browsers work, although this will get better with the newer JS standards that will allow EWB to wait for commits to occur.

2) If you update 300 rows in a grid while a) the grid is still enabled to receive notifications/updates and b) you have AutoTransactions:=True, then yes, it's going to be very slow.  You're causing the grid to update its cells *and* causing an entirely separate transaction to occur with every single row update.  The network latency alone means that you're looking at *at least* 6-10 seconds for the 300 separate transactions.

3) The only way to corrupt an EDB database is to kill the process or the machine while Windows is in the middle of writing data to disk (with the latter, killing the machine, being the worst situation).  However, there is absolutely no way whatsoever that killing the process or machine will cause a primary key to disappear when you haven't performed any DDL statements on the table recently.  If you haven't performed any DDL statements and updated the database catalog, then there's no way that the database catalog could become corrupted.  Furthermore, database catalog updates in EDB are protected with a special write sequence that first makes a backup of the database catalog (with a forced flush to disk after the copy), the new database catalog is written (again, with a forced flush to disk), and then the backup of the catalog (edbdatabase.edbcat.old) is left as a safety measure. If *any* part of this process experiences an error, then the catalog is restored to the backup.  So, unless you killed the process or machine in the middle of such a catalog update, then something else is going on, such as you copied the wrong database catalog to the correct location, or the correct database catalog to the wrong location, etc.

Tim Young
Elevate Software
www.elevatesoft.com
Page 1 of 2Next Page
Jump to Page:  1 2
Image