Deadlocks

[Skyedra]

Hello,

First, thank you for the library, it has been very useful to me :)

My question is in regards to deadlocks. In the case there is a deadlock, and mysql server decides that this connection loses, what does this look like to the application? (ie: is an exception supposed to be thrown? It doesn't seem to be for me, but I could be doing something wrong).

Thank you

[bgrainger]

Yes, for an XA transaction, you'll get an exception similar to the following:

MySqlConnector.MySqlException (0x80004005): XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected

For a regular transaction, you'll get an exception similar to:

MySqlConnector.MySqlException: Deadlock found when trying to get lock; try restarting transaction

[Skyedra]

Thank you! With this information, I realized my problem is actually not directly related to deadlocks, but no exception is being raised (even due to syntax error, etc). I eventually came to the realization that the ordering of try/using seems to be important for catching the exception.

IE this exception is not caught:

private async Task<bool> UpdateSessionInDatabase(...)
{
	try
	{
		using (var command = new MySqlCommand(updateString, Database.db))
		{
			// ...
			int result = await command.ExecuteNonQueryAsync();
			// ...
		} 
	} catch (Exception ex)
	{
		// This does NOT trigger
		Debug.WriteLine("Exception: " + ex.Message);
	}
}

However, by swapping the ordering of try vs using, this exception is caught:

private async Task<bool> UpdateSessionInDatabase(...)
{
	using (var command = new MySqlCommand(updateString, Database.db))
	{
		try
		{
			// ...
			int result = await command.ExecuteNonQueryAsync();
			// ...
		}
		catch (Exception ex)
		{
			// This DOES trigger
			Debug.WriteLine("Exception: " + ex.Message);
		}
	} 
}

Do you know, is this expected or something weird with my code?

[bgrainger]

I think something is weird with your code. I can't reproduce this problem:

class Program
{
	static async Task Main(string[] args)
	{
		using var connection = new MySqlConnection("Server=localhost;Uid=root;Pwd=pass;");
		await connection.OpenAsync();

		var program = new Program();
		var result = await program.UpdateSessionInDatabase(connection);
		Console.WriteLine("Result: " + result);
	}

	private async Task<bool> UpdateSessionInDatabase(MySqlConnection db)
	{
		try
		{
			using (var command = new MySqlCommand("bad sql", db))
			{
				int result = await command.ExecuteNonQueryAsync();
				return true;
			} 
		} catch (Exception ex)
		{
			// This does NOT trigger
			Console.WriteLine("Exception: " + ex.Message);
			return false;
		}
	}
}

prints:

Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bad sql' at line 1
Result: False

If you can produce a small repro that shows that MySqlConnector doesn't throw exceptions, please open a new issue with that code.

[Skyedra]

Thank you; yep, it was my code indeed

Just FYI if you are curious, it was a combination of three issues:

1. Async methods that aren't awaited don't get exceptions handled in the expected place due to how .net translates exception handling in async functions -- ie, this:

If you do not wait on a task that propagates an exception, or access its Exception property, the exception is escalated according to the .NET exception policy when the task is garbage-collected.

2. vscode debugger not consistently printing exception debug output when they happen at end of execution of an async program (I think related to C#'s exception state machine translation happening above)
3. Simple mistake of accidentally using Debug.WriteLine instead of Console.WriteLine and so thinking exceptions weren't getting called in production

My program now seems to be working well after:

1. Implementing retry logic and try/catches (to handle deadlock)
2. Changing main method to be async and use await (so exceptions are caught)
3. Double checking I had no async voids, as those also wouldn't handle exceptions
4. Fixing debug.writeline -> console.writeline so exception output would be shown in production

Thanks again for your help and tips while I learned up about all of this :)

[naughtyGitCat]

@bgrainger in my application, logging shows .....MySqlConnector.MySqlException (0x80004005): Deadlock found when trying to get lock; try restarting transaction at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(.....

I want to catch this situation and retry reexecute the sql or just ignore

Is there any example to instruct catch this exception precisely? the 0x80004005 seems to be a hex number, not the part of the MySqlException(int Number, MySqlErrorCode ErrorCode), the Deadlock found when trying to get lock; try restarting transaction is the MySqlException(string? SqlState) ?

so when try to catch this exception, the best way is check ex.SqlState == "Deadlock found when trying to get lock; try restarting transaction"?

[bgrainger]

You should prefer to test the (numeric) .ErrorCode property, instead of checking .SqlState, which is an arbitrary string from the server (although likely to be predictable in practice). E.g.,

catch (MySqlException ex) when (ex.ErrorCode == MySqlErrorCode.LockDeadlock)
{
    // code here
}

[naughtyGitCat]

thx, bro, you saved my time 🤠