Thread: RE: [Squirrel-sql-develop] DatabaseMetaData & Thread Safety
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Gollapudi, A. C. (STL) <Acg...@ex...> - 2005-10-07 20:34:20
Attachments:
DatabaseFailureReplicator.java
|
Robert: Thank you for your quick response. I'm attaching a standalone test that we've developed to replicate the protocol violation messages we see in Squirrel sessions. It uses pure JDBC and a set of threads attempting to share a DatabaseMetaData reference. It fails, almost invariably, irrespective of which JDK and Oracle JDBC driver versions we try (including the 10g version). Funny thing is, we do have a DBA who is trying to help us analyze the problem. As I mentioned, it's been an arduous task to replicate the problem. However, I wish to clarify myself. The problem with our database going down doesn't have a confirmed direct correlation to the problem we are discussing here. =20 However, I am still interested in finding out if Squirrel is using the JDBC API in a way that will generate unpredictable results. As developers use Squirrel SQL with the code completion plugin against Oracle database instances, we get SQLExceptions with messages "Protocol Violation", "Maximum data type length exceeded", and "Connection is closed" pretty regularly as we work with the SQL editor. Thank you for your help, Amit -----Original Message----- From: Rob Manning [mailto:Rob...@co...]=20 Sent: Friday, October 07, 2005 2:39 PM To: Gollapudi, Amit C. (STL) Cc: squ...@li... Subject: Re: [Squirrel-sql-develop] DatabaseMetaData & Thread Safety Gollapudi, Amit C. (STL) wrote: > Hi, > > Our development team uses Squirrel SQL to work mostly with Oracle > databases. Recently, however, we've been running into problems. =20 > After spending a lot of time trying to understand and reproduce the=20 > problems, we've narrowed the symptoms.=20 > > SchemaInfo's loadColumns method gets triggered from a few places. Of > interest, however, is the code completion plugin. The plugin seems to > trigger the loadColumns on key strokes. That is all and well. But,=20 > loadColumns uses a class instance variable reference to=20 > DatabaseMetaData to grab column information about the table being=20 > worked on.=20 > > Now, with Oracle's JDBC drivers, having DatabaseMetaData accessed from > multiple threads is inherently problematic. It causes some problems=20 > that, under the right load, take the entire database down. As=20 > disappointing as all that is, the question is whether SchemaInfo is=20 > right in making the assumption that JDBC drivers will provide a=20 > thread-safe DatabaseMetaData object. > > Thoughts, suggestions? > I've recently decompiled the Oracle 10g JDBC driver and I notice that oracle.jdbc.driver.OracleDatabaseMetaData has the following signature=20 for getColumns: public synchronized ResultSet getColumns(String s, String s1, String s2, String s3) throws SQLException =20 Since it's synchronized, multiple threads cannot be in here at the same=20 time. I've noticed in the past that most public JDBC API methods in the Oracle drivers are=20 synchronized using the instance monitor. So, while that's a huge performance bottleneck if you aren't careful, I don't see that concurrent access is going to cause trouble. I've personally=20 seen hundreds of threads sharing access to a single pool of PreparedStatements allocated from a=20 single Connection without any difficulty on the client. I don't see how this could "take=20 the entire database down". I'm not a DBA, but one might be useful in determining what statement(s)=20 are hogging the CPU/IO on your Oracle server. Rob CollabraSpace - Revolutionary Collaboration Visit us at http://www.collabraspace.com This message has been scanned for viruses by ClamAV v0.83 ******* Confidentiality Notice ******* This email, its electronic document attachments, and the contents of its = website linkages may contain confidential health information. This = information is intended solely for use by the individual or entity to = whom it is addressed. If you have received this information in error, = please notify the sender immediately and arrange for the prompt = destruction of the material and any accompanying attachments. |
From: Gollapudi, A. C. (STL) <Acg...@ex...> - 2005-10-07 21:32:34
|
Indeed. The stream has already been closed message is usually followed by the other ones I mentioned in our tests. Let me know if you need any help in diagnosing or fixing this problem. I would be glad to see my favorite SQL tool become fully useable again. Amit -----Original Message----- From: Rob Manning [mailto:Rob...@co...]=20 Sent: Friday, October 07, 2005 4:10 PM To: Gollapudi, Amit C. (STL) Cc: squ...@li... Subject: Re: [Squirrel-sql-develop] DatabaseMetaData & Thread Safety Gollapudi, Amit C. (STL) wrote: >Robert: > >Thank you for your quick response. I'm attaching a standalone test=20 >that we've developed to replicate the protocol violation messages we=20 >see in Squirrel sessions. It uses pure JDBC and a set of threads=20 >attempting to share a DatabaseMetaData reference. It fails, almost=20 >invariably, irrespective of which JDK and Oracle JDBC driver versions=20 >we try (including the 10g version). > >Funny thing is, we do have a DBA who is trying to help us analyze the=20 >problem. As I mentioned, it's been an arduous task to replicate the=20 >problem. However, I wish to clarify myself. The problem with our=20 >database going down doesn't have a confirmed direct correlation to the=20 >problem we are discussing here. > >However, I am still interested in finding out if Squirrel is using the=20 >JDBC API in a way that will generate unpredictable results. As=20 >developers use Squirrel SQL with the code completion plugin against=20 >Oracle database instances, we get SQLExceptions with messages "Protocol >Violation", "Maximum data type length exceeded", and "Connection is=20 >closed" pretty regularly as we work with the SQL editor. > =20 > This particular test gives me the following: java.sql.SQLException: Stream has already been closed at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269) at oracle.jdbc.ttc7.LongTTCItem.getChars(LongTTCItem.java:189) at=20 oracle.jdbc.dbaccess.DBDataSetImpl.getCharsStreamItem(DBDataSetImpl.java :1630) at=20 oracle.jdbc.driver.OracleStatement.getCharsInternal(OracleStatement.java :3347) at=20 oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.java:3 556) at=20 oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5 659) at=20 oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5 622) at=20 oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.jav a:739) at=20 DatabaseFailureReplicator$MetaDataTestCase.dumpResultSet(DatabaseFailure Replicator.java:69) at=20 DatabaseFailureReplicator$MetaDataTestCase.run(DatabaseFailureReplicator .java:50) at java.lang.Thread.run(Thread.java:534) What I find peculiar is that the getColumns implementation (from the=20 driver I decompiled) creates a new PreparedStatement, executes it and returns the ResultSet, which=20 should be independent of other ResultSet and their associated PreparedStatements. In other words,=20 closing one in thread A shouldn't have any effect on thread B's attempt to read another ResultSet. =20 Definitely requires a bit more study. Rob CollabraSpace - Revolutionary Collaboration Visit us at http://www.collabraspace.com This message has been scanned for viruses by ClamAV v0.83 ******* Confidentiality Notice ******* This email, its electronic document attachments, and the contents of its = website linkages may contain confidential health information. This = information is intended solely for use by the individual or entity to = whom it is addressed. If you have received this information in error, = please notify the sender immediately and arrange for the prompt = destruction of the material and any accompanying attachments. |
From: Gerd W. <bir...@t-...> - 2005-10-09 12:45:38
Attachments:
SchemaInfo.java
|
Hi Amit and Rob, since the DatabaseMetaData object is private in SchemaInfo and is used only in two methods it's easy to synchronize the object. The attached code introduces the necessary synchronized blocks. Amit, it would be nice if you could try out the code and tell us about the results. Please note that you need the newest version from CVS to take the attached class as it is. I've just committed some changes to it that deal with about the opposite of your problem (see RFE #1281766). If you don't want to do a fresh update just look for "synchronized" in the attached class. It should be easy to introduce my changes to the version you have. By the way, on Friday I managed to install Oracle 10g on my box :-). I couldn't get DatabaseFailureReplicator to fail even with a hundred threads. Probably I've got to few tables. Gerd Gollapudi, Amit C. (STL) wrote: > Indeed. The stream has already been closed message is usually followed > by the other ones I mentioned in our tests. Let me know if you need any > help in diagnosing or fixing this problem. I would be glad to see my > favorite SQL tool become fully useable again. > > Amit > > -----Original Message----- > From: Rob Manning [mailto:Rob...@co...] > Sent: Friday, October 07, 2005 4:10 PM > To: Gollapudi, Amit C. (STL) > Cc: squ...@li... > Subject: Re: [Squirrel-sql-develop] DatabaseMetaData & Thread Safety > > > Gollapudi, Amit C. (STL) wrote: > > >>Robert: >> >>Thank you for your quick response. I'm attaching a standalone test >>that we've developed to replicate the protocol violation messages we >>see in Squirrel sessions. It uses pure JDBC and a set of threads >>attempting to share a DatabaseMetaData reference. It fails, almost >>invariably, irrespective of which JDK and Oracle JDBC driver versions >>we try (including the 10g version). >> >>Funny thing is, we do have a DBA who is trying to help us analyze the >>problem. As I mentioned, it's been an arduous task to replicate the >>problem. However, I wish to clarify myself. The problem with our >>database going down doesn't have a confirmed direct correlation to the >>problem we are discussing here. >> >>However, I am still interested in finding out if Squirrel is using the >>JDBC API in a way that will generate unpredictable results. As >>developers use Squirrel SQL with the code completion plugin against >>Oracle database instances, we get SQLExceptions with messages "Protocol > > >>Violation", "Maximum data type length exceeded", and "Connection is >>closed" pretty regularly as we work with the SQL editor. >> >> > > This particular test gives me the following: > > java.sql.SQLException: Stream has already been closed > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269) > at oracle.jdbc.ttc7.LongTTCItem.getChars(LongTTCItem.java:189) > at > oracle.jdbc.dbaccess.DBDataSetImpl.getCharsStreamItem(DBDataSetImpl.java > :1630) > at > oracle.jdbc.driver.OracleStatement.getCharsInternal(OracleStatement.java > :3347) > at > oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.java:3 > 556) > at > oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5 > 659) > at > oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5 > 622) > at > oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.jav > a:739) > at > DatabaseFailureReplicator$MetaDataTestCase.dumpResultSet(DatabaseFailure > Replicator.java:69) > at > DatabaseFailureReplicator$MetaDataTestCase.run(DatabaseFailureReplicator > .java:50) > at java.lang.Thread.run(Thread.java:534) > > What I find peculiar is that the getColumns implementation (from the > driver I decompiled) creates a > new PreparedStatement, executes it and returns the ResultSet, which > should be independent of other > ResultSet and their associated PreparedStatements. In other words, > closing one in thread A shouldn't > have any effect on thread B's attempt to read another ResultSet. > Definitely requires a bit more study. > > Rob > > > CollabraSpace - Revolutionary Collaboration > Visit us at http://www.collabraspace.com > This message has been scanned for viruses by > ClamAV v0.83 > > > ******* Confidentiality Notice ******* > This email, its electronic document attachments, and the contents of its website linkages may contain confidential health information. This information is intended solely for use by the individual or entity to whom it is addressed. If you have received this information in error, please notify the sender immediately and arrange for the prompt destruction of the material and any accompanying attachments. > > > > > > ------------------------------------------------------- > This SF.Net email is sponsored by: > Power Architecture Resource Center: Free content, downloads, discussions, > and more. http://solutions.newsforge.com/ibmarch.tmpl > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
From: Gollapudi, A. C. (STL) <Acg...@ex...> - 2005-10-11 14:35:27
|
Thanks for the patch. I'll try it out sometime today. I wonder if your test against Oracle 10g didn't fail because it was running locally. It could, plausibly, have some correlation to the amount of time it takes to grab the DatabaseMetaData from a remote server. Amit -----Original Message----- From: Gerd Wagner [mailto:bir...@t-...]=20 Sent: Sunday, October 09, 2005 7:46 AM To: Gollapudi, Amit C. (STL) Cc: Rob...@co...; squ...@li... Subject: Re: [Squirrel-sql-develop] DatabaseMetaData & Thread Safety Hi Amit and Rob, since the DatabaseMetaData object is private in SchemaInfo and is used=20 only in two methods it's easy to synchronize the object. The attached=20 code introduces the necessary synchronized blocks. Amit, it would be nice if you could try out the code and tell us about=20 the results. Please note that you need the newest version from CVS to=20 take the attached class as it is. I've just committed some changes to it that deal with about the opposite of your problem (see RFE #1281766). If you don't want to do a fresh update just look for "synchronized" in the=20 attached class. It should be easy to introduce my changes to the version you have. By the way, on Friday I managed to install Oracle 10g on my box :-). I=20 couldn't get DatabaseFailureReplicator to fail even with a hundred=20 threads. Probably I've got to few tables. Gerd Gollapudi, Amit C. (STL) wrote: > Indeed. The stream has already been closed message is usually=20 > followed by the other ones I mentioned in our tests. Let me know if=20 > you need any help in diagnosing or fixing this problem. I would be=20 > glad to see my favorite SQL tool become fully useable again. >=20 > Amit >=20 > -----Original Message----- > From: Rob Manning [mailto:Rob...@co...] > Sent: Friday, October 07, 2005 4:10 PM > To: Gollapudi, Amit C. (STL) > Cc: squ...@li... > Subject: Re: [Squirrel-sql-develop] DatabaseMetaData & Thread Safety >=20 >=20 > Gollapudi, Amit C. (STL) wrote: >=20 >=20 >>Robert: >> >>Thank you for your quick response. I'm attaching a standalone test >>that we've developed to replicate the protocol violation messages we=20 >>see in Squirrel sessions. It uses pure JDBC and a set of threads=20 >>attempting to share a DatabaseMetaData reference. It fails, almost=20 >>invariably, irrespective of which JDK and Oracle JDBC driver versions=20 >>we try (including the 10g version). >> >>Funny thing is, we do have a DBA who is trying to help us analyze the >>problem. As I mentioned, it's been an arduous task to replicate the=20 >>problem. However, I wish to clarify myself. The problem with our=20 >>database going down doesn't have a confirmed direct correlation to the >>problem we are discussing here. >> >>However, I am still interested in finding out if Squirrel is using the >>JDBC API in a way that will generate unpredictable results. As=20 >>developers use Squirrel SQL with the code completion plugin against=20 >>Oracle database instances, we get SQLExceptions with messages "Protocol >=20 >=20 >>Violation", "Maximum data type length exceeded", and "Connection is >>closed" pretty regularly as we work with the SQL editor. >>=20 >> >=20 > This particular test gives me the following: >=20 > java.sql.SQLException: Stream has already been closed > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269) > at oracle.jdbc.ttc7.LongTTCItem.getChars(LongTTCItem.java:189) > at > oracle.jdbc.dbaccess.DBDataSetImpl.getCharsStreamItem(DBDataSetImpl.java > :1630) > at=20 > oracle.jdbc.driver.OracleStatement.getCharsInternal(OracleStatement.java > :3347) > at=20 > oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.java:3 > 556) > at=20 > oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5 > 659) > at=20 > oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5 > 622) > at=20 > oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.jav > a:739) > at=20 > DatabaseFailureReplicator$MetaDataTestCase.dumpResultSet(DatabaseFailure > Replicator.java:69) > at=20 > DatabaseFailureReplicator$MetaDataTestCase.run(DatabaseFailureReplicator > .java:50) > at java.lang.Thread.run(Thread.java:534) >=20 > What I find peculiar is that the getColumns implementation (from the > driver I decompiled) creates a > new PreparedStatement, executes it and returns the ResultSet, which=20 > should be independent of other > ResultSet and their associated PreparedStatements. In other words,=20 > closing one in thread A shouldn't > have any effect on thread B's attempt to read another ResultSet. =20 > Definitely requires a bit more study. >=20 > Rob >=20 >=20 > CollabraSpace - Revolutionary Collaboration > Visit us at http://www.collabraspace.com > This message has been scanned for viruses by > ClamAV v0.83 >=20 >=20 > ******* Confidentiality Notice ******* > This email, its electronic document attachments, and the contents of=20 > its website linkages may contain confidential health information. =20 > This information is intended solely for use by the individual or=20 > entity to whom it is addressed. If you have received this information > in error, please notify the sender immediately and arrange for the=20 > prompt destruction of the material and any accompanying attachments. >=20 >=20 >=20 >=20 >=20 > ------------------------------------------------------- > This SF.Net email is sponsored by: > Power Architecture Resource Center: Free content, downloads,=20 > discussions, and more. http://solutions.newsforge.com/ibmarch.tmpl > _______________________________________________ > Squirrel-sql-develop mailing list=20 > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop >=20 |
From: Rob M. <Rob...@co...> - 2005-10-11 14:45:14
|
Gollapudi, Amit C. (STL) wrote: >Thanks for the patch. I'll try it out sometime today. I wonder if your >test against Oracle 10g didn't fail because it was running locally. > > My test which reproduced the error was against a local Oracle server (9i instead of 10g though) While I'm here, I might add that looking at SchemaInfo I was happy to find that it only queries getColumns once per table - after that it uses the results of the previous call. So it definitely seems to be limiting as best it can the number of calls to the database for meta info about tables. Rob CollabraSpace - Revolutionary Collaboration Visit us at http://www.collabraspace.com This message has been scanned for viruses by ClamAV v0.83 |
From: Rob M. <Rob...@co...> - 2005-10-07 21:10:09
|
Gollapudi, Amit C. (STL) wrote: >Robert: > >Thank you for your quick response. I'm attaching a standalone test that >we've developed to replicate the protocol violation messages we see in >Squirrel sessions. It uses pure JDBC and a set of threads attempting to >share a DatabaseMetaData reference. It fails, almost invariably, >irrespective of which JDK and Oracle JDBC driver versions we try >(including the 10g version). > >Funny thing is, we do have a DBA who is trying to help us analyze the >problem. As I mentioned, it's been an arduous task to replicate the >problem. However, I wish to clarify myself. The problem with our >database going down doesn't have a confirmed direct correlation to the >problem we are discussing here. > >However, I am still interested in finding out if Squirrel is using the >JDBC API in a way that will generate unpredictable results. As >developers use Squirrel SQL with the code completion plugin against >Oracle database instances, we get SQLExceptions with messages "Protocol >Violation", "Maximum data type length exceeded", and "Connection is >closed" pretty regularly as we work with the SQL editor. > > This particular test gives me the following: java.sql.SQLException: Stream has already been closed at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269) at oracle.jdbc.ttc7.LongTTCItem.getChars(LongTTCItem.java:189) at oracle.jdbc.dbaccess.DBDataSetImpl.getCharsStreamItem(DBDataSetImpl.java:1630) at oracle.jdbc.driver.OracleStatement.getCharsInternal(OracleStatement.java:3347) at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.java:3556) at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5659) at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5622) at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:739) at DatabaseFailureReplicator$MetaDataTestCase.dumpResultSet(DatabaseFailureReplicator.java:69) at DatabaseFailureReplicator$MetaDataTestCase.run(DatabaseFailureReplicator.java:50) at java.lang.Thread.run(Thread.java:534) What I find peculiar is that the getColumns implementation (from the driver I decompiled) creates a new PreparedStatement, executes it and returns the ResultSet, which should be independent of other ResultSet and their associated PreparedStatements. In other words, closing one in thread A shouldn't have any effect on thread B's attempt to read another ResultSet. Definitely requires a bit more study. Rob CollabraSpace - Revolutionary Collaboration Visit us at http://www.collabraspace.com This message has been scanned for viruses by ClamAV v0.83 |