Data lock with prepared statement
Brian
connecting to SQL Server 2008 using JDBC.
In a unit test, I create two separate database connections and set
auto-commit to false. I insert a record into the
same table of each database connection. These records are uncommited.
Next, I execute a prepared statement that queries one of the
connections for the data that was just inserted. I get a database
lock. If I do the same query with a non-prepared statement, no data
lock occurs. Why?
I have verified that the connection used to query the data is the same
connection that inserted that data.
Erland Sommarskog
a diffrence in query plan, so that you get a scan with a prepared statement.
I don't really what you mean with a database lock, but I assume that you
mean a table lock.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
Brian
Yes, I meant a table lock.
I found this link on the web, so I'm somewhat certain I'm not crazy
Has anyone else seen this and know the fix?
Erland Sommarskog
> Yes, I meant a table lock.
>
> I found this link on the web, so I'm somewhat certain I'm not crazy
>
> http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/5eae7e94-70f3-4564-ac70-b66bf108675b/
>
> Has anyone else seen this and know the fix?
Could you post:
1) Your actual client code?
2) The CREATE TABLE statement for the table.
3) Run SQL Profiler when the client code produces the prepared statement.
Brian
out how to get SQL Proflier to work now.
CREATE TABLE TEST_TABLE(
id varchar2(20),
descr varchar2(50),
CONSTRAINT pk_test_table PRIMARY KEY (id)
);
public void testExample() throws Exception
{
String id1 = ValueGenerator.pseudoUniqueAlphaString("T-", 20);
String id2 = ValueGenerator.pseudoUniqueAlphaString("T-", 20);
String desc1 = ValueGenerator.pseudoUniqueAlphaString("T-", 30);
String desc2 = ValueGenerator.pseudoUniqueAlphaString("T-", 30);
Connection connA = getConnection();
connA.setAutoCommit(false);
assertFalse("Auto-Commit should be set to false.",
connA.getAutoCommit());
// INSERT #1
String sql = "INSERT INTO test_table(id, descr) " +
" VALUES( '"+id1+"','"+desc1+"' )";
System.out.println(sql);
Statement stmt = connA.createStatement();
stmt.executeUpdate(sql);
Connection connB = getConnection();
connB.setAutoCommit(false);
assertFalse("Auto-Commit should be set to false.",
connB.getAutoCommit());
assertFalse("The connections should not be the same.",
connA.equals(connB));
assertFalse("The connections should not be the same.", connA ==
connB);
// INSERT #2
String sql2 = "INSERT INTO test_table(id, descr) " +
" VALUES( '"+id2+"','"+desc2+"' )";
System.out.println(sql2);
Statement stmt2 = connB.createStatement();
stmt2.executeUpdate(sql2);
// SELECT #1 & SELECT #2
String select1 = "select * from test_table where id=?";
PreparedStatement ps1 =
connA.prepareStatement(select1,ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps1.setString(1, id1);
ps1.executeQuery();// <----------------------- ***** LOCKED.
Progress halted
// This is never executed
String select2 = "select * from test_table where id=?";
PreparedStatement ps2 =
connB.prepareStatement(select2,ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps2.setString(1, id2);
ps2.executeQuery();
}
private Connection getConnection() throws SQLException
{
Properties connectionProps = new Properties();
connectionProps.put("user", "xxx");
connectionProps.put("password", "yyy");
Connection conn = DriverManager.getConnection("jdbc:sqlserver://
aserver:1433", connectionProps);
System.out.println("Connected to database.");
return conn;
}
On Jun 30, 4:23 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Brian (banman...@gmail.com) writes:
> > Yes, I meant a table lock.
>
> > I found this link on the web, so I'm somewhat certain I'm not crazy
>
> >http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/threa...
Erland Sommarskog
> I synthesized it down to a generic JDBC example. I'm trying to figure
> out how to get SQL Proflier to work now.
>
> CREATE TABLE TEST_TABLE(
> id varchar2(20),
> descr varchar2(50),
Something is amiss here. There is no data type varchar2 in SQL Server.
I believe there is one in Oracle.
Could you confirrm which RDBMS you are working with?
If you are working with SQL Server, could you give the correct CREATE TABLE
statement for test_table?
Brian
CREATE TABLE TEST_TABLE(
id varchar(20),
descr varchar(50),
CONSTRAINT pk_test_table PRIMARY KEY (id)
);
On Jul 6, 4:59 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
Erland Sommarskog
> My fault. I was testing in both Oracle and SQL Server.
>
> CREATE TABLE TEST_TABLE(
> id varchar(20),
> descr varchar(50),
>
> CONSTRAINT pk_test_table PRIMARY KEY (id)
> );
as such. A simple way to verify this is to change the definition of
test_table so that id is nvarchar. In this case you should not see any
lock on table level.
This is the scoop: By default Java has all strings as Unicode, and
the string parameters as declared as nvarchar. This means your query
gets translated to:
SELECT * FORM test_table WHERE id = @unicodevalue
SQL Server has a strict data-type precendence which says that if two
data types meet, and there is an implicit conversion, the type with
lowest precedence is converted to the other type. varchar has lower
precendence than nvarchar, since varchar covers a much smaller range.
But the index on id is defined for varchar, and cannot be used as
intended. Exactly what happens, depends on the collation. If the
collation of the column is a Windows collation, the index can still
be seeked with a range seek, which is slower, but it's only an
overhead of 200-300%. However, if you have an SQL collation, the
index is dead entirely, because in an SQL collations there are different
rules in varchar and nvarchar. Since the index cannot be used, SQL Server
has to scan the table, and this leads to locking.
When you install SQL Server, the default is always a Windows collation,
unless you system locale is English (United States).
I've already mention two remedies: 1) Use nvarchar instead. 2) Use a
Windows collation. There is a third: you can tell Java to not use
Unicode. Exactly where you find this switch, I don't know. I don't
work with Java myself.
Brian
Why does a statement work vs a prepared statement? Is it because of
the pre-compilation of the SQL?
Erland Sommarskog
> Why does a statement work vs a prepared statement? Is it because of
> the pre-compilation of the SQL?
If you say
SELECT * FROM tbl WHERE id = 'somevalue'
The literal is varchar, and there is no data type conversion. The fact
that the SQL string that JDBC passes to SQL Server is Unicode is of
no importance.
On the other hand, if you say:
SELECT * FROM tbl WHERE id = N'somevalue'
you will see exactly the same issue. Now the constant is nvarchar, and you
will get the same conversion problem.
Again, the prepared statement has nothing to with it as such. It's the
data-type clash which is the problem.
Parameterised statements - prepared or not - is what you should use, to
avoid risk for SQL injection, to utilise the plan cache in SQL Server
better, and to avoid problem with some data types such as datetime.
Brian
been a tremendous help.
Take care