Re: Behavior of Connection.commit()

Sam,
The problem is the same as above. If we set set_implicit_transactions on we
will no longer be able to tell that a serious issue happened on commit and
we would not throw an exception:

//start transaction
//insert data 1
//execute ddl
exception. At this time insert data 1 has been rolled back!
//insert data 2 //with set_implicit_transactions on this will create a NEW
transaction under the covers.
//COMMIT will no longer throw an exception, it will commit the transaction
started with insert data 2 and you will silently ignore the fact that data 1
has been rolled back.


--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/




"Sam Wilson" <SamWilson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:250B6857-2B1A-44C3-B7A1-33CEEBBD5E30@xxxxxxxxxxxxxxxx
> I'm with Joe... I'm a bit concerned why the driver doesn't issue "set
> implicit_transactions on" which is the logical equivalent of
> setAutoCommit(false).
>
> Am I missing something or is this functionality not analogous??
>
> Thanks,
>
> Sam Wilson
> Ecount, Inc.
>
> "Joe Weinstein" wrote:
>
>>
>>
>> Angel Saenz-Badillos[MS] wrote:
>>
>> > You have to remember that Sql Server does not understand the concept of
>> > a
>> > autoCommit(false) state, we have to fake this with "Begin transaction"
>> > blocks. By default the Sql Server database is in the equivalent of
>> > autoCommit(true) state, anything you do will permanently update the
>> > database.
>> >
>> > When we set autoCommit to off the expectation is that no SQL statements
>> > will
>> > be committed (that is, the database will not be permanently updated)
>> > until
>> > you have explicitly told it to commit by invoking the commit() method.
>> > To do
>> > this in Sql Server we issue the following command to the server:
>> >
>> >
>> >>c = d.connect(URL, props);
>> >>c.setAutoCommit(false);
>> >
>> > IF @@TRANCOUNT = 0 BEGIN TRAN //if we are currently not in a
>> > transaction,
>> > start one.
>>
>> Surely the driver can do "set implicit_transactions on" instead of
>> that. My old driver from '96 does this:
>>
>> setAutoCommit(true): "if (@@trancount > 0) rollback transaction set
>> implicit_transactions off"
>> setAutoCommit(false): "if (@@trancount > 0) rollback transaction set
>> implicit_transactions on"
>>
>> Joe Weinstein at BEA Systems
>>
>> >>s.commit();
>> >
>> > COMMIT TRAN; IF @@TRANCOUNT = 0 BEGIN TRAN //Commit the existing
>> > transaction, start a new transaction to fake the fact that we are still
>> > in
>> > autoCommit(false) state.
>> >
>> >
>> >>Also, when the driver throws the exception above, in
>> >>a circumstance you suggest, presumably the connection
>> >>is still in autoCommit(false) mode. Is it?
>> >
>> >
>> > Thanks to the BEGIN TRAN at the end of the commit it would be, but this
>> > should be no consolation. When you receive the "no corresponding begin"
>> > exception you probably have data corruption.
>> >
>> > We recently looked into this error for a large customer. They started
>> > getting this error with our driver and filed it as a bug. When we
>> > investigated it turned out that they were doing something as inoquous
>> > as
>> >
>> > try{
>> > //create table
>> > }catch{
>> > //ignore failure, this means that table already existed. }
>> >
>> > This was sometimes running inside of a transaction, when the table was
>> > already in the database the Sql Exception would roll back the
>> > transaction
>> > but they would ignore it. With their previous driver they had no idea
>> > that
>> > everything before the above code was being rolled back.
>> >
>> > I really should put this in an easier to read format, maybe a blog with
>> > source code you can try.
>> >
>>
>>


.

Relevant Pages

  • Re: Driver AutoCommit issue
    ... I am using the container managed transaction and I expect the container to handle that for me. ... Is there different driver class that i have to use in the connection pool configuration. ... Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: commit
    ... I am using SQLite, and I want to select the maxfrom a certain ... gives an error telling that the database is locked if in that moment ... allowing an update until the "transaction" finishes. ... Only then does process A commit its transaction and the rowA becomes ...
    (perl.beginners)
  • Re: Behavior of Connection.commit()
    ... "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." ... When this happens the old driver was ... if no transaction active throw exception. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Managing transations involving thousands of records
    ... Where are they before they are in the database? ... This statement indicates that the logical transaction is the individual ... > C. Commit in blocks ... Often that intensity is worth it. ...
    (comp.databases.oracle.server)
  • Managing transations involving thousands of records
    ... more than one insert operation into the database. ... four different ways of implementing transaction ... Commit in blocks ... Using savepoint ...
    (comp.databases.oracle.server)