bug? socket timeout leaving transaction hung

[JAF-53]

We're experiencing a intermittent problem with a J2EE application, MS JDBC
2005 1.1 driver, and SQL Server 2005. The application opens a transaction,
issues a number of prepared statements in batches, and then commits. The
problem we are seeing is that the transaction hangs up. This then ends up
blocking other processes, causing even more problems.

In the Activity monitor, the process that hangs is marked as blocking,
status = sleeping, command = awaiting command. On the locks page, the
process shows that it has been granted all locks requested. Basically from
the db side, it looks like we are waiting for the application to issue the
rest of its statements and commit the transaction. However, from the
application side, we are never getting a result back from the statement
execution.

1. This has been reported as an issue with the jDTS driver as well.
Perhaps this is a SQL Server or JVM bug?

2. There does not appear to be a way to specify a socket timeout with the
MS JDBC 2005 driver. Is there a connection string parameter to do this? We
are using lockTimeout to at least return from the blocked processes, and this
works, however the blocking process has all its locks, so lockTimeout doesn't
work there.

3. Anyone else experiencing this?

[Evan T. Basalik (MSFT)]

Generally, excessive blocking due to locks is caused by unexpectedly deep transactions levels. Check your transaction level using a Profiler trace to see
if you can find out why you are not committing all of the transactions. We would normally expect to see a "Select @@trancount >0" type command to figure
out if you are in a nested transaction and to deal with it.

Generally, this shows up as being a code issue, rather than a driver or database issue. You really should attack this using the command timeout, as
compared to the using the lockTimeout.

Evan

--------------------
>Thread-Topic: bug? socket timeout leaving transaction hung
>thread-index: AcfDCJWKCGnGQH2fRzakVJb3bHWv3g==
>X-WBNR-Posting-Host: 208.102.181.197
>From: =?Utf-8?B?SkFGLTUz?= <JAF...@discussions.microsoft.com>
>Subject: bug? socket timeout leaving transaction hung
>Date: Tue, 10 Jul 2007 08:40:03 -0700
>Lines: 24
>Message-ID: <C6EA548E-491B-4358...@microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: TK2MSFTNGHUB02.phx.gbl
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:235
>NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver

Evan T. Basalik
This posting is provided “AS IS” with no warranties, and confers no rights.

[JAF-53]

The number of open transactions in the activity monitor was 1. That suggests
this is not a nested transaction issue, right? Also, I need to be clear, the
process that is blocking is not itself blocked. This is not a deadlock. The
blocking process is idle, with an open transaction, and has acquired all of
its locks, and has no waits.

[Bindesh Vijayan[MSFT]]

Can you post the settings/properties for your connection

"JAF-53" <JA...@discussions.microsoft.com> wrote in message
news:B6C1274E-2017-4B93...@microsoft.com...

[JAF-53]

the connection string:
jdbc:sqlserver://myServer:1433;
databasename=myDB;
sendStringParametersAsUnicode=false;
applicationName=myAPP;
lockTimeout=1200000

We are using hibernate and c3p0 for database persistence and connection
pooling. Some relevant settings:

hibernate.connection.isolation=1
hibernate.jdbc.use_scrollable_resultset=false
hibernate.c3p0.min_size=3
hibernate.c3p0.max_size=25
hibernate.c3p0.timeout=3600
hibernate.c3p0.max_statements=0
hibernate.c3p0.idle_test_period=300
connection.pool_size=1
statement_cache.size=0
use_outer_join=true

[Bindesh Vijayan[MSFT]]

Will you be able to create a sample app and send it to me ..

"JAF-53" <JA...@discussions.microsoft.com> wrote in message

news:050ECBFF-1220-411B...@microsoft.com...

[JAF-53]

I can't do that, but I can post some sanitized snippets of the code if that
would help.

[Bindesh Vijayan[MSFT]]

I believe this issue is not something to do with the ms jdbc driver, the
reason why i asked the code is to run it here and see the profiler traces
for SQL. May be you can follow the same path and identify the statement that
is holding a block.

"JAF-53" <JA...@discussions.microsoft.com> wrote in message

news:10C88076-7AC8-447D...@microsoft.com...

[JAF-53]

I know the statement that is blocking. But that's not the issue. The
blocking process ISN'T DOING ANYTHING, but it has an open transaction. It
has all of its locks, it doesn't have any waits. From the code's
point-of-view, its never getting a response back from the database after it
executes a statement in the transaction. From the database point of view,
its waiting for the code to finish the transaction. Obviously there is a
disconnect somewhere in-between.

I really don't think this is an issue with the statements in the
transaction. They are getting key level locks to update single records.
This error is intermittent, I can't reproduce it at will, but with enough
volume I eventually hit it, usually daily.

[Bindesh Vijayan[MSFT]]

We need to do an analysis of the issue. Is it possible for you to open a
incident with MS CSS?

Thanks

"JAF-53" <JA...@discussions.microsoft.com> wrote in message

news:213AF7BA-0F37-4A5A...@microsoft.com...

[Kev]

Was there any outcome for this?

Cheers.

[Manoj Kumar]

I do have an identical situation here at my work.

In case of a SELECT query, the shared lock obtained should not block any DML
operations, but what is happening is this SELECT query appears to block many
other DML operations and stays in the picture for a very long time.

The blocking pattern that I see is unlike any other environments. Are there
any known issues [such as connection reset] with the JDBC drivers for
Sqlserver?

--
Manoj Kumar

[Evan T. Basalik (MSFT)]

This is a classic blocking analysis. If you need some assistance with this, please open up a case with our support group.

Evan
--------------------
>Thread-Topic: bug? socket timeout leaving transaction hung

>thread-index: AcgmCg7TRs8v308CShGgk7rwvCVuNQ==
>X-WBNR-Posting-Host: 207.46.193.207
>From: =?Utf-8?B?TWFub2ogS3VtYXI=?= <Manoj...@discussions.microsoft.com>
>References: <C6EA548E-491B-4358...@microsoft.com> <t7CO#QzwHH...@TK2MSFTNGHUB02.phx.gbl> <B6C1274E-2017-
4B93-B600-E...@microsoft.com> <2942CAD7-EE1B-420B...@microsoft.com> <050ECBFF-1220-411B-9690-
C89DB5...@microsoft.com> <335D811A-A359-4E59...@microsoft.com> <10C88076-7AC8-447D-9C85-8E0BDAD027A7
@microsoft.com> <0784C75E-25E4-4266...@microsoft.com> <213AF7BA-0F37-4A5A...@microsoft.com>
>Subject: Re: bug? socket timeout leaving transaction hung
>Date: Tue, 13 Nov 2007 07:30:02 -0800
>Lines: 34
>Message-ID: <645FA0D6-834E-41D4...@microsoft.com>

>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal

>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: TK2MSFTNGHUB02.phx.gbl
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:341
>NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver

Evan T. Basalik

[someguy]

Posting this for the benefit of any future developers stuck with a similar
problem-


We had some odd problems in our application where one of the connections in
our
connection pool would just not release the transaction from SQL Server 2005,
and
then retain it&rsquo;s locks on the tables. This would then cause deadlocks in
the other transactions/connections when they needed to operate on the same
tables.

From SQL Server Activity Monitor you could see the connections in deadlock.
Upon
tracking the guilty connection, you can see the last chunk of sql that it
executed, and it&rsquo;s status = sleeping, and command = awaiting command.

Initially we thought this locking to be due to the default c3p0 query to test
a
connection (on SQL Server 2005 it is some &ldquo;sp_tables&rdquo; or something
to enumerate the tables). We later placed an override on that in the c3p0
config
as:
c3p0.preferredTestQuery=select 1;

That still didn&rsquo;t solve the problem.

We have a custom hibernate session context manager, which ensures a
session-per-transaction strategy, and ensures that each thread in the
application is not able to start more than one transaction on a session.

We also checked all commit and rollback statements in the code on transactions
to ensure they are all closed out.

Still the problem remained.

In the end &ndash; out of pure desperation, we upgraded the c3p0 connection
pool
from 0.9.1 to 0.9.1.2; and since then we&rsquo;ve not had a single deadlock!
(knock on wood)



Custom build of Hibernate 3.3.1

hibernate.transaction.factory_class=org.hibernate.transaction.JDBCTransactionFactory
hibernate.connection.autocommit=false
hibernate.jdbc.batch_size=30
We&rsquo;re using a custom solution for session-per-transaction strategy.


c3p0 0.9.1 for connection pool (was bundled with
hibernate-distribution-3.3.1.GA)
#c3p0 Connection Pool Config
c3p0.acquireIncrement=3
c3p0.acquireRetryAttempts=0
c3p0.acquireRetryDelay=1000
c3p0.breakAfterAcquireFailure=false
c3p0.autoCommitOnClose=false
c3p0.idleConnectionTestPeriod=60
c3p0.initialPoolSize=10
c3p0.minPoolSize=10
c3p0.maxPoolSize=50
c3p0.maxIdleTime=550
c3p0.maxIdleTimeExcessConnections=300
c3p0.maxStatementsPerConnection=0
c3p0.numHelperThreads=15
c3p0.maxStatements=0
c3p0.preferredTestQuery=select 1;

iNet Opta JDBC driver

SQL Server 2005 (version 9.0.1399)