Many Different Results Sets Deadlock Test Case

[Tim Smith]

Reggie,

I've found a test case that fails when selecting mutiple result sets. There is a deadlock somewhere along the way. I'm assuming there is a lock being placed on something that is already locked. I haven't looked at the code but thats what appears to happen since CPU utilization and memory remain flat. This also as expected fails when filling a data adapter. I haven't created a test case but did experience this same behavior.

Also, there is an issue with FieldCount. In my tests it returns 5 after the third result set for the following 4 result sets. I also tried creating the table as InnoDb just to see if anything changed. The deadlock occurs either way.

I haven't tested it yet but just wanted to ask, if I add "RoundZeroDatetime=true" to my connection string will this make Connector/Net return DateTime objects instead of MySqlDateTime objects so I don't have to do a .ToString().

Below is my test case. Let me know if it fails for you or for anyone else that wants to test it.

[Test()]
public void TestManyDifferentResultsets()
{
MySqlDataReader reader =null;
try
{
MySqlCommand cmd = new MySqlCommand("", conn);
// insert 100 records
cmd.CommandText = "INSERT INTO Test (id,name,dt,b1) VALUES (?id, 'test','2004-12-05 12:57:00','long blob data')";
cmd.Parameters.Add( new MySqlParameter("?id", 1));
for (int i=1; i <= 100; i++)
{
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}

cmd = new MySqlCommand("SELECT id FROM test WHERE id<?param1; "+
"SELECT id,name FROM test WHERE id = -50; "+
"SELECT * FROM test WHERE id >= ?param1; "+
"SELECT id, dt, b1 FROM test WHERE id = -50; "+
"SELECT b1 FROM test WHERE id = -50; "+
"SELECT id, dt, b1 FROM test WHERE id < ?param1; "+
"SELECT b1 FROM test WHERE id >= ?param1;", conn);

cmd.Parameters.Add("?param1",50);

reader = cmd.ExecuteReader();

Assert.IsNotNull( reader );

//First ResultSet, should have 49 rows.
//SELECT id FROM test WHERE id<?param1;
Assert.AreEqual( true, reader.HasRows );
Assert.AreEqual( 1, reader.FieldCount );
for (int i = 0; i < 49; i++)
{
Assert.IsTrue( reader.Read() );
}
Assert.AreEqual( false, reader.Read() );

//Second ResultSet, should have no rows.
//SELECT id,name FROM test WHERE id = -50;
Assert.IsTrue( reader.NextResult() );
Assert.AreEqual( false, reader.HasRows );
Assert.AreEqual( 2, reader.FieldCount );
Assert.AreEqual( false, reader.Read() );


//Third ResultSet, should have 51 rows.
//SELECT * FROM test WHERE id >= ?param1;
Assert.IsTrue( reader.NextResult() );
Assert.AreEqual( true, reader.HasRows );
Assert.AreEqual( 5, reader.FieldCount );
for (int i = 0; i < 51; i++)
{
Assert.IsTrue( reader.Read() );
}
Assert.AreEqual( false, reader.Read() );


//Fourth ResultSet, should have no rows.
//SELECT id, dt, b1 FROM test WHERE id = -50;
Assert.IsTrue( reader.NextResult() );
Assert.AreEqual( false, reader.HasRows );
//Assert.AreEqual( 3, reader.FieldCount ); //Will Fail if uncommented expected 3 returned 5
Assert.AreEqual( false, reader.Read() );

//Fifth ResultSet, should have no rows.
//SELECT b1 FROM test WHERE id = -50;
Assert.IsTrue( reader.NextResult() );
Assert.AreEqual( false, reader.HasRows );
//Assert.AreEqual( 1, reader.FieldCount ); //Will Fail if uncommented expected 1 returned 5
Assert.AreEqual( false, reader.Read() );

//Sixth ResultSet, should have 49 rows.
//SELECT id, dt, b1 FROM test WHERE id < ?param1;
Assert.IsTrue( reader.NextResult() );
Assert.AreEqual( true, reader.HasRows );
//Assert.AreEqual( 3, reader.FieldCount ); //Will Fail if uncommented expected 3 returned 5
for (int i = 0; i < 49; i++)
{
Assert.IsTrue( reader.Read() );
}
Assert.AreEqual( false, reader.Read() );

//Seventh ResultSet, should have 51 rows.
//SELECT b1 FROM test WHERE id >= ?param1;
Assert.IsTrue( reader.NextResult() );
Assert.AreEqual( true, reader.HasRows );
//Assert.AreEqual( 1, reader.FieldCount ); //Will Fail if uncommented expected 1 returned 5
for (int i = 0; i < 51; i++)
{
Assert.IsTrue( reader.Read() );
}
Assert.AreEqual( false, reader.Read() );

reader.Close();

cmd.CommandText = "DELETE FROM Test WHERE id>=0";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Assert.Fail( ex.Message );
}
finally
{
if (reader != null) reader.Close();
}
}