Raul wrote:

On 3/31/2015 12:48 PM, Barry wrote:

Barry,

> I just don't understand *why* a Restricted Transaction (a transaction on one or two tables), should prevent a new table from being created. It is not like the new table is part of the existing transaction. Perhaps Roy is correct when he says the Create Table requires a database lock and the current transaction prevents that from happening. If Roy is correct it also means any user who wants to create a table cannot do so when any transaction is in effect.

There likely are tech reasons - Tim would have to address.

However I think there are number of ways one can think about this.


>In my book this is a side effect of the transaction (i.e. this behavior
might change in the engine without Tim telling us).<

That's true.

>Have you tried using the BeginCachedUpdates/ApplyCachedUpdates and see
if this makes a difference.<

I never used them before, but I will definitely give them a try. It is probably what I should have been using in the first place, instead of transactions.

>I believe you're using SQL for this or are you using table object and
delphi code ? I've found delphi navigational operations to be faster in
some cases over SQL operations.<

Yes, I'm using TEDBTable for the Sync operation.

>Exclusive access should definitely help as well if you can use it.<
I've found in the past exclusive access improves the speed a bit, but not as much as having transactions which keeps the updated rows in memory. But I will give it a try. (Maybe this is a testament to EDB's fast locking?)

Thanks

Barry

Raul wrote:

On 4/1/2015 3:47 AM, Roy Lambert wrote:
Roy,

>> I would take the opposite view, unless the table is created within the transaction which is what I think you mean.<<

>The scenario Barry described was "i start a transaction with tables A
and B (meaning restricted transaction) and then inside the transaction i
also create table C" - the logical question is whether C should
automatically become part of the transaction. I think it should since
there is no other way to add it to the transaction.<

I would like to create the table "C"  OUTSIDE of the restricted transaction. I don't want the transaction to have any effect on the created table whatsoever. So if the transaction for Tables A & B rollback, the table that was created will remain.

>> Which would involve dropping the table. I can see good and bad points to this.<<

>Assuming transaction supports (some) DDL statements i think it should
behave normally - rollback will drop table and triggers and whatever
else was created. I think this gives most flexibility - alternative is
to not create them inside transaction so we have both options available.<

>Obviously until EDB supports this it's all academic though other DBMS
are adding this capability also (some do already as well and some do a
auto commit when reaching DDL statement).<

Yes, it would be nice if EDB handled DDL statements inside of a transaction, but is not needed in my particular situation.

Barry

Roy,

>Hmm, see the quote from Barry's original post. This is why I say I can see good and bad points. If you run a transaction which happens to create a table and another process is monitoring that and starts doing something when the table is created but it then suddenly vanishes - woops.<

Correct.
In that type of scenario where the created table "C" is inside the transaction, the other user would only see the newly created table "C" *after* the transaction was committed. But if table "C" is outside the transaction, which is what I want, then everyone sees table "C" as soon as it is created and the transaction does not roll it back.

>My personal view is that the only tables that should be allowed to be crated inside of a transaction are TEMPORARY tables, and I think it should be automatic that they vanish when the transaction is commited or rolled back. That way there can be no confusion with them.<

Did you really mean to say "vanish the temporary table when the transaction is committed"? After a commit why wouldn't the temporary table remain until the session expired?

So how do you create a permanent table when someone is running a transaction (restricted or otherwise), either in your application or another application? Wait for all transactions to finish before a table can be created? It could be a long wait. It appears to me having any transaction running in any application will prevent most DDL commands from executing even though the DDL commands are operating on tables outside of any of the current transactions. This I think is a severe restriction. Don't you?

Barry

Barry


>Did you really mean to say "vanish the temporary table when the transaction is committed"? After a commit why wouldn't the temporary table remain until the session expired?

Yup, that's what I mean. I can see no reason for creating tables within a transaction apart from one for temporary use within the transaction so get rid as soon as possible. I do understand Raul's comments about restructuring the database but it seems almost like an excuse to not do a backup first (OK with massive ones you will have difficulty so it may be valid there)

>So how do you create a permanent table when someone is running a transaction (restricted or otherwise), either in your application or another application? Wait for all transactions to finish before a table can be created? It could be a long wait. It appears to me having any transaction running in any application will prevent most DDL commands from executing even though the DDL commands are operating on tables outside of any of the current transactions. This I think is a severe restriction. Don't you?

Thinking of an alternative approach what about dumping the data into an in-memory table and then using that to update the target inside of a transaction - should run faster than normal table edit, alter, post

The other alternative is to offload the sync operation to a thread.

Roy Lambert

Raul wrote:

>Have you tried using the BeginCachedUpdates/ApplyCachedUpdates and see
if this makes a difference.<

I tried using Cached Updates and I thought I'd report my findings.

There are two drawbacks.

1) BeginCachedUpdates creates a copy of the entire (ranged) table to a temporary table. I was hoping it would only cache just the updates to the table, and not all the rows of the original table. Since I am syncing large tables (1 million rows), this will slow down the syncing process quite a bit. I can see using Cached Updates being useful for a few dozen rows, but not for large tables. Maybe it should be called "CachedTables" and not "CachedUpdates"?

2) It appears the cached temporary table does not have the Default value definition of the original table. I have a BD_Id column that whose DEFAULT is Current_GUID() and this does not get executed in when a new row is added to the cached temporary table. I'm using EDB v2.13B2 so it may be fixed in a later version of EDB.

It looks like I'm back to using transactions to buffer the updates in memory.

Barry