Deadlocks and "Too many connections"

This issue is strictly related to #56 and #57 but this time I have some answers.

Symptoms

When scheduling tasks from many threads MySQL may trow deadlock exception (on inserts I guess). For some reason though it leads to connection leaks and quickly we reach the point when connection pool is exhausted and whole process grinds to a halt.

Reproduction

It can be reproduced with this code:

using System;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

// ReSharper disable IdentifierTypo
// ReSharper disable StringLiteralTypo

namespace Hangfire.MySql.Deadlock
{
  class Program
  {
    private static int TID => Thread.CurrentThread.ManagedThreadId;
    private static DateTime Now => DateTime.Now;

    static void Main(string[] args)
    {
      // create database hangfire collate utf8_bin;
      var (db, uid, pwd) = ("hangfire", "mysql", "mysql");

      var connectionString = $"Server=localhost;Database={db};Uid={uid};Pwd={pwd}";
      var storage = new MySqlStorage(connectionString, new MySqlStorageOptions());
      var client = new BackgroundJobClient(storage);

      using (new BackgroundJobServer(storage))
      {
        var cancel = new CancellationTokenSource();

        Task Run() => Task.Factory.StartNew(
          () => Loop(cancel.Token, client, TimeSpan.Zero),
          TaskCreationOptions.LongRunning);

        var loops = Task.WhenAll(Run(), Run(), Run(), Run());

        Console.WriteLine("Press <enter> to stop...");
        Console.ReadLine();

        cancel.Cancel();
        loops.Wait(CancellationToken.None);
      }
    }

    private static void Loop(
      CancellationToken cancel, 
      BackgroundJobClient client, 
      TimeSpan interval)
    {
      Console.WriteLine($"Loop started @ [{TID}]");
      while (!cancel.IsCancellationRequested)
      {
        Thread.Sleep(interval);
        var now = Now;
        Console.WriteLine($">>> [{TID}] {now:s}");

        try
        {
          client.Schedule(() => Execute(now), now.AddSeconds(10));
        }
        catch (Exception e)
        {
          Console.WriteLine($"{e.GetType().Name}: {e.Message}\n{e.StackTrace}");
        }
      }
    }

    public static void Execute(DateTime then) => 
      Console.WriteLine($"<<< [{TID}] {then:s} ({Now.Subtract(then).TotalSeconds}s)");
  }
}

It schedules some simple task using four threads at "full" speed (interval = 0). From time to time it throws deadlock exception and at some point just stops. It seems like it exhaust all connections in connection pool. It wakes up from time to time to enqueue 1 or 2 tasks but hangs after that. I was thinking that it reuses some connections released by GC, but this is just hypothesis.

Workaround

Adding a lock (mutex/semaphore) around adding a task so there are no two client.Schedule(...); executing at the same time. Seems like fixes deadlock problem but also keeps number of connections at reasonable level. Although, I would still expect problem with multiple processes.

arnoldasgudas / Hangfire.MySqlStorage