Lock wait timeout exceeded from insert

[satuday]

Hi, recently I switched my project from Connector/net to MySqlConnector, but then I notices I'm getting "MySql.Data.MySqlClient.MySqlException (0x80004005): Lock wait timeout exceeded; try restarting transaction" from some of the insert statements within a TransactionScope. After 24 hrs I'd decided to switch back to connector/net and the error no longer show up.
Any idea why this is happening?

[bgrainger]

It may be because MySqlConnector implements TransactionScope differently.

Can you provide more details about how your transactions, connections, etc. are structured/nested?

[bgrainger]

See also #254.

[satuday]

yes, they are nested. I have a service that uses several other services to perform some insert and update. At the top level I created a transactionScope and within it call all the insert and update operations, but within the individual insert and updates there could be another transactionScope with scope.Complete(), but all Scope should be using the ambient transaction because they are all created from the same method like this.

var transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
transactionOptions.Timeout = TransactionManager.MaximumTimeout;
return new TransactionScope(TransactionScopeOption.Required, transactionOptions,
TransactionScopeAsyncFlowOption.Enabled);

And I have Pooling=false in connection string.

[bgrainger]

Thanks. A small sample that reproduces the problem would be ideal, but time-permitting I'll see if I can reproduce what you're seeing based on your description.

[bgrainger]

I can reproduce a hang "lock wait timeout exceeded" when a new connection in a TransactionScope UPDATEs a row INSERTed on a previous connection within the same scope. I wonder if this is because MySQL Server sees two different connections trying to modify the same row and doesn't know how to resolve the conflict.

using (var connection = new MySqlConnection(connectionString))
{
	connection.Open();
	connection.Execute("drop table test; create table test(rowid integer not null auto_increment primary key, value text);");
	connection.Execute("insert into test(value) values('one'),('two'),('three');");
}

using (var scope = CreateScope())
{
	using (var connection = new MySqlConnection(connectionString))
	{
		connection.Open();
		connection.Execute("insert into test(value) values('four'),('five'),('six');");
	}

	using (var connection = new MySqlConnection(connectionString))
	{
		connection.Open();

		// succeeds for id=2, lock wait timeout for id=4
		// succeeds (for either value) with Connector/NET
		connection.Execute("update test set value = @newValue where rowid = @id", new { newValue = "new value", id = 4 });
	}

	using (var connection = new MySqlConnection(connectionString))
	{
		connection.Open();
		connection.Execute("insert into test(value) values(@value);", new { value = "seven" });
	}

	scope.Complete();
}

TransactionScope CreateScope()
{
	var transactionOptions = new TransactionOptions();
	transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
	transactionOptions.Timeout = TransactionManager.MaximumTimeout;
	return new TransactionScope(TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled);
}

[bgrainger]

I suspect the right guidance here is "Don't open multiple connections to the same database within a TransactionScope"; that seems like something that's prone to transaction deadlock.

However, for backwards compatibility with Connector/NET, we probably need to implement #254 so code can be migrated without a rewrite.

I think this is working as designed, so I'm going to close this issue. However, if someone wants to make an argument that XA Transactions should not work this way, I'm open to feedback.

(One possibility is that we could detect an existing ServerSession associated with the XA Transaction and associate it with a new MySqlConnection under the hood when a second connection is opened within a TransactionScope; however, the OP says they're using Pooling=false.)

[Sp1rit]

I have the same issue, except that I have pooling enabled.

"Don't open multiple connections to the same database within a TransactionScope"

makes using TransactionScope releativley useless don't you think? If I have to pass the connection to all other methods to make it work I could use a simple transaction and set it inside the connection.

[bgrainger]

The purpose of TransactionScope is for distributed transactions. The example in MSDN “[creates] a transaction involving two SQL Servers”. It is perfectly suited to this task; you could use TransactionScope to create a transaction involving SQL Server and MySQL, or two different MySQL databases.

The issue arises when opening two different conflicting SQL statements in two different connections to the same MySQL database within a TransactionScope. This causes a deadlock and MySQL will terminate one of the connections within the transaction.

It's possible that this is a MySQL Server bug; I'm not sure what the right behaviour is here for XA Transactions. (I haven't tested SQL Server or Postgres to compare.) I'm happy for someone who's an expert on distributed transactions to chime in and explain what the expected behaviour should be.

[bgrainger]

With System.Transactions, a common pattern (with Connector/NET) is:

using (var transactionScope = new TransactionScope())
{
	using (var conn = new MySqlConnection(connectionString))
	{
		conn.Open();
	}

	using (var conn = new MySqlConnection(connectionString))
	{
		conn.Open();
	}

	transactionScope.Complete();
}

Connector/NET reuses the same physical connection to the server behind the scenes. MySqlConnector currently uses two different physical connections, which is prone to deadlock (see above).

We can continue using XA Transactions but avoid the risk of deadlock if we reuse the same ServerSession whenever possible within a TransactionScope. This should fix the major problems people run into when porting Connector/NET code with TransactionScope to MySqlConnector. (#254 will remain open to provide a way to completely opt out of XA Transactions for code that's incompatible with them.)

[Timovzl]

@bgrainger Does this change also make the code fix or workaround described here unnecessary?
https://mysql-net.github.io/MySqlConnector/troubleshooting/transaction-usage/