This snippet is from the 2.02.b10 SQL Manual: "When a transaction on the =
entire database is started, ElevateDB acquires a table transaction lock =
on all tables in the database.  This prevents any other sessions from =
inserting, updating, or deleting any rows from the tables in the =
database while the current transaction is active.  When a restricted =
transaction is started on a specific set of tables, ElevateDB will only =
acquire this table transaction locks on the tables specified as part of =
the transaction. "

Am I interpreting this correctly? If my .Net app starts a transaction, =
all of the tables get locked and no other users can make any data =
changes. For a multiuser app, that sound like it stinks so I'm hoping I =
am wrong. If I start a transaction in the .Net app and include a bunch =
of affected tables, then just those tables get locked also preventing =
any subsequent users from making any changes while the transaction is =
active, which I am also hoping I'm mis interpreting.

If I am wrong, what record locking happens during the transactions? Is =
it closer to a behavior where rows get locked & temporarily cached and =
are only visible to my connection until the transaction is committed? =
This is closer to what I'm hoping for really.

I'm looking at an app that would typically have a batch of many (maybe 5 =
mabye 500 or maybe 5000+) records with changes of some sort accross =
multiple tables @ any given time. Take that x up to 50 concurrent users =
doing the same thing, and I'm very concerned about table locks causing =
me a nightmare of support headaches. With that in mind, I am wide open =
for suggestion how to make transactions work for me, and to optimize =
their use.


Thanks


jr

James,

> Am I interpreting this correctly? If my .Net app starts a transaction,
> all of the tables get locked and no other users can make any data
> changes.

Yes, you are, except for a small detail: after a sessions starts a Transaction
and until the Transaction is Rolledback or Committed no other sessions can
change any table included in the transaction, but row locks can still be
acquired by other sessions. Only the update operations are delayed (or aborted
if they can't be executed after a timeout period) until the transaction ends.
That's why it is VERY IMPORTANT to keep transactions as short as possible.

> If I am wrong, what record locking happens during the transactions? Is
> it closer to a behavior where rows get locked & temporarily cached and
> are only visible to my connection until the transaction is committed?
> This is closer to what I'm hoping for really.

Changes executed inside a transaction are kept in memory and are only visible to
the session that started the transaction. Any row locks acquired during a
transaction will remain until the transaction is rolled back or committed.


> I'm looking at an app that would typically have a batch of many (maybe 5
> mabye 500 or maybe 5000+) records with changes of some sort accross
> multiple tables @ any given time. Take that x up to 50 concurrent users
> doing the same thing, and I'm very concerned about table locks causing
> me a nightmare of support headaches. With that in mind, I am wide open
> for suggestion how to make transactions work for me, and to optimize
> their use.

The "golden" rule is: make transactions as short as possible. Also, if only a
few tables are updated, use restricted transactions instead of a full transaction.

--
Fernando Dias
[Team Elevate]