prevent mssqlserver deadlock

amergey commented 3 weeks ago

This PR fix potential deadlock occuring with sqlserver

(similar issue previously raised here https://github.com/quartz-scheduler/quartz/issues/700) Exception we can get with java version:

org.quartz.JobPersistenceException: Couldn't acquire next trigger: Transaction (Process ID 136) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [See nested exception: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 136) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2923)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2805)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2803)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3864)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTriggers(JobStoreSupport.java:2802)
    at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:287)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 136) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:473)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.updateTriggerStateFromOtherState(StdJDBCDelegate.java:1439)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2901)

If this PR is accepted, it should be also backported in 2.4.0 branch, how to proceed ?

Checklist

[x] tested locally
[ ] updated the docs
[x] added appropriate test
[x] signed-off on the DCO referenced in the CONTRIBUTING link below via git commit -s on my commits, and submit this code under terms of the Apache 2.0 license and assign copyright to the Quartz project owners (If you're not using command-line, you can use a browser extension )

In submitting this contribution, I agree to the terms of contributing as referred to here: https://github.com/quartz-scheduler/contributing/blob/main/CONTRIBUTING.md

melloware commented 3 weeks ago

Nice effort even to use TestContainers to prove it!

TarasTielkes commented 2 weeks ago

Will this improvement make it into the 2.4 release as well?

quartz-scheduler / quartz

prevent mssqlserver deadlock #1246

Checklist

[x] signed-off on the DCO referenced in the CONTRIBUTING link below via `git commit -s` on my commits, and submit this code under terms of the Apache 2.0 license and assign copyright to the Quartz project owners (If you're not using command-line, you can use a browser extension )

quartz-scheduler / quartz

prevent mssqlserver deadlock #1246

Checklist

[x] signed-off on the DCO referenced in the CONTRIBUTING link below via git commit -s on my commits, and submit this code under terms of the Apache 2.0 license and assign copyright to the Quartz project owners (If you're not using command-line, you can use a browser extension )

[x] signed-off on the DCO referenced in the CONTRIBUTING link below via `git commit -s` on my commits, and submit this code under terms of the Apache 2.0 license and assign copyright to the Quartz project owners (If you're not using command-line, you can use a browser extension )