Snapshot isolation with pooled connections
- From: ttps <ttps@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 12 May 2006 16:26:01 -0700
Please let me know if it's possible to use snapshot isolation with the MS
JDBC driver when connections
are pooled using DBCP. I'm seeing some very strange behavior. The first time
a connection is borrowed
from the pool, it malfunctions. Only connections that were previously
borrowed and returned to the pool
behave properly. The brand-new connections inappropriately return SQL error
3591 (sqlState: S0001) when
they should instead return SQL error 3960 (sqlState: S0005). Please see the
stack traces and the test
code below. Thank you. Any feedback would be greatly appreciated.
ENVIRONMENT:
SQL Server 2005 9.00.2047
Microsoft SQL Server 2005 JDBC driver 1.0.809.102
Tomcat 5.5.16 (with integrated DBCP 1.2.1)
Windows XP Pro SP2
DataSource configuration in server.xml:
<GlobalNamingResources>
<Resource name="jdbc/dsdb"
type="javax.sql.DataSource"
auth="Container"
scope="Shareable"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerXADataSource"
url="jdbc:sqlserver://box312:1433;database=db312"
username="user"
password="pass"
defaultTransactionIsolation="4096"
defaultAutoCommit="false"
loginTimeout="60"
poolPreparedStatements="false"
maxOpenPreparedStatements="300"
maxActive="3000"
initialSize="2"
maxIdle="10"
minIdle="10"
maxWait="60000"
testOnBorrow="true"
validationQuery="select 1;"
/>
</GlobalNamingResources>
The test code listed below uses two threads to create a database conflict.
The first stack trace shows
what should happen. These are the expected results. Unfortunately, very
different and erroneous results
occur when the connections borrowed from the pool are brand-new. The second
stack trace shows these
unexpected results.
Started===================================================Stack trace when TestSnapshotIsolation.java is run with ALREADY-USED connections:
com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation
transaction
aborted due to update conflict. You cannot use snapshot isolation to access
tabl
e 'dbo.company' directly or indirectly in database 'dsdb' to update, delete,
or
insert the row that has been modified or deleted by another transaction.
Retry t
he transaction or change the isolation level for the update/delete statement.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown S
ource)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteUpdate(Unkno
wn Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(Unknown
Source)
at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(Delegat
ingStatement.java:225)
at test.TestSnapshotIsolation$T1.run(TestSnapshotIsolation.java:44)
at java.lang.Thread.run(Thread.java:595)
2006-05-12 13:58:26,000 WARN TestSnapshotIsolation:100 -
finished===================================================
Started===================================================Stack trace when TestSnapshotIsolation.java is run with BRAND-NEW connections:
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction failed in
database
'dsdb' because the statement was run under snapshot isolation but the
transactio
n did not start in snapshot isolation. You cannot change the isolation level
of
the transaction to snapshot after the transaction has started unless the
transac
tion was originally started under snapshot isolation level.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown S
ource)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteQuery(Unknow
n Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown
Source)
at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(Delegati
ngStatement.java:205)
at test.TestSnapshotIsolation$T2.run(TestSnapshotIsolation.java:64)
at java.lang.Thread.run(Thread.java:595)
2006-05-12 13:59:54,546 WARN TestSnapshotIsolation:100 -
finished===================================================
package test;TEST CODE:
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public final class TestSnapshotIsolation {
static final Log log = LogFactory.getLog(TestSnapshotIsolation.class);
static final DataSource ds = getDataSource();
static final String selectString =
"select * from company where co_company_num=3";
static final String updateString =
"update company set co_manager=14 where co_company_num=3";
static int stepsCompleted = 0;
private TestSnapshotIsolation() {
}
static class T1 implements Runnable {
public void run() {
Connection conn;
Statement stmt;
try {
conn = ds.getConnection();
stmt = conn.createStatement();
stmt.executeQuery(selectString);
stepsCompleted = 1;
while (stepsCompleted < 2)
pause();
stmt.executeUpdate(updateString); // this should fail on
conflict
conn.commit();
stmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
static class T2 implements Runnable {
public void run() {
while (stepsCompleted < 1)
pause();
try {
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
stmt.executeQuery(selectString);
stmt.executeUpdate(updateString); // this should work fine
conn.commit();
stmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
stepsCompleted = 2;
}
}
public static void mainline() {
log.warn("\nStarted===================================================");
// Un-comment the following 9 lines to run the test against ALREADY-USED
connections.
// try {
// Connection c1 = ds.getConnection();
// Connection c2 = ds.getConnection();
// c1.close();
// c2.close();
// }
// catch (SQLException e) {
// e.printStackTrace();
// }
Thread thread1 = new Thread(new T1());
Thread thread2 = new Thread(new T2());
thread1.start();
thread2.start();
try {
thread1.join();
thread2.join();
}
catch (InterruptedException e) {
log.warn("Interrupted durring join.");
}
log.warn("\nfinished===================================================");
}
static void pause() {
try {
Thread.sleep(500);
}
catch (InterruptedException e) {
log.warn("Pause was interrupted.");
}
}
private static DataSource getDataSource() {
DataSource s = null;
try {
Context c = (Context) new
InitialContext().lookup("java:comp/env");
s = (DataSource) c.lookup("jdbc/dsdb");
}
catch (NamingException e) {
throw new RuntimeException("Could not locate dsdb.", e);
}
return s;
}
}
.
- Follow-Ups:
- Prev by Date: Re: Callablestatement.execute() and clearParameter() causing excep
- Next by Date: RE: java.lang.SecurityException
- Previous by thread: Re: Callablestatement.execute() and clearParameter() causing excep
- Next by thread: RE: Snapshot isolation with pooled connections
- Index(es):
Relevant Pages
|
