Ed Leafe wrote:
> On Aug 15, 2007, at 6:30 PM, Paul McNett wrote:
>
>> Again, I believe that AutoCommit is named almost backwards, and
>> that we
>> need a new ExplicitTransactions property.
>
> AutoCommit has nothing to do with transactions. All it is
> designed
> to do is control the additional commit() call that some backends
> require after data is changed.
>
> If you are using transactions under programmatic control, then there
> is no reason whatsoever that you would want this behavior, and so you
> would set AutoCommit to True to tell Dabo not to add the additional
> commit() call. I realize that this may not be the optimum name, which
> is why I took the time to explain the derivation of the behavior and
> how it came to be a property, and why I said that I think that a
> discussion of a better name would be welcome.
>
Sorry Ed,
replies like the above "description" are the reason why the dabo docs
are so bad like they are. Either you can not read anything out of it
or you read it in a totally wrong way.
The autocommit setting for the backend database has database specific
meanings. That's why you have to differentiate between a autocommit
meaning for the database and for the client.
In dabo there is no clear differentiation between server and client
autocommit setting which leads to the confusion.
In addition, in some databases the autocommit server side setting
is influenced by the way transactions are started.
It can be a difference if a transaction is started implicit or explicit.
I hope that the following extracts from the docs of different
databases will help dismantle the confusion.
-------------------------------------------------------------------
Description from the sqlite docs:
"""
7.0 Transaction Control At The SQL Level
The changes to locking and concurrency control in SQLite version 3 also
introduce some subtle changes in the way transactions work at the SQL
language level. By default, SQLite version 3 operates in autocommit
mode. In autocommit mode, all changes to the database are committed as
soon as all operations associated with the current database connection
complete.
The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is
optional) is used to take SQLite out of autocommit mode. Note that the
BEGIN command does not acquire any locks on the database. After a BEGIN
command, a SHARED lock will be acquired when the first SELECT statement
is executed. A RESERVED lock will be acquired when the first INSERT,
UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired
until either the memory cache fills up and must be spilled to disk or
until the transaction commits. In this way, the system delays blocking
read access to the file file until the last possible moment.
The SQL command "COMMIT" does not actually commit the changes to disk.
It just turns autocommit back on. Then, at the conclusion of the
command, the regular autocommit logic takes over and causes the actual
commit to disk to occur. The SQL command "ROLLBACK" also operates by
turning autocommit back on, but it also sets a flag that tells the
autocommit logic to rollback rather than commit.
If the SQL COMMIT command turns autocommit on and the autocommit logic
then tries to commit change but fails because some other process is
holding a SHARED lock, then autocommit is turned back off automatically.
This allows the user to retry the COMMIT at a later time after the
SHARED lock has had an opportunity to clear.
If multiple commands are being executed against the same SQLite database
connection at the same time, the autocommit is deferred until the very
last command completes. For example, if a SELECT statement is being
executed, the execution of the command will pause as each row of the
result is returned. During this pause other INSERT, UPDATE, or DELETE
commands can be executed against other tables in the database. But none
of these changes will commit until the original SELECT statement finishes.
"""
Description from the Firebird docs:
"""
Database and Software Design
The availability and scalability of any software system can be
negatively affected by poor database design, careless query
specification, inappropriate workflows and, especially, poor transaction
management.
The multi-generational architecture ensures robustness, excellent,
optimistic task isolation and highly efficient throughput.
Well-performing databases result from careful design, clean
normalization, good indexing and, on the client side, well-planned
queries and careful attention to timely completion of transactions. On
the contrary, poor database design results in complicated queries and
careless indexing that can cripple the capability of the optimizer to
make effective query plans. Slow queries are generally the result of a
combination of these flaws.
Garbage Collection
MGA accumulates “garbage”, in the form of old record versions. The
engine performs in-line garbage collection. However, it will not permit
garbage collection of old record versions that are still “interesting”
to some transaction. Transactions that remain interesting for long
periods trap record versions pertinent to any transactions that started
later, causing garbage to build up to an unmanageable level. It is
essential that Firebird developers understand how this could happen and
write software that avoids it, keeping the level of garbage low enough
for the garbage collection subsystem to cope with.
Commit Retaining and “Autocommit”
Commit Retaining is a “feature” of Firebird that was inherited from the
ancestor, InterBase. It was implemented as a means to retain server-side
resources and keep them available for developers using Borland's rapid
application development tools and the BDE, the generic data access layer
that was used to connect Windows graphical applications to databases.
Its purpose was to present a level playing field for RAD development,
regardless of the database running at the back-end.
Autocommit—-a client-side mechanism that rolls the statement-level Post
and the transaction-level Commit phases into a single step-—was provided
to enable developers to avoid transactions altogether. In the RAD
components, Commit Retaining and Autocommit were bound together. This
fitted well with desktop databases like dBase and Paradox (whose engine
is, in fact, the BDE!), which do not have transactions.
With Firebird (and InterBase), Commit Retaining causes transactions to
remain interesting indefinitely. Garbage collection effectively ceases
on the “standard” Borland RAD tools database application and any other
applications that make use of Commit Retaining. Such systems are fraught
with problems of progressively degrading performance that cannot be
resolved except by shutting down the database and allowing these old
transactions to die.
Autocommit and Commit Retaining are not restricted to the Borland tools,
of course. They are supported by most data access interfaces and Commit
Retaining is available in SQL, so it behoves the application developer
to understand the effects and to use these features with extreme care
and control.
"""
Description from the Postgresql docs:
"""
The server-side autocommit setting was removed and reimplemented
in client applications and languages. Server-side autocommit was causing
too many problems with languages and applications that wanted to control
their own autocommit behavior, so autocommit was removed from the server
and added to individual client APIs as appropriate.
"""
"""
AUTOCOMMIT
When on (the default), each SQL command is automatically committed upon
successful completion.
To postpone commit in this mode, you must enter a BEGIN or START
TRANSACTION SQL command.
When off or unset, SQL commands are not committed until you explicitly
issue COMMIT or END.
The autocommit-off mode works by issuing an implicit BEGIN for you, just
before any command that
is not already in a transaction block and is not itself a BEGIN or other
transaction-control command,
nor a command that cannot be executed inside a transaction block (such
as VACUUM).
Note: In autocommit-off mode, you must explicitly abandon any failed
transaction by entering
ABORT or ROLLBACK. Also keep in mind that if you exit the session
without committing, your work
will be lost.
Note: The autocommit-on mode is PostgreSQL’s traditional behavior, but
autocommit-off is closer
to the SQL spec. If you prefer autocommit-off, you may wish to set it in
the system-wide psqlrc
file or your ~/.psqlrc file.
"""
-------------------------------------------------------------------
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/dabo-dev/[EMAIL PROTECTED]
