1

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

5
  • Where do you get the idea that your index isn't being used? You have one index -- Clustered -- which is the table. Not a copy of the table. Your queries have no choice but to use it to access that table's data. Have you looked at the query plan? – Erik Darling Nov 29 '17 at 12:41
  • Sure i looked at the query plan of the Statement, it states "Clustered Index Seek" for the PK. But for the PreparedStatement the plan states "Clustered Index Scan" :-( Switching to a non-clustered Index does change nothing in my test. – wogy Nov 29 '17 at 13:16
  • Related: stackoverflow.com/questions/510214/… – mustaccio Nov 29 '17 at 13:29
  • @wogy - So, the PreparedStatement is using the index - it's just doing a scan rather than a seek. – RDFozz Nov 29 '17 at 21:07
  • You can try to use setNString() instead – eckes Dec 9 '17 at 2:27
0

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.

1

Not the answer you're looking for? Browse other questions tagged or ask your own question.