Search code examples
javajdbcprepared-statementdeadlockmssql-jdbc

PreparedStatement parameterization triggers deadlocks (JDBC, SQL Server 2019)

Background

I am working on a piece of enterprise software for which we've recently decided to move from building SQL queries via string concatenation to doing so by using prepared statements with parameters, so that this first example:

String query = "SELECT * FROM CARS WHERE MAKE = '" + getMake() 
             + "' AND MODEL = '" + getModel() + "'";
PreparedStatement preparedStatement = connection.prepareStatement(query);

becomes the second:

String query = "SELECT * FROM CARS WHERE MAKE = ? AND MODEL = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, getMake());
preparedStatement.setString(2, getModel());

For context, I am using SQL Server 2019 and JDBC.

Issue

With this change being the ONLY difference in the code, I now receive deadlocks that interrupt processing and I don't understand why. The environment is highly concurrent in that large batches of data are processed in multiple threads, with many of each of insert, delete, and select. What I don't understand is why this concurrency only becomes a problem when using templated statements, as opposed to concatenated ones.

So far I've tried:

  • Lowering the isolation level to READ_UNCOMMITTED – results in only delete statements locking
  • Creating a unique PreparedStatement object rather than reusing one object – results in no change
  • Asking ChatGPT, which suggested that it could be related to SQL Server reusing the template statements, since the template strings are identical, while the concatenated versions are unique because they include the variables within the string.
  • Using WITH (ROWLOCK), which seems to result in essentially the same situation only with Key Locks instead of a Page Locks causing the deadlock.

What else can I try? Why in the world would the behavior between these approaches be so dramatic as to cause deadlocks? How can I fix them?

Thanks!

Solution

  • if you ask for reason of the dramatic difference in performance I can give at least this one: the type conversion.

    Java type of SQL parameters is Unicode String but on the database you will have something else like varchar with a specific collation. The conversion can cause that even if an index is defined for the table columns it will not be used.

    The concatenated String used before the code change didn't need conversion of types.

    Solution

    You can tell the driver to not send parameters as Unicode by adding to the connection url:

    sendStringParametersAsUnicode=false

    Hope it helps.