Uploaded image for project: 'Infinispan'
  1. Infinispan
  2. ISPN-11004

SQL server exception purging data

    Details

      Description

      When using string keyed jdbc store with dialect="SQL_SERVER" a SQL exception of 'FOR UPDATE clause allowed only for DECLARE CURSOR' is thrown.

      14:57:39,444 WARN  [org.infinispan.PERSISTENCE] (expiration-thread--p7-t1) ISPN000026: Caught exception purging data container!: org.infinispan.persistence.spi.PersistenceException: Failed clearing string based JDBC store
              at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.purge(JdbcStringBasedStore.java:502)
              at org.infinispan.persistence.spi.SegmentedAdvancedLoadWriteStore.purge(SegmentedAdvancedLoadWriteStore.java:291)
              at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$purgeExpired$8(PersistenceManagerImpl.java:522)
              at java.util.ArrayList.forEach(ArrayList.java:1257)
              at org.infinispan.persistence.manager.PersistenceManagerImpl.purgeExpired(PersistenceManagerImpl.java:528)
              at org.infinispan.persistence.support.DelegatingPersistenceManager.purgeExpired(DelegatingPersistenceManager.java:90)
              at org.infinispan.expiration.impl.ExpirationManagerImpl.processExpiration(ExpirationManagerImpl.java:120)
              at org.infinispan.expiration.impl.ExpirationManagerImpl$ScheduledTask.run(ExpirationManagerImpl.java:282)
              at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
              at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
              at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
              at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
              at java.lang.Thread.run(Thread.java:748)
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
              at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
              at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
              at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
              at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.purge(JdbcStringBasedStore.java:467)
              ... 14 more
      

      The method initSelectOnlyExpiredRowsSql in AbstractTableManager includes a FOR UPDATE:

      return String.format("%1$s WHERE %2$s < ? AND %2$s > 0 FOR UPDATE", getLoadAllRowsSql(), config.timestampColumnName());
      

      It should probably be overwritten in SQLServerTableManger with

      return String.format("%1$s WITH(UPDLOCK) WHERE %2$s < ? AND %2$s > 0", getLoadAllRowsSql(), config.timestampColumnName());
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  ryanemerson Ryan Emerson
                  Reporter:
                  mvandevalk Megan van de Valk
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: