I believe I understand the purpose of the table list parameter to the
StartTrans function, but I want to make sure...

If I pass a list of tables, then only those tables will be involved in
the transaction (as opposed to the whole database).  However, I cannot
start a second transaction using different tables until the first
transaction has completed with either a Rollback or a Commit.  Is this
correct?

And just to help me understand what I can and cannot do...

1. What is the select behavior for tables involved in the transaction
for other users (when a list of tables is passed)?  Can they also start
a transaction on the same table but different records?

2. Are there any restrictions on updating tables in the database not
involved in the transaction by other users when a transaction is being
processed?

3. Can I update a table not involved in the transaction during the
processing of the transaction (if I pass a list of tables)?

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

And to add to the list...

If I start a transaction and make some changes using SQL, are any
changes made with the FindKey, Update methods involved in the
transaction as well (as long as either the table is included in the list
for the StartTrans or I use the EmptyEDBStringsArray parameter)?

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon

I'm going to have a go at replying although I suspect my knowledge is about on a par with your own, so someone correcting both of us will be helpful.


>If I pass a list of tables, then only those tables will be involved in
>the transaction (as opposed to the whole database). However, I cannot
>start a second transaction using different tables until the first
>transaction has completed with either a Rollback or a Commit. Is this
>correct?

Not in the same piece of procedural code following the transaction - you have to wait for it to finish.

But yes in a separate thread, in response to an event, or as I suspect your question meant, in the body of the transaction itself. IE yes you can nest transactions.

>And just to help me understand what I can and cannot do...
>
>1. What is the select behavior for tables involved in the transaction
>for other users (when a list of tables is passed)? Can they also start
>a transaction on the same table but different records?

They can start it - but it will have to wait for the other transaction to finish before it starts. At it current stage of development ElevateDB locks the table not a record or set of records. Think in terms of setting Exclusive to True  (but just for editing) for the tables involved in the transaction and you're nearly there.

>2. Are there any restrictions on updating tables in the database not
>involved in the transaction by other users when a transaction is being
>processed?

Only ones you impose. I'm not sure what happens to any RI or other constraints you've set though.

>3. Can I update a table not involved in the transaction during the
>processing of the transaction (if I pass a list of tables)?

Yup. But I'm not sure if its subject to commit/rollback or just treated as a normal table edit.

>If I start a transaction and make some changes using SQL, are any
>changes made with the FindKey, Update methods involved in the
>transaction as well (as long as either the table is included in the list
>for the StartTrans or I use the EmptyEDBStringsArray parameter)?

No idea


Roy Lambert

Gordon,

<< If I pass a list of tables, then only those tables will be involved in
the transaction (as opposed to the whole database).  However, I cannot start
a second transaction using different tables until the first transaction has
completed with either a Rollback or a Commit.  Is this correct? >>

Correct.

<< 1. What is the select behavior for tables involved in the transaction for
other users (when a list of tables is passed)?  Can they also start a
transaction on the same table but different records? >>

No, only one transaction can be active on a given table at one time.

<< 2. Are there any restrictions on updating tables in the database not
involved in the transaction by other users when a transaction is being
processed? >>

No.

<< 3. Can I update a table not involved in the transaction during the
processing of the transaction (if I pass a list of tables)? >>

Sure, no problem.

--
Tim Young
Elevate Software
www.elevatesoft.com

Gordon,

<< If I start a transaction and make some changes using SQL, are any changes
made with the FindKey, Update methods involved in the transaction as well
(as long as either the table is included in the list for the StartTrans or I
use the EmptyEDBStringsArray parameter)? >>

Yes.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< If I pass a list of tables, then only those tables will be involved in
>the transaction (as opposed to the whole database). However, I cannot start
>a second transaction using different tables until the first transaction has
>completed with either a Rollback or a Commit. Is this correct? >>

>Correct.

What about other threads or nesting?

Roy Lambert

I've been trying out a new architecture for my application.  All of the
database functionality is contained in a COM object.  I pass SQL strings
or other record specific variables to the COM object where the tables
are accessed, data fetched, records updated, etc.  Data results are
passed back in variant arrays which are handled by the classes, so the
program has no access whatsoever to the tables directly.  The COM object
is created once at the beginning of the application and nowhere else.

Here is the behavior I'm seeing.  I call the COM object to start a
transaction using the EmptyEDBStringsArray parameter...

  if not AppDBDataMod.AppDB.InTransaction then begin
    AppDBDataMod.AppDB.StartTransaction(EmptyEDBStringsArray);

and then add a master record to Table A and detail records to Table B
(through separate calls to the COM object for each record).  Before
adding the detail records to Table B (because they can be added in more
than one way) I check to see if a transaction has already been started
by calling an InTransaction function in the COM object...

  Result := AppDBDataMod.AppDB.InTransaction;

and if not I call the COM object to start one.  At the end of the
updates I check where the transaction was started and call the COM
object from the appropriate place to perform the Commit...

  if AppDBDataMod.AppDB.InTransaction then
    AppDBDataMod.AppDB.Commit(True);

As I'm debugging my code, my expectation was that if I cancelled
execution of the program before the Commit then all the changes would be
rolled back.  But what happens is that each table change passed to the
COM object is performed immediately and nothing is rolled back.  As soon
as the table change is executed from within the COM object, the results
are visible within EDBManager, even though I "know" a transaction has
been started as the InTransaction method returns True as I continue with
the processing.

So I'm guessing I'm missing something important here or I am
misunderstanding how EDB works in the context of a COM object.  Any help
with this would be appreciated.

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Roy,

<< What about other threads or nesting? >>

Threads, yes.   Nesting, no.   Nested transactions will come with the
enterprise server that can do proper versioning.

--
Tim Young
Elevate Software
www.elevatesoft.com

Gordon,

<< and then add a master record to Table A and detail records to Table B
(through separate calls to the COM object for each record).  Before adding
the detail records to Table B (because they can be added in more than one
way) I check to see if a transaction has already been started by calling an
InTransaction function in the COM object...

  Result := AppDBDataMod.AppDB.InTransaction;

and if not I call the COM object to start one.  At the end of the updates I
check where the transaction was started and call the COM object from the
appropriate place to perform the Commit...

  if AppDBDataMod.AppDB.InTransaction then
    AppDBDataMod.AppDB.Commit(True);

As I'm debugging my code, my expectation was that if I cancelled execution
of the program before the Commit then all the changes would be rolled back.
>>

What do you mean by "cancelled" ?  Do you mean killing the process via the
IDE ?

<< But what happens is that each table change passed to the COM object is
performed immediately and nothing is rolled back.  As soon as the table
change is executed from within the COM object, the results are visible
within EDBManager, even though I "know" a transaction has been started as
the InTransaction method returns True as I continue with the processing. >>

Are you saying that this is occurring *after* the expected rollback, or
before ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
>
> What do you mean by "cancelled" ?  Do you mean killing the process via the
> IDE ?

Yes.  My expectation is that no changes in a transaction would be saved
until the actual Commit, whether that is an exception that occurs within
the executable, or the process being killed via the IDE or termination
of the process via Windows.

 > Are you saying that this is occurring *after* the expected rollback, or
> before ?

Before the rollback or commit.  I pause the executable in the IDE to
step through the code.  While stepping through the code I complete the
update to the first table and then pause code execution - refresh my
view of the data in the EDBManager - and see the change that was made.

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com