RE: Snapshot isolation with pooled connections
- From: ttps <ttps@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 18 May 2006 14:19:02 -0700
Here is an easier-to-deal-with test case:
/**
* This test case demonstrates a bug in the Microsoft SQL Server 2005 JDBC
* driver.
*
* Under DBCP, the driver does not initialize connections properly. 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. When a snapshot transaction conflict occurs, the brand-new
* connections inappropriately return SQL error 3591 (sqlState: S0001). They
* should instead return SQL error 3960 (sqlState: S0005).
*
* Environment:
* jdk 1.5.0_06
* SQL Server 2005 9.00.2047
* Microsoft SQL Server 2005 JDBC driver 1.0.809.102
* DBCP 1.2.1 (provided with Tomcat 5.5.16)
* Windows XP Pro SP2
*
* SQL code to create test table:
* create table test (
* t_id int identity primary key,
* t_num int
* )
* go
* insert test (t_num) values(1);
* insert test (t_num) values(1);
* insert test (t_num) values(1);
* go
*
* To run this test, create the test table (code provided above), and adjust
* the first couple of lines in main() to reflect your host, db, userName, and
* passWord. You will get different exceptions depending on whether you
specify
* true or false for the TEST_WITH_ALREADY_USED_CONNECTIONS variable below.
* This behavior does not occur when other drivers are used with DBCP.
*
*/
package test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.apache.tomcat.dbcp.dbcp.BasicDataSource;
public class TestConflictMSJdbc {
private static final boolean TEST_WITH_ALREADY_USED_CONNECTIONS = false;
private static final String SQL_SELECT = "select * from test where
t_id=?";
private static final String SQL_UPDATE =
"update test set t_num=? where t_id=?";
private static final int KEY = 2;
private static final int NUM = 14;
public static void main(String[] args) {
BasicDataSource ds = new BasicDataSource();
ds.setUrl("jdbc:sqlserver://bhw2:1433;database=dsdb;sendStringParametersAsUnicode=false");
ds.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
ds.setUsername("ds_external");
ds.setPassword("=cubbycando=");
ds.setDefaultTransactionIsolation(4096); // snapshot isolation
ds.setDefaultAutoCommit(false);
ds.setPoolPreparedStatements(true);
ds.setMaxOpenPreparedStatements(300);
ds.setMaxActive(160);
ds.setInitialSize(3);
ds.setMaxIdle(3);
ds.setMinIdle(3);
ds.setMaxWait(60000);
if (TEST_WITH_ALREADY_USED_CONNECTIONS) {
try {
Connection c1 = ds.getConnection();
Connection c2 = ds.getConnection();
Connection c3 = ds.getConnection();
c1.close();
c2.close();
c3.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
try {
PreparedStatement ps;
Connection conn1 = ds.getConnection();
ps = conn1.prepareStatement(SQL_SELECT);
ps.setInt(1, KEY);
ps.executeQuery();
ps.close();
Connection conn2 = ds.getConnection();
ps = conn2.prepareStatement(SQL_UPDATE);
ps.setInt(1, NUM);
ps.setInt(2, KEY);
ps.addBatch();
ps.executeBatch();
ps.close();
conn2.commit();
conn2.close();
ps = conn1.prepareStatement(SQL_UPDATE);
ps.setInt(1, NUM);
ps.setInt(2, KEY);
ps.addBatch();
try {
ps.executeBatch();
}
catch (Exception e) {
// A transaction conflict occured, as planned.
e.printStackTrace();
}
finally {
ps.close();
conn1.close();
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}
"ttps" wrote:
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;
}
}
- References:
- Snapshot isolation with pooled connections
- From: ttps
- Snapshot isolation with pooled connections
- Prev by Date: Driver corrupts prepared statements in pool.
- Next by Date: Driver does not initialize connections properly under DBCP.
- Previous by thread: Snapshot isolation with pooled connections
- Next by thread: RE: java.lang.SecurityException
- Index(es):
Relevant Pages
|
