|
Problem(Abstract) |
Using JDBC™ PreparedStatement object with Java™ String
parameter accessing SQL Server datatypes CHAR, VARCHAR or TEXT might lead
to a deadlock situation. |
|
|
|
Cause |
- Deadlock Scenario
Create a table called TestTable with 2 columns:
Column Name
|
Data Type
|
Col1
|
VARCHAR (50)
|
Col2
|
VARCHAR (100)
|
Add 3 records to the preceding table with following values:
Execute 2 independent transactions (read_committed, auto commit = false):
Transaction 1:
UPDATE TestTable SET Col2='1' WHERE
Col1='a' |
Transaction 2:
UPDATE TestTable SET Col2='2' WHERE
Col1='b' |
|
- Results
- Usage of Statement in Java code allows the preceding two transactions
to be executed concurrently (no deadlock).
- Usage of PreparedStatement in Java code causes Transaction 2 to be
held until Transaction 1 is finished (deadlock occurs).
The holding of Transaction 2 in case B is the result of trying to lock the
row being updated by Transaction 1. This is an unexpected behavior because
the two transactions update different records.
- Conclusions
Detailed analysis has shown the following.
Usage of PreparedStatement causes execution of stored procedure
sp_executesql. For type String parameters, Java uses nvarchar(4000)
variable independently of the column type in database (VARCHAR, NVARCHAR).
It is an unfounded and undocumented property of the JDBC driver and should
be treated as an error. The JDBC Driver documentation allows usage of
CHAR, VARCHAR and text types in the database and does not mention
parameter types NCHAR, NVARCHAR, NTEXT for sp_executesql when the database
types are CHAR, VARCHAR or TEXT.
Setting parameter type for sp_executesql different from one in database
(for example: nvarchar for varchar) causes that Microsoft® SQL probably
doesn't use index and searches through whole table until proper record is
found (identified by WHERE condition). That is how we can explain a try to
get a lock (U type) in transaction 2 on record being updated by
transaction 1.
It is an unfounded and undocumented property of Microsoft SQL and should
be treated as an error. Documentation for sp_executesql doesn't mention
that.
Testcase B can be run out of Java (for example: SQL Analyzer) executing in
2 transactions proper sp_executesql with parameter nvarchar for database
column of varchar type.
Change of column types from varchar to nvarchar caused that both
transactions in both testcases worked independently.
|
|
|
Resolving the
problem |
There is no easy way with JDBC to specify or identify the
proper types to be used for this type of operation without creating some
overhead. DataDirect Connect® JDBC driver provides a connection property
called "SendStringParametersAsUnicode" which can be set to "false" to
address the situation. The description of the
"SendStringParametersAsUnicode" from Connect JDBC's documentation as
follows:
{true | false}. Determines whether string parameters are sent to the
Microsoft SQL Server database in Unicode or in the default character
encoding of the database.
If set to true, string parameters are sent to Microsoft SQL Server in
Unicode.
If set to false, string parameters are sent in the default encoding,
which can improve performance because the server does not need to convert
Unicode characters to the default encoding. You should, however, use
default encoding only if the parameter string data you specify is the same
as the default encoding of the database.
The default is true.
For more information, see DataDirect Connect for JDBC User's Guide and
Reference, chapter "The Microsoft SQL Server Driver", section "Connection
Properties" (which is available on-line at the DataDirect Technologies Web
site). |
|
|
|
|
Cross Reference information |
Segment |
Product |
Component |
Platform |
Version |
Edition |
Application Servers |
Runtimes for Java Technology |
Java SDK |
|
|
|
|
|
|