Hello.
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
Eli Tucker
"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
sqlsql
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Tuesday, March 27, 2012
Consistency of row when reading with READ UNCOMMITTED
Hello.
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
--
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
--
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
Consistency of row when reading with READ UNCOMMITTED
Hello.
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
Let me give a small example to illustrate my question.
Say I have a table T1 with some data in it:
CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
With connection #1, we perform an update statement:
UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
The above statement will update our row in two ways: it will update the
rowversion and the Note.
Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:
select * from [T1] WITH(NOLOCK)
Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?
What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.
Thanks in advance for your time,
Eli Tucker"Eli Tucker" <EliTucker@.discussions.microsoft.com> wrote in message
news:0C3A0C35-26D2-4578-9A8D-69B8353F29E4@.microsoft.com...
> Hello.
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>
There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.
> Let me give a small example to illustrate my question.
> Say I have a table T1 with some data in it:
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
> With connection #1, we perform an update statement:
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
> select * from [T1] WITH(NOLOCK)
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>
Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.
It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.
Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.
David
Saturday, February 25, 2012
Connection string with a Select Statement
Is it possible to use and ODBC connection string within a Select Statement?
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
Connection string with a Select Statement
Is it possible to use and ODBC connection string within a Select Statement?
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
If so what would the syntax be?
You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
Connection string with a Select Statement
Is it possible to use and ODBC connection string within a Select Statement?
If so what would the syntax be?You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
If so what would the syntax be?You can use something like Openrowset with the OLE DB
provider for ODBC. You can find examples and more
information in books online under Openrowset.
-Sue
On Fri, 10 Mar 2006 11:19:38 -0600, "Preacher Man" <nospam>
wrote:
>Is it possible to use and ODBC connection string within a Select Statement?
>If so what would the syntax be?
>
Tuesday, February 14, 2012
Connection reset error using JDBC
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context
1) [Thread[pool-2-thread-1,5,main],
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context1)
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?Connection reset error
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context1) [Thread[pool-2-thread-1,5,main],
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context1)
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?Connection reset error
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context1) [Thread[pool-2-thread-1,5,main],
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context1)
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?Connection reset error
I see the following error randomly -
* SQLException during execution of sql-statement:
* sql statement was 'SELECT count(*) FROM PRODUCT_ATTRIBUTES A0 WHERE A0.PRODUCT_ID = ?'
* Exception message is [An exception occurred during the DBComms.receive method. Operation:Connection reset. Context1) [Thread[pool-2-thread-1,5,main],
IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.]
* Vendor error code [0]
* SQL state code [08S01]
* The root stack trace is -->
* com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context1)
[Thread[pool-2-thread-1,5,main], IO:18563, Dbc:c67b9]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:293)
at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:189)
at org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:45)
at org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2217)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2252)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2177)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCount(PersistenceBrokerImpl.java:2148)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCount(DelegatingPersistenceBroker.java:383)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.loadSize(CollectionProxyDefaultImpl.java:154)
at org.apache.ojb.broker.core.proxy.CollectionProxyDefaultImpl.size(CollectionProxyDefaultImpl.java:268)
at com.serus.common.caching.FlyWeight.getCollection(FlyWeight.java:272)
at com.serus.common.caching.FlyWeight.intercept(FlyWeight.java:125)
at com.serus.dto.core.ProductDTO$$EnhancerByCGLIB$$52ef9193.getProductAttributes(<generated>)
at com.serus.manager.scm.ModelManager.getProductAttributeForProductIdAttributeName(ModelManager.java:1664)
at com.serus.inca.outbound.PNAdapter.processTemplate6_New(PNAdapter.java:910)
at com.serus.inca.outbound.PNAdapter.runChildAdapter(PNAdapter.java:144)
at com.serus.inca.outbound.WSBaseAdapter.runAdapter(WSBaseAdapter.java:114)
at com.serus.inca.outbound.WorkstreamProcessor.startProcessor(WorkstreamProcessor.java:375)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:152)
at com.serus.inca.outbound.WorkstreamProcessor.call(WorkstreamProcessor.java:78)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
My database and driver details are as below:
Database Product Version 9.00.1399
Driver Name Microsoft SQL Server 2005 JDBC Driver
Driver Version 1.1.1501.101
Driver Major Version 1
Driver Minor Version 1
It is a AMD 64 bit 2 dual core machine which is deployed with jdk 1.5 update 10.
Has anyone seen this before?
Thanks
-giriraj
We are also getting this. Have you made any progress? I posted the problem on the ms jdbc board.|||Are you using a single statement from multiple threads? Are you using distributed transactions? Can you try the CTP1 of the 1.2 release to see if it solves the issue.|||Yes, we are running in Tomcat, and multiple users on mutliple threads are running SQL statements through the JDBC driver.I'm not sure if we can try the CTP driver, since the error is occurring during performance testing on a minor release of a productoin commerical product. We have been experimenting with different connection pool settigs. We are using the Apache Commons DBCP connection pool, and over extend and modify some of their classes. We tried validating the connection as we obtained it from the pool, and that seems to have solved the problem for now. This implies the connectioins are going bad while idle in the the pool.
What is different in the CTP driver that could help? By CTP1, do you mean the currently available version, or is there an different version?
|||
Currently, the publicly available released SQL Server 2005 JDBC driver is version 1.1. The CTP1 driver referenced above is the public beta release of the SQL Server 2005 JDBC v1.2 driver, which is available for download at http://msdn.microsoft.com/data/jdbc
Jimmy
|||The test is expensive. Why do you think the CTP1 driver might help?
Subscribe to:
Posts (Atom)