MSSQL and snaphost isolation issues

[cezarg1410]

HI,
I am using db-scheduler 10.5 version with Spring Boot 2.5.6.
Database which i use is Microsoft SQL Server 2019.
I use db scheduler autoconfiguration so basically the whole configuration of db-scheduler is just:

db-scheduler:
  threads: 5
  heartbeat-interval: 1m
  polling-interval: 300ms
  delay-startup-until-context-read: true

What's really important: for some other reasons we HAD to switch to Snaphost isolation in MSSQL. Unfortunately Spring Boot doesn't support that isolation so it had to be set on hikari connection level:

spring:
    hikari:
      transaction-isolation: 4096 #That means SNAPSHOT isolation - can't be set using Spring Boot

The problem is: from time to time i can see the following errors in the logs:

Exception in thread "*****************" com.github.kagkarlsson.jdbc.SQLRuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.scheduled_tasks' directly or indirectly in database '**************************' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
	at com.github.kagkarlsson.jdbc.JdbcRunner.translateException(JdbcRunner.java:126)
	at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:93)
	at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:140)
	at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:66)
	at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:54)
	at com.github.kagkarlsson.scheduler.jdbc.JdbcTaskRepository.pick(JdbcTaskRepository.java:234)
	at com.github.kagkarlsson.scheduler.FetchCandidates$PickDue.call(FetchCandidates.java:120)
	at com.github.kagkarlsson.scheduler.FetchCandidates.lambda$run$2(FetchCandidates.java:84)
	at com.github.kagkarlsson.scheduler.Executor.lambda$addToQueue$0(Executor.java:52)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.scheduled_tasks' directly or indirectly in database '***************' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:515)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:85)
	... 10 more

So the easiest way to solve it would be to NOT use Snapshot isolation for db-scheduler tasks.
It could be done by creating another DataSource just for db-scheduler. Like that:

    @Bean
    Scheduler scheduler(List<Task<?>> schedulerTasks, DataSource dataSource) {
        DataSource schedulerDataSource = copyDataSource(dataSource);

        return new SchedulerBuilder(schedulerDataSource, schedulerTasks).build();
    }

    DataSource copyDataSource(DataSource dataSource) {
        HikariDataSource hds = (HikariDataSource) dataSource;
        HikariConfig hikariConfig = new HikariConfig();
        hds.copyStateTo(hikariConfig);
        hikariConfig.setTransactionIsolation(IsolationLevel.TRANSACTION_READ_COMMITTED.toString());

        return new HikariDataSource(hikariConfig);
    }

But: the problem is that with that approach i will lose transactionality for db-scheduler (because i will have two separate DataSource's).
Maybe anybody had similar problem and is able to tell me how could i solve that issue?

[kagkarlsson]

I am not very familiar with Snapshot isolation, but from what I can tell it does not look to be compatible with the default locking mechanism of db-scheduler, optimistic locking. There is another locking mechanism (select for update) but it is currently only implemented for postgres. It is high up on the roadmap to support this for all databases supporting SKIP LOCKED. I think that would work for you.

Are you using transactions that span schedulerClient operations (such as runtime scheduling of tasks) and other database operations? If not you will not lose anything by going for a separate datasource until SELECT FOR UPDATE locking is in place for mssql

[cezarg1410]

Thank You for Your response!
You are probably right: that is also my thought that snapshost isolation collides with optimistic locking. Unfortunately Transaction which is created with Snaphost isolation will immediately fail if any two threads will try to modify the same row.

I saw that You've already created an issue for the SELECT FOR UPDATE approach (#264) - i used it in one project with PostgreSQL database and it worked flawlessly. So really can't wait for that one!

Answering Your last question: unfortunately i have to schedule and cancel tasks during operations which involves another db operations and are executed in Transactions - but fortunately it is not a critical issue becuase i am fine with implementing error handling in those tasks which could be scheduled as a part of eventually rolled-back transaction. So i will probably go with that approach (creating second Datasource).

The last thing is - autoconfiguration which normally creates Scheduler bean is quite complex - to be sure that i will create Scheduler manually exactly as autoconfiguraiton is doing - i will have to probably copy&paste all that code into mine...

[kagkarlsson]

I saw that You've already created an issue for the SELECT FOR UPDATE approach (#264) - i used it in one project with PostgreSQL database and it worked flawlessly. So really can't wait for that one!

Yeah, I have started working on it, but put on hold to work on serialization.

The last thing is - autoconfiguration which normally creates Scheduler bean is quite complex - to be sure that i will create Scheduler manually exactly as autoconfiguraiton is doing - i will have to probably copy&paste all that code into mine...

Is there something that could be done to the autoconfiguration that would make it easier for you?

[cezarg1410]

I didn't notice Your comment. Sorry.
In the meantime i successfully created another Datasource - i studied autoconfiguration a lot, chose what i needed, then created custom configuration and it seems to work at the moment.
So looks like issue can be closed with that workaround - looking forward for lock-and-fetch :)

[kagkarlsson]

🎉 This issue has been resolved in v13.0.0 (Release Notes)