Re: Is statement.executeBacth() a transaction?

On Thu, 30 Oct 2008 21:22:54 -0700, George wrote:

Thank you. As always, I got a bit more confused on the other by
clearing up one part. I originally thought all the statements in one
transaction are all executed or non executed in jdbc automatically. Am I
right? However, the second last paragraph in the article I linked seems
to say the opposite. It says one needs to rollback when SQLExcption is
thrown, which seems to indicate that one transaction (one conn.commit())
might leave some statement executed and some not.

If you use the default (auto-commit) each SQL statement is treated as a
separate commitment unit, IOW it does:

start commitment unit
execute SQL statement
if (statement failed)
rollback
else
commit

This is OK for most single-table updating, but as soon as you start to
work with multiple tables, e.g. storing a new payment detail, crediting
the recipient and debiting the sender, then you have several SQL
statements that must all complete successfully if the database is to be
consistent. Under these conditions you need to turn auto-commit off and
your program must explicitly commit or rollback depending on whether the
statement set completed or failed.

JDBC's batch execution has nothing to do with commitment apart from
telling you that all statements executed OK or that at least one failed.
This looks to me as if you'd need to disable auto-commit before using
batch execution, so you'd still need to commit or rollback. The javadoc
says that not all JDBC drivers support batch execution, so IMO if there's
any change that your code needs to run against different RDBMS then its a
good thing to avoid.

Batch execution isn't something I'd do myself. I prefer more control over
the transaction. In any case its only suitable for running an inline set
of SQL statements while many cases the update actions depend on the
database contents (e.g. do we update an existing row or must it be
inserted).


--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |
.

Relevant Pages

  • Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... there is an "implied" transaction that's handled ... I would hit the SQL Server list... ... However, if the parameters change, the optimal query plan ... difference in execution time between QA and the application. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Effecting run time change in behavior
    ... We have implemented a OO language called Indus that is an extension of ... separates program execution from execution environment (that enables ... the transaction management problem has come up wrt to the e) ... These tx keywords in turn invoke the Transaction manager (a separate ...
    (comp.object)
  • Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... I created another stored procedure that starts a transaction, ... However, if the parameters change, the optimal query plan ... >> execution time between QA and the application. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problems with views from Firebird
    ... > else 'Execution succeeded ... I don't understand why he used an explicit transaction for a single ... > that the system has to be rebooted after every report. ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • error 7390 when isolation level is set to serializable...
    ... I am trying to replicate the execution of a stored procedure and hence am using isolation level of serializable as recommended by BOL for purge operations. ... SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ...
    (microsoft.public.sqlserver.connect)