Hi,

Thank you for the accurate answers.

Just the final questions.

Let's suppose I set autoCommit(true) and execute a SQL statement which
fires a trigger in my database. The trigger tries to make some updates
in a table. After some registers are successfully updated, it fails
because a primary key constraint. So the java application receives a
SQLException.

In this situation, am I expected to do conn.rollback() in the catch
block, to ensure every updated register is conveniently rolled back, or
is this opertation automatically perfomed because of the autocommit
mode?

So the general question could be: what happens after a SQL error occurs
and autocommit mode is switched on?

Thank you very much.

Juan Pedro

> Hi,
>
> > Why should I always set autoCommit(false) if most of the database
> > accesses are just one SQL statement?
>
> If you do not need transactions, you can use setAutocommit(true).
>
> > 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.
>
> Sorry, I do not understand this sentense.
>
> According to the JDBC specification, in auto-commit mode each
> statement is executed in its own transaction context. This means that
> for each statement database server will start new transaction and
> commit it after statement is finished (for selects it means "when last
> row was fetched", for updates/inserts/deletes right after
> executeUpdate() method) or before a new statement is executed.
>
> Transaction start and commit are _expensive_ operations, so
> setAutocommit(true) always slows things down.
>
> > 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?
>
> Almost none. If there is no transaction started, setAutocommit(true)
> is just setting a boolean flag in the FBConnection class. If there is
> transaction running, it will cause transaction commit. Since you
> execute all statements in auto-commit mode, there hardly will be any
> active transaction when you call setAutocommit(true).
>
> > Should it be better to switch autocommit off, even if most database
> > accesses are just one SQL statement?
>
> Depends on your usage scenario. It will definitely speed things up,
> but you have to consider transaction isolation level. Firebird's
> lowest transaction isolation level is TRANSACTION_READ_COMMITTED, this
> means that your transaction will "see" changes made by concurrent
> transactions only when they commit. If this is ok for you, switch
> auto-commit off, if no - switch it on.
>
> > If I switch autoCommit off, does a new transaction start inmediately
> > either after executing that opertation or after performing a
> > commit/rollback?
>
> According to the JDBC specification transaction is started right
> before executing a statement that should be executed in new
> transaction context. In other words, when you commit/rollback, old
> transaction is finished (by either commit or rollback). Then next call
> to Statement.execute() will check if there is any active transaction,
> and will start new one if no active transaction is found.
>
> Best regards,
> Roman Rokytskyy
>
>
> 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.