FreeTDS Development Group

["Betty Chang" <bettyc AT aimnet.com>]

Thank you all for your help and comments!

 

So, let me summarize -- if I have a bunch of things I want do under one transaction, I MUST use just one Statement, or one PreparedStatement?

 

I cannot use 2 different prepared statements to update 2 tables under the same transaction, correct?

 

And, if I want to read back something from an update in the same transaction, I must use the same Statement or PreparedStatement that I used for the update?

 

Betty

 

----- Original Message -----

From: Eric Mountain

To: TDS Development Group

Sent: Thursday, February 24, 2000 11:47 AM

Subject: [freetds] Re: JDBC bug?

(1) I can't reproduce this if I use the same Statement object for UDPATE and SELECT: if it were the case it would be very strange since the row is locked by the UPDATE and the SELECT is in the same transaction.  I won't even try to use 2 different Statement objects since (provided autoCommit is false) I know that I will get that behaviour as it is what I would expect: freetds allows you to have thread-safe Statements on the same Connection by having a _physical_ connection to the database per Statement object.  So if you UPDATE on one Statement (and don't commit) and SELECT on another Statement, then the SELECT will block until the UDPATE is commited because they are in 2 different transactions.  This is not a bug, this is ACID.

 

(2) This is debatable as a bug. As mentionned above, freetds implements thread-safe Statement on the same Connection by having a _physical_ connection to the database per Statement object.  When you do Connection.commit(), freetds issues a COMMIT TRAN on each _physical_ connection owned by each Statement object created using the Connection object.  As part of Statement.close(), ROLLBACK TRAN is issued on the physical connection for that Statement (well, I didn't check, maybe it isn't explicitly issued, but the server will rollback any uncommitted open transaction when the Statement closes its physical connection, same difference) which is why you loose your update if you have not done Connection.commit() before doing Statement.close().

 

Now, is (2) in violation of the JDBC spec?  It's late, I'm not checking now...

 

Cheers,

:E

-----Original Message-----
From: Betty Chang <bettyc AT aimnet.com>
To: TDS Development Group <freetds AT franklin.oit.unc.edu>
Date: Thursday, 24 February, 2000 08:18
Subject: [freetds] JDBC bug?

Hello -- I have found 2 problems.  Are these known bugs?

 

(1)  If I update a row within a transaction, then try to read the same row within the transaction, the program hangs, because the row has become locked.  I am using the same Connection object.  I have tried reading both with the same and with different Statement objects and it hangs in both cases.

 

(2)  If I update a row within a transaction, then do a close() on the Statement object,.  or a PreparedStatement object,  then commit() later on, the update never takes place.   I have to wait until after the commit() to close the statements.  This is not as big a problem as (1).

 

Thanks

 

Betty