Warnings about deadlocks when using MySQL storage and transport

[oleggolovkov]

Hello, I use Rebus for sagas and sometimes I am seeing logs like this:

An error occurred when attempting to receive the next message: MySql.Data.MySqlClient.MySqlException (0x80004005): Deadlock found when trying to get lock; try restarting transaction
   at MySql.Data.MySqlClient.MySqlStream.ReadPacketAsync(Boolean execAsync)
   at MySql.Data.MySqlClient.NativeDriver.GetResultAsync(Int32 affectedRow, Int64 insertedId, Boolean execAsync)
   at MySql.Data.MySqlClient.Driver.GetResultAsync(Int32 statementId, Int32 affectedRows, Int64 insertedId, Boolean execAsync)
   at MySql.Data.MySqlClient.Driver.NextResultAsync(Int32 statementId, Boolean force, Boolean execAsync)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlDataReader.CloseAsync(Boolean execAsync)
   at MySql.Data.MySqlClient.MySqlDataReader.DisposeAsync(Boolean execAsync)
   at MySql.Data.MySqlClient.MySqlDataReader.Dispose(Boolean disposing)
   at Rebus.MySql.Transport.MySqlTransport.Receive(ITransactionContext context, CancellationToken cancellationToken)
   at Rebus.Workers.ThreadPoolBased.ThreadPoolWorker.ReceiveTransportMessage(CancellationToken token, ITransactionContext context)

from Rebus.Workers.ThreadPoolBased.ThreadPoolWorker, unfortunately I do not have a way to reproduce and neither I understand all the consequences (like maybe it is just retried and everything is fine?), packages that I'm using:

<PackageReference Include="Rebus.MySql" Version="2.1.0" />
<PackageReference Include="Rebus.ServiceProvider" Version="7.0.0" />
<PackageReference Include="MySql.Data" Version="8.0.33" />

C# registration:

services.AddRebus(configure => configure
            .Logging(l => l.MicrosoftExtensionsLogging(loggerFactory))
            .Transport(t => t.UseMySql(DbConnection, "rebus_saga_messages", "MessageQueue"))
            .Sagas(s => 
            {
                s.StoreInMySql(WorkflowsDbConnection, "rebus_saga_data", "rebus_saga_data_index");
                s.EnforceExclusiveAccess();
            })
            .Routing(r => r.TypeBased().MapAssemblyOf<MessageBase>("MessageQueue")));

I use .NET 7 and MySQL 8.0.33. Is this something I should worry about or is there a way to gather more info?

[mookid8000]

Unfortunately I know basically nothing about MySQL, so I'm afraid I'm not of much help here. I assume this has got to do with Rebus' MySQL transport somehow using the wrong type of locks, or maybe you're running with the wrong isolation level? (does MySQL have something similar to SQL Server's "snapshot isolation"?)

[huysentruitw]

This issue should also be fixed with this PR so in version 2.2.0.

I "copied" the idea of the new v4 implementation to retry in case of deadlock.

[mookid8000]

Assuming this was fixed by #28