Driver corrupts prepared statements in pool.
- From: ttps <ttps@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 18 May 2006 10:54:01 -0700
/**
* This test case demonstrates a bug in the Microsoft SQL Server 2005 JDBC
driver.
*
* When a prepared statement fails due to a snapshot transaction conflict in
* Sql Server, the copy of that statement in the pool becomes corrupted and
will
* cause an exception the next time it is used.
*
* Run this program with setPoolPreparedStatements(true) (see below) and an
* unwarranted exception is thrown.
*
* Run this program with setPoolPreparedStatements(false) and everything works
* properly.
*
* This crazy behavior does not occur with other drivers.
*
* 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 results depending on whether you specify
* true or false in setPoolPreparedStatements() below. This crazy behavior
does
* not occur with other drivers.
*
*/
package test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.apache.tomcat.dbcp.dbcp.BasicDataSource;
public class TestConflictDBCP {
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("user");
ds.setPassword("pass");
ds.setDefaultTransactionIsolation(4096); // snapshot isolation
ds.setDefaultAutoCommit(false);
ds.setPoolPreparedStatements(true); // false works, but true fails!
ds.setMaxOpenPreparedStatements(300);
ds.setMaxActive(160);
ds.setInitialSize(10);
ds.setMaxIdle(10);
ds.setMinIdle(10);
ds.setMaxWait(60000);
try {
// A connection will NOT work properly until it has been
returned to the pool.
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) {
// Do nothing. A transaction conflict occured, as planned.
// e.printStackTrace();
}
finally {
ps.close();
conn1.close();
}
Connection conn3 = ds.getConnection();
ps = conn3.prepareStatement(SQL_UPDATE);
ps.setInt(1, NUM);
ps.setInt(2, KEY);
ps.addBatch();
try {
ps.executeBatch(); // throws exception if
poolPreparedStatements=true
}
catch (Exception e) {
e.printStackTrace();
}
finally {
ps.close();
conn3.close();
}
}
catch (Exception e) {
throw new RuntimeException(e);
}
}
}
.
- Prev by Date: Re: Cannot open user default database.
- Next by Date: RE: Snapshot isolation with pooled connections
- Previous by thread: Re: sqlserver.jdbcdriver Thread safe?
- Next by thread: Driver does not initialize connections properly under DBCP.
- Index(es):
Relevant Pages
|
