Why should I always set autoCommit(false) if most of the database accesses are just one SQL statement?

Reusable code means that every database access sets its transaction mode, so they all are completelly independent. That's why I set the particular autoCommit mode, in most cases to true.

So, my main questions are:

Is there any performance penalty if I set autoCommit(true) repeated times, even knowing that autocommit is already switched on in most cases?

Should it be better to switch autocommit off, even if most database accesses are just one SQL statement?

If I switch autoCommit off, does a new transaction start inmediately either after executing that opertation or after performing a commit/rollback?

Thank you in advance.

Juan Pedro

On Thu, 2003-06-05 at 21:22, Robert DiFalco wrote:

Why do you believe autoCommit( true ) allows the code to be more reusable? Why not just set autoCommit( false ) always?

-----Original Message-----
From: Juan Pedro López Sáez [mailto:jpls@...]
Sent: Thursday, June 05, 2003 11:25 AM
To: Firebird-Java@yahoogroups.com
Subject: [Firebird-Java] autocommit mode


Hello everybody,

I've got a perfomance issue related to the autocommit mode.

Most of my application's database accesses are single SQL statements, so I don't need a transaction management different to the autoCommit(true) behaviour.
However, there are a few cases where I must manage transactions in order to update two tables atomically, so there I set autoCommit(false).

Because of this mixed scenario, I set autoCommit(true) everytime a perform an "unmanaged" database access. It's a simple way of writting reusable code.

Questions:

1- Is it a high consuming resources to set autoCommit(true), just to ensure the database access in this mode?
2- I have read contradictory oppinions about switching autocommit on:
    - It is recomended in order to release resources attached to an active transaction.
    - It is not recomended because there is a perfomance penalty. Every database access is an independent transaction and transactions are time consuming operations.
    What's the best choice?
3- If I switch autoCommit off, does a new transaction start inmediately either after executing that opertation or after performing a commit/rollback?

Any comments would be very apreciate.

Thank you.

Juan Pedro

--

To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

Yahoo! Groups Sponsor

To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.