Getting Deadlock in MySQLSelectForUpdateBasedProxyManager.tryInsertEmptyData method

[saikatkar]

We are using Bucket4j to implement throttling in our microservice. Recently we noticed few requests failed for deadlock in the library. After initial diagnosis we found out its coming from MySQLSelectForUpdateBasedProxyManager.tryInsertEmptyData () method.
We are using MySQL 8.0 and Bucket4j-core and Bucket4j-mysql 8.1.0. We are using a combination of 3 parameters configured as the throttling key.All the requests which failed because of deadlock were having the identical throttling key.
I checked the code of tryInsertEmptyData(). Its using an "Insert Ignore" statement. Can this be a potential reason for deadlock because of the usage of Gap Lockin mysql innodb engine?
I am sharing the exception stacktrace as well here.

j.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction at o.m.j.i.p.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1695) at o.m.j.i.p.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1557) at o.m.j.i.p.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1520) at o.m.j.i.p.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318) ... 192 common frames omitted Wrapped by: o.m.j.i.u.e.MariaDbSqlException: Deadlock found when trying to get lock; try restarting transaction at o.m.j.i.u.e.MariaDbSqlException.of(MariaDbSqlException.java:34) at o.m.j.i.p.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194) at o.m.j.i.p.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177) at o.m.j.i.p.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321) at j.i.r.GeneratedMethodAccessor52.invoke(Unknown Source) at j.i.r.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at j.l.reflect.Method.invoke(Method.java:568) at o.m.j.i.f.i.MastersReplicasListener.invoke(MastersReplicasListener.java:233) at o.m.j.i.f.FailoverProxy.executeInvocation(FailoverProxy.java:301) at o.m.j.i.f.FailoverProxy.invoke(FailoverProxy.java:294) at j.proxy2.$Proxy113.executeQuery(Unknown Source) at j.i.r.GeneratedMethodAccessor52.invoke(Unknown Source) at j.i.r.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at j.l.reflect.Method.invoke(Method.java:568) at o.m.j.i.l.ProtocolLoggingProxy.invoke(ProtocolLoggingProxy.java:103) at j.proxy2.$Proxy113.executeQuery(Unknown Source) at o.m.j.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220) ... 179 common frames omitted Wrapped by: j.s.SQLTransactionRollbackException: (conn=266912) Deadlock found when trying to get lock; try restarting transaction at o.m.j.i.u.e.ExceptionFactory.createException(ExceptionFactory.java:76) at o.m.j.i.u.e.ExceptionFactory.create(ExceptionFactory.java:158) at o.m.j.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:266) at o.m.j.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229) at o.m.j.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149) at o.m.j.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:181) at c.z.h.p.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) at c.z.h.p.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) at i.g.b.m.MySQLSelectForUpdateBasedProxyManager$1.tryInsertEmptyData(MySQLSelectForUpdateBasedProxyManager.java:150) ... 174 common frames omitted Wrapped by: i.g.b.BucketExceptions$BucketExecutionException: java.sql.SQLTransactionRollbackException: (conn=266912) Deadlock found when t...

Any help is much appreciated.

[vladimir-bukhtoyarov]

Hello @saikatkar

1. You use MySQLSelectForUpdateBasedProxyManager with MariaDB JDBC driver. This configuration was never tested because, there is dedicated integration for MariaDB try to switch to this dependency if you use MariaDb.

2. How did you activate Gap locking? Did you configure configure default transaction level at datasource levele that deffers from READ COMMITTED? If so, that looks that I need to add loggic to MySQLSelectForUpdateBasedProxyManager that will be responsible to set READ COMMITTED isolation level(that explicitly disables gap locking) after taking connection from datasource, and restore previous isolation level before returning transaction to datasource.

[saikatkar]

thanks for prompt reply @vladimir-bukhtoyarov
The default isolation level for MySQL is REPEATABLE READ
https://dev.mysql.com/doc/refman/8.4/en/set-transaction.html#:~:text=The%20default%20isolation%20level%20is,isolation%20levels%2C%20see%20Section%2017.7.

[vladimir-bukhtoyarov]

I am sure that reason is in usage inapropriate dependency. MariaDBSelectForUpdateBasedProxyManager has specific branch to handle such cases, so you need to switch to bucket4j-mariadb as I mentionned above

[saikatkar]

Thanks for the prompt response @vladimir-bukhtoyarov.
We are trying with your proposed solution