Troubleshooting
Problem
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: - 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 SendStringParametersAsUnicode to false can result in data corruption if the server-side character encoding uses high order bits; for example, SQL Server data types, NCHAR, NVARCHAR, and NTEXT.
Setting parameter type for sp_executesql different from one in database (for example: nvarchar for varchar) causes that Microsoft SQL probably does not 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 does not 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.
Add 3 records to the preceding table with following values:
Execute 2 independent transactions (read_committed, auto commit = false): Transaction 1:
Transaction 2:
|
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).
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21207965