[BUG] Deadlock found when trying to get lock

[jamesfredleyallied]

Contact Details

JobRunr Version

5.3.2

JDK Version

Amazon Corretto 11.0.13

Your SQL / NoSQL database

AWS RDS MariaDB 10.6.10

What happened?

Would not expect a deadlock, unless a transaction was started and either not committed or rolled back at the end.

This happened on 2 of our 4 running instances.

If a deadlock occurs, do not shutdown, attempt to recover, like when database connectivity fails.

How to reproduce?

Not sure.

Run multiple instance of 5.3.2 connected to the same database, at the same time.

Relevant log output

Dec  4 17:33:02 ip-172-31-71-183 web: 2022-12-04 17:33:02.311 ERROR --- [       Thread-5] org.jobrunr.server.BackgroundJobServer   : An unrecoverable error occurred. Shutting server down...
Dec  4 17:33:02 ip-172-31-71-183 web: org.jobrunr.storage.StorageException: java.sql.SQLTransactionRollbackException: (conn=104800) Deadlock found when trying to get lock; try restarting transaction
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.storage.sql.common.DefaultSqlStorageProvider.signalBackgroundJobServerAlive(DefaultSqlStorageProvider.java:97)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.storage.ThreadSafeStorageProvider.signalBackgroundJobServerAlive(ThreadSafeStorageProvider.java:72)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.server.ServerZooKeeper.signalBackgroundJobServerAlive(ServerZooKeeper.java:91)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.server.ServerZooKeeper.signalBackgroundJobServerAliveAndDoZooKeeping(ServerZooKeeper.java:76)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.server.ServerZooKeeper.run(ServerZooKeeper.java:49)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.storage.sql.common.db.Sql.insertOrUpdate(Sql.java:149)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.storage.sql.common.db.Sql.update(Sql.java:130)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.storage.sql.common.BackgroundJobServerTable.signalServerAlive(BackgroundJobServerTable.java:63)
Dec  4 17:33:02 ip-172-31-71-183 web: at org.jobrunr.storage.sql.common.DefaultSqlStorageProvider.signalBackgroundJobServerAlive(DefaultSqlStorageProvider.java:93)

[rdehuyss]

Duplicate of #275.

You are using the wrong StorageProvider: I would expect to see MariaDBStorageProvider. signalBackgroundJobServerAlive(...) instead of DefaultSqlStorageProvider.signalBackgroundJobServerAlive(...).

The Spring integration normally creates the correct StorageProvider based on the Datasource. Can you provide me with the start of your connection string?

[rdehuyss]

FYI - from the official MySQL & MariaDB docs:

Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.

That's a really nice way to provide an API 😄 .

[jamesfredleyallied]

@rdehuyss

Our connection strings all look like the following, except for the hostname. useBulkStmts=false was added for version 5.3.x

jdbc:mariadb://localhost:3306/aosc?useBulkStmts=false

And here is our JobRunrConfig

import org.jobrunr.jobs.mappers.JobMapper;
import org.jobrunr.storage.StorageProvider
import org.jobrunr.storage.sql.mariadb.MariaDbStorageProvider
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration

import javax.sql.DataSource;

@Configuration
class JobRunrConfig {

    @Autowired
    DataSource dataSource

    @Bean
    public StorageProvider storageProvider(JobMapper jobMapper) {
        MariaDbStorageProvider storageProvider = new MariaDbStorageProvider(dataSource)
        storageProvider.setJobMapper(jobMapper);
        return storageProvider;
    }
}

[rdehuyss]

Hi @jamesfredleyallied ,

is there a reason why you are creating the StorageProvider by hand? Normally, the jobrunr-spring-boot-starter does this all out-of-the-box for you.

[jamesfredleyallied]

@rdehuyss Our app is technically Grails on top of Spring Boot and for some reason jobrunr-spring-boot-starter did not connect to our datasource out of the box.

[rdehuyss]

I've added a fix that should solve this. Waiting for CI/CD to release.