I don't understand why I should get the error:

"Error #404 Transaction error: This operation cannot be performed while the database MyDatabase has an active transaction (CREATE TABLE _NewTable)"

Yes I have a transaction on Table1 and Table2. But why should that prevent me from creating _NewTable? I don't want _NewTable to be in the transaction because I will never roll it back. I can understand I might get this error if I have a transaction on all the tables in the database, but why do I get this error when a transaction is on only 2 tables?

TIA

Barry
EDB 2.13B2

Barry


Wild guess because only Tim will know for sure. Its because CREATE TABLE requires a total database lock, possibly in case it has to do something with other tables, possibly just to make sure two people can't create tables simultaneously and stuff the catalog.

Roy Lambert

On 3/27/2015 12:04 PM, Barry wrote:
> I don't understand why I should get the error:
> "Error #404 Transaction error: This operation cannot be performed while the database MyDatabase has an active transaction (CREATE TABLE _NewTable)"
> Yes I have a transaction on Table1 and Table2. But why should that prevent me from creating _NewTable? I don't want _NewTable to be in the transaction because I will never roll it back. I can understand I might get this error if I have a transaction on all the tables in the database, but why do I get this error when a transaction is on only 2 tables?

Because there are operations that are incompatible with the transactions

(see Incompatible Operations section)

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Transactions

It's not just table create but many DDL statements.


Raul

Raul,

>Because there are operations that are incompatible with the transactions<

Yes, I did see that part in the manual where it said DDL statement were incompatible with transactions. 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.

This is fine when transactions are short, say under 1/2 second. But I'm using transactions to speed up batch processes where a lot of updates or inserts are being performed on one or two tables. Normally I commit the transaction after 5000 rows have been updated. When adding rows this happens rather quickly, less than 5 seconds. But when updating rows using a sync process, I only update rows that need updating so maybe only 1% of the rows will actually get updated.

Since I am using transactions only for speed and not rollback, I will likely drop transactions altogether and put up with the slower updates. Or commit the transaction every 1/2 second even if it has only a couple of updated rows in it. To increase performance I could try an exclusive lock on the table I am updating since no one else will be accessing it, but the lock will again likely prevent any DDL statements from being executed. I am syncing tables with 50,000 rows and 1 million rows so any speed improvement is welcomed.

Does anyone else have any suggestions?

Barry               

P.S. This is the 3rd time I am trying to post the reply because my computer hung during the last 2 attempts because of my USB drive keeps hanging. I feel like Bill Murray in Groundhog Day! Didn't I do this before?

Barry

>. If Roy is correct it also means any user who wants to create a table cannot do so when any transaction is in effect.

I could be totally wrong, but a second user not being able to create a table is easy enough to test for, or you could ask Tim

It may also depend on the type of table (TEMPORARY or real) or if its in a different database eh memory. Without spending time I don't know the answer.

>P.S. This is the 3rd time I am trying to post the reply because my computer hung during the last 2 attempts because of my USB drive keeps hanging. I feel like Bill Murray in Groundhog Day! Didn't I do this before?

Could be worse - you could be trying to post to the Embarcadero news groups.

Roy Lambert

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.

Since the table does not exist you cannot include it in restricted
transaction when it starts so logically i would expect any newly created
table to become part of the restricted transaction - otherwise one
should just create it prior and then start the transaction and exclude it).

Assuming transaction also allows rollback of such operation it would be
also do a cleanup properly this way (meaning table is part of transaction).

> This is fine when transactions are short, say under 1/2 second. But I'm using transactions to speed up batch processes where a lot of updates or inserts are being performed on one or two tables. Normally I commit the transaction after 5000 rows have been updated. When adding rows this happens rather quickly, less than 5 seconds. But when updating rows using a sync process, I only update rows that need updating so maybe only 1% of the rows will actually get updated.

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

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

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.

Exclusive access should definitely help as well if you can use it.

Raul

Raul


>Since the table does not exist you cannot include it in restricted
>transaction when it starts so logically i would expect any newly created
>table to become part of the restricted transaction - otherwise one
>should just create it prior and then start the transaction and exclude it).

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

>Assuming transaction also allows rollback of such operation it would be
>also do a cleanup properly this way (meaning table is part of transaction).

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

Roy Lambert

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.

> 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).

Raul

Raul


>> 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 must be missing a post because I didn't see that anywhere. However, the original post does contain <<I don't want _NewTable to be in the transaction because I will never roll it back>>

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

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.

>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).

Academic yes, but it doesn't hurt for Tim to have an idea of what will make the users happy / unhappy.

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.

Roy Lambert

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

Roy,

> I must be missing a post because I didn't see that anywhere. However, the original post does contain <<I don't want _NewTable to be in the transaction because I will never roll it back>>

It's in the same message - he has a restricted transaction on 2 tables
and then gets an error when creating a 3rd (_NewTable) inside the
transaction.

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

That's where you have the transaction isolation - until you commit
nobody else should "see" anything.

The core question is what does "create table" inside transaction mean?

If it's not allowed like today things are very clear. However if it is
allowed then it needs to be consistent and useful.


> Academic yes, but it doesn't hurt for Tim to have an idea of what will make the users happy / unhappy.

Sure but also you need to consider use cases and what does transaction
mean in SQL - not to mention this should be at least somewhat similar to
how other systems behave.

My point simply is that I see proper behavior to be that it becomes part
of the transaction (i.e. i want to roll it back automatically).


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

It is extremely useful to have transaction support for DDL - we use it
with another database system since all of our database upgrade logic
(i.e. alter table, add index, triggers etc) is now transactional.

Basically we can do versions upgrades in-place and if it fails DBMS
rolls it all back so customer DB is always either old version or new but
never in some intermediate state with objects created.

Raul