Repository: kagkarlsson/db-scheduler (java)

Issue:
Polling - support select-for-update-skip-locked polling for mysql, mssql



Discussion:
Groundwork laid, but not prioritizing this anymore.

Patch:
diff --git a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MariaDBJdbcCustomization.java b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MariaDBJdbcCustomization.java
--- a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MariaDBJdbcCustomization.java
+++ b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MariaDBJdbcCustomization.java
@@ -49,6 +49,7 @@ public String createGenericSelectForUpdateQuery(
         Queries.postgresSqlLimitPart(limit),
         requiredAndCondition,
         " FOR UPDATE SKIP LOCKED ",
+        null,
         null);
   }
 }
diff --git a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MssqlJdbcCustomization.java b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MssqlJdbcCustomization.java
--- a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MssqlJdbcCustomization.java
+++ b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MssqlJdbcCustomization.java
@@ -66,6 +66,7 @@ public String createGenericSelectForUpdateQuery(
         Queries.ansiSqlLimitPart(limit),
         requiredAndCondition,
         null,
-        " WITH (READPAST,ROWLOCK) ");
+        " WITH (READPAST,ROWLOCK) ",
+        null);
   }
 }
diff --git a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MySQL8JdbcCustomization.java b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MySQL8JdbcCustomization.java
--- a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MySQL8JdbcCustomization.java
+++ b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/MySQL8JdbcCustomization.java
@@ -37,8 +37,7 @@ public String getQueryLimitPart(int limit) {
 
   @Override
   public boolean supportsGenericLockAndFetch() {
-    // FIXLATER: fix syntax and enable
-    return false;
+    return true;
   }
 
   @Override
@@ -48,7 +47,8 @@ public String createGenericSelectForUpdateQuery(
         tableName,
         Queries.postgresSqlLimitPart(limit),
         requiredAndCondition,
-        " FOR UPDATE SKIP LOCKED ",
-        null);
+        null,
+        null,
+        " FOR UPDATE SKIP LOCKED ");
   }
 }
diff --git a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/OracleJdbcCustomization.java b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/OracleJdbcCustomization.java
--- a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/OracleJdbcCustomization.java
+++ b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/OracleJdbcCustomization.java
@@ -40,6 +40,7 @@ public String createGenericSelectForUpdateQuery(
         Queries.ansiSqlLimitPart(limit),
         requiredAndCondition,
         " FOR UPDATE SKIP LOCKED ",
+        null,
         null);
   }
 }
diff --git a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/PostgreSqlJdbcCustomization.java b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/PostgreSqlJdbcCustomization.java
--- a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/PostgreSqlJdbcCustomization.java
+++ b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/PostgreSqlJdbcCustomization.java
@@ -57,6 +57,7 @@ public String createGenericSelectForUpdateQuery(
         getQueryLimitPart(limit),
         requiredAndCondition,
         " FOR UPDATE SKIP LOCKED ",
+        null,
         null);
   }
 
diff --git a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/Queries.java b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/Queries.java
--- a/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/Queries.java
+++ b/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/Queries.java
@@ -22,7 +22,8 @@ public static String selectForUpdate(
       String limitPart,
       String requiredAndCondition,
       String postgresOracleStyleForUpdate,
-      String sqlServerStyleForUpdate) {
+      String sqlServerStyleForUpdate,
+      String mysqlOracleStyleForUpdate) {
     return "SELECT * FROM "
         + tableName
         + Optional.ofNullable(sqlServerStyleForUpdate).orElse("")
@@ -30,7 +31,9 @@ public static String selectForUpdate(
         + requiredAndCondition
         + " ORDER BY execution_time ASC "
         + Optional.ofNullable(postgresOracleStyleForUpdate).orElse("")
-        + limitPart;
+        + limitPart
+        // Note: Compared to Postgres, MySQL expects `FOR UPDATE SKIP LOCKED` after `LIMIT`
+        + Optional.ofNullable(mysqlOracleStyleForUpdate).orElse("");
   }
 
   public static String postgresSqlLimitPart(int limit) {


Test patch:
diff --git a/db-scheduler/src/test/java/com/github/kagkarlsson/scheduler/compatibility/Mysql8CompatibilityTest.java b/db-scheduler/src/test/java/com/github/kagkarlsson/scheduler/compatibility/Mysql8CompatibilityTest.java
--- a/db-scheduler/src/test/java/com/github/kagkarlsson/scheduler/compatibility/Mysql8CompatibilityTest.java
+++ b/db-scheduler/src/test/java/com/github/kagkarlsson/scheduler/compatibility/Mysql8CompatibilityTest.java
@@ -24,7 +24,7 @@ public class Mysql8CompatibilityTest extends CompatibilityTest {
   private static HikariDataSource pooledDatasource;
 
   public Mysql8CompatibilityTest() {
-    super(false, false); // FIXLATER: fix syntax and enable
+    super(true, false);
   }
 
   @BeforeAll