The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by Yanping Wa » Thu, 27 Apr 2006 08:11:02



hi, I just updated my database from SQL Server 2000 to SQL Server 2005 and
is getting this error.
I was using the SQLServerDataSource in the 2000 version and the code works
fine.
Now I use SQLServerConnectionPoolDataSource in the 2005 version.

Can someone please tell me how to fix it? Thanks,

Here is the exception thrown:

SEVERE: com.microsoft.sqlserver.jdbc.SQLServerException: The COMMIT
TRANSACTION
request has no corresponding BEGIN TRANSACTION.
com.microsoft.sqlserver.jdbc.SQLServerException: The COMMIT TRANSACTION
request
has no corresponding BEGIN TRANSACTION.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Un
known Source)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.sendCommit(Unknown S
ource)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.commit(Unknown
Sourc
e)
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by Joe Weinst » Thu, 27 Apr 2006 08:26:24


Have you considered the first thing, which is to verify that you started
a transaction? You shouldn't be calling commit() unless you did. Unless
you had called setAutoCommit(false) and then do something, you should
never call commit.
Joe Weinstein at BEA Systems

 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by Yanping Wa » Thu, 27 Apr 2006 09:04:50

hi, Joe,

Yes, setAutoCommit(false) was called when the connection was created. If
this is the case, should the call to commit() fail? Thank you, Yanping

*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by Joe Weinst » Thu, 27 Apr 2006 09:28:33


No it shouldn't. Once you've called setAutoCommit(false) the driver
should never throw that exception. However, maybe it's a blessing
in disguise, because it means that for some number of statements
previous to this, whatever you were doing in the DBMS was committing
statement-by-statement because the DBMS is thinking the connection
is already in autoCommit(true) mode. Something bad happened with your
SQL before that, that messed up the DBMS state. Did you get any
deadlock exception? Did you try to do any DDL in autoCommit(false)
mode? Did you call any procedures that may have "COMMIT TRAN" in
them? How easily/quickly/predictably can you repeat this failure?

Joe Weinstein at BEA Systems
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by Angel Saen » Sat, 29 Apr 2006 07:30:24

Joe has a great handle on this now <g> I believe that this is a good time
and place to run some ideas by the group.

Yanping, the reason why you are seeing this exception is a direct result of
the following implementation detail in the new driver:
Old driver: when you call conn.Commit() we send to the server "IF
TRANCOUNT>0 COMMIT TRAN"
New driver: when you call conn.Commit() we send to the server "COMMIT TRAN"

In the old driver we would ignore your commit call if the server was not in
a transaction, in the new driver we would fail. This behavior is not
consistent with other JDBC drivers that we are aware of and it may make
sense to revert back to the standard way.

The second thing I would like to shop would be moving from the BEGIN TRAN
model to a transaction mode model, this is a request that we have received
from Joe and a number of other customers.

Thoughts, ideas, suggestions?

--
Angel Saenz-Badillos [MS] Data Programability
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://www.yqcomputer.com/
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by Joe Weinst » Sun, 30 Apr 2006 01:01:30


I think the main problem is that under an eclectic collection of circumstances
semantical or even *syntactical* errors in user SQL will cause the DBMS to
roll back any current transaction as part of the response. To emphasise, I
have seen cases where an error in the SQL parser(!!) will cause a rollback.
I can see how this would cause a very thorny problem for the driver, to have
to react to almost every exception with a recheck of the transactional state
of the connection. However, whether or not the DBMS has been told to start an
implicit transaction or it has been simply given a "BEGIN TRAN", after one
of these mystery errors, the previous user transaction is defunct, and
they had better be notified of such. Otherwise they may continue with the
rest of what they intended as a transaction, and commit only the remaining
part.
The current customer's problem *may* be self-inflicted, if they have
"commit tran" in procedures etc, but if it's a DBMS tx drop, the only
adequate safe solution will be for the driver to have to know when/whether
the DBMS has summarily killed a tx, and then not let the user proceed
with the connection until the user sync's up with the real condition by
calling rollback().

Joe Weinstein at BEA Systems