MSSQL Server not using index when called with JDBC and Java PreparedStatement [closed]

Question

I am using a MSSQL Server 2016 from my java application via JDBC with transaction isolation level READ_COMMITTED and with AUTOCOMMIT = false.

I need to lock some rows of a table from differnet threads/connections and get different results when using a PreparedStatemtn vs. using a Statement. With the Statement everything is shiny, but the PreparedStatement locks too many rows (in the following sample all rows) because (assumption) the PK index is not used.

This is the sample table:

CREATE TABLE [dbo].[TEST] (
 [ID] [NVARCHAR](64) NOT NULL,
 [VALUE] [NVARCHAR](64) NULL
);
GO
ALTER TABLE [dbo].[TEST]
ADD CONSTRAINT PK_TEST PRIMARY KEY CLUSTERED ([ID]) WITH (ALLOW_ROW_LOCKS=ON,         
ALLOW_PAGE_LOCKS=ON);
GO

And i use the following test class:

public void test() throws Exception
{
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD))
    {
        try (PreparedStatement pst = connection.prepareStatement("DELETE FROM TEST"))
        {
            pst.execute();
        }
        connection.commit();

        // Initial-Data
        for (int i = 0; i < 10; i++)
        {
            try (PreparedStatement pst = connection.prepareStatement("INSERT INTO TEST (ID, VALUE) VALUES (?, ?)"))
            {
                pst.setString(1, Integer.toString(i));
                pst.setString(2, "Text"+i);
                pst.execute();
            }
        }
        connection.commit();
    }

    try (Connection connection1 = createConnection())
    {
        try (Statement st = connection1.createStatement();
                ResultSet resultSet = st.executeQuery("SELECT ID FROM TEST WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE ID IN ('2','3','4')"))
        {
            while (resultSet.next())
                System.out.println(resultSet.getString(1));
        }

        try (Connection connection2 = createConnection();
                Statement st = connection2.createStatement();
                ResultSet resultSet = st.executeQuery("SELECT ID FROM TEST WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE ID IN ('6','7','8')"))
        {
            while (resultSet.next())
                System.out.println(resultSet.getString(1));
        }
    }

    try (Connection connection1 = createConnection())
    {
        try (PreparedStatement pst = connection1.prepareStatement("SELECT ID FROM TEST WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE ID IN (?,?,?)"))
        {
            pst.setString(1, "2");
            pst.setString(2, "3");
            pst.setString(3, "4");
            try (ResultSet resultSet = pst.executeQuery())
*// HERE ALL ROWS OF THE TABLE ARE LOCKED...*
            {
                while (resultSet.next())
                    System.out.println(resultSet.getString(1));
            }
        }

        try (Connection connection2 = createConnection();
                PreparedStatement pst = connection2.prepareStatement("SELECT ID FROM TEST WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE ID IN (?,?,?)"))
        {
            pst.setString(1, "6");
            pst.setString(2, "7");
            pst.setString(3, "8");
            try (ResultSet resultSet = pst.executeQuery())
*// ... SO HERE THE EXECUTION WAITS FOR THE ROWS TO BE UNLOCKED*
            {
                while (resultSet.next())
                    System.out.println(resultSet.getString(1));
            }
        }
    }
}

Connections are retrieved though:

Connection createConnection() throws SQLException
{
    Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
    connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    connection.setAutoCommit(false);
    return connection;
}

My questions are:

• why is the execution and the index use for Statement and PreparedStatement different (i know MSSSQL uses 'sp_prepexec' or 'sp_prepexecrpc' (from Microsoft SQL Server: Prepared Statements))
• does it mean for my application, that i should never use prepared statements with MSSQL because of possibly not using indices (which is not very good, because i have to support other DBs also, where i already use massively PreparedStatement)

As a side note: when using the PreparedStatement without '?' (without setting parameters, but using the values direct in the SQL like in the Statement) the index is used correct and the selected rows are locked.

I already learned that i have to user NVARCAR instead of VARCHAR with PreparedStatements: IBM has already reported as an issue with PreparedStatements and the usage of VARCHAR/CHAR/TEXT which also expects that the indices are not used, but when switching to NVARCHAR/NCHAR/NTEXT they work again using the indices Different behavior when using Statement versus PreparedStatement Java objects

Any help is appreciated!

Thanks

Answer 1

Hy, according to the reference to stackoverflow.com/questions/510214/ mentioned by mustaccio i found out that this behaviour is the intended behaviour of Microsoft SQL Server with PreparedStatements.

Also the reference to parameter sniffing was very helpfully.