Devart Forums

Posted: **Wed 16 Aug 2006 22:06**

Hello everyone.
I am using MySQLDirect 3.50.11 in Visual Studio 2005 in C# On Windows XP SP2; MySQL Server 5.0.21

The Issue I am having is the BeginExecuteReader();
Look at the code below:

Code: Select all

try {
    DbDataReader myReader = null;
    IAsyncResult result = mySelectCommand.BeginExecuteReader(null, null, CommandBehavior.Default);
    if (!result.IsCompleted) {
        
        while(!result.IsCompleted){
            // Wait untill it is complete
            // you can do Polling in here.
        }
    }
    myReader = mySelectCommand.EndExecuteReader(result);
    if (myReader.HasRows && myReader.Read()) {
        myReader.Close();
        updateResult = DialogResult.OK;
    }
    else {
         // The record was deleted from the Database by a diferent user
         //throw MySqlException();
    }
}
catch (MySqlException ex) {
    MessageBox.Show(ex.Message);
}

When the while loop is running to check the state of the result.IsCompleted, it is finally reports that it is Completed, However, The Exception is not raised the the "Lock wait timeout exceeded, Try restarting the transaction".
NOTE: my "innodb_lock_wait_timout" is set to 10
I think, It should raise the Exception first, so that it could be caught.

So just by my luck, the Exception is raised on the

Code: Select all

myReader = mySelectCommand.EndExecuteReader(result);

after the result is Completed.

More issues I encounter when I am trying to use the mechanism above
(BeginExecuteReader()) to lock a parent Record in one table and all the Child records in the other table for update:

Code: Select all

myTransaction = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
DialogResult updateResult = DialogResult.Retry;
while (updateResult == DialogResult.Retry) {
	try {
	    DbDataReader myReader = null;
	    IAsyncResult result = mySelectCommand.BeginExecuteReader(null, null, CommandBehavior.Default);
	    if (!result.IsCompleted) {
	        while (!result.IsCompleted) {
	        Console.Write(".");
	    }
	    myReader = mySelectCommand.EndExecuteReader(result);
	    if (myReader.HasRows && myReader.Read()) {
	        myReader.Close();
	        updateResult = DialogResult.OK;
	    }
	}
	catch (MySqlException ex) {
	    // this MessageBox displays options to cancel or to Retry
	    // If you hit Retry button it will execute the mySelectCommand.BeginExecuteReader() again.
	    // and that is where the "Command out of sync" Exception is raised!
	    updateResult = MessageBox.Show(ex.Message, "Error occured", MessageBoxButtons.RetryCancel);
	}
}

If The Child Record is already locked by another Client and You hit Retry Button the next call to BeginExecuteReader will generate
"Commands out of sync" Exception.

And again, The Wait Timeout exceeded exception Should be raised before the ASyncResult is "Completed".

I am desperatly needing the resolution to this issue.

Thank you for your time and consideration.

Let me know if you need the Sample Project (VS2005 C#)
Also here is the Schema for the tables:

Code: Select all

USE test;

CREATE TABLE test.parenttable (
  parentid INT(11) NOT NULL AUTO_INCREMENT,
  parentname VARCHAR(20),
  PRIMARY KEY (parentid)
)
AUTO_INCREMENT=1
CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE test.childtable (
  childid INT(11) NOT NULL AUTO_INCREMENT,
  parentid INT(11) NOT NULL,
  childname VARCHAR(20),
  PRIMARY KEY (childid),
  INDEX childtable_FK1 USING BTREE (parentid),
  CONSTRAINT childtable_FK1 FOREIGN KEY (parentid)
    REFERENCES parenttable(parentid) ON DELETE CASCADE ON UPDATE CASCADE
)
AUTO_INCREMENT=1
CHARACTER SET utf8 COLLATE utf8_general_ci;


INSERT INTO parenttable(parentname) values("Parent 1");
INSERT INTO parenttable(parentname) values("Parent 2");
INSERT INTO parenttable(parentname) values("Parent 3");

INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 1" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 2" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 3" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 4" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 5" FROM parenttable t1 WHERE parentid = 1;

INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 1-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 2-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 3-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 4-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 5-2" FROM parenttable t1 WHERE parentid = 2;

INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 1-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 2-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 3-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 4-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 5-3" FROM parenttable t1 WHERE parentid = 3;

Posted: **Thu 17 Aug 2006 07:39**

Send us your project to reproduce the problem. Do not use third party components. Use e-mail address provided in the Readme file.

Posted: **Tue 22 Aug 2006 11:17**

Hello everyone,
I just got the 3.50.12 build and I am experiencing the same problem.

I sent you the test project a few days ago where you can reproduce the issue.
Were you able to see this problem?

I am desperately waiting for some type of a resolution to this.

Thank you for your time and consideration.

Tim

P.S.
Let me know if you need any other information.

Posted: **Tue 22 Aug 2006 11:52**

Unfortunately, i haven't received your test project yet. So i've sent you initial letter, please resend your project to my address.

Posted: **Sat 26 Aug 2006 16:51**

Hello,
I am just wondering about the status on this issue.

I understand, I have to be a little more patient, but my time constraints are very strict.

Thank you for your time and consideration.

Tim.
Good day.

Posted: **Wed 30 Aug 2006 06:32**

IAsyncResult.IsCompleted only indicates that command execution is complete. MySqlDataReader opens at the MySqlCommand.EndExecuteReader() method, so command execution result analysed there. This corresponds to the common .NET behaviour when you retrieve some action result (including exception) on EnxXXX() method.

As for "Command out of sync" exception. It occurs when you do not close all data readers explicitly (or if data reader's finalizer was not executed). Review your code to close explicitly all data readers (this is very important, use try ... finally or using constructions) and repeat testing, inform us about results.

Posted: **Wed 30 Aug 2006 16:32**

Hi, I utilized the try...catch...finally block, but the behavior of the EndExecuteReader or Command is still different in the different queries.

From my observation, the Exceptions are different based on the actual SQL query. This is very interesting.

Scenario #1:
A Child record is locked by "SELECT FOR UPDATE" query by Client # 1.
In the test project I provided (Client #2), you issue the query to "SELECT FOR UPDATE" on the same record in a single table to try to lock that record. So after 10seconds the Lock wait timeout exception will be raised. Then a message box is displayed with the Retry and Cancel button. if you Click the "retry" button, it will try to "SELECT FOR UPDATE" for that record again. In this scenario, you will always get the "Lock wait timeout exceeded" exception untill you either click "Cancel" button or the Client # 1 unlocks that record (by doing Commit or Rollback).

Scenario #2:
A Child record is locked by "SELECT FOR UPDATE" query by Client # 1.
In the test project I provided (Client #2), you issue the query to "SELECT FOR UPDATE" but using a Parent-Child relation to try to Lock the Parent record and all the Child records for this given Parent (there are two tables used using JOINs:

Code: Select all

SELECT * FROM parenttable JOIN childtable USING(parentid) WHERE parentid = 1 FOR UPDATE;

When the 10seconds are elapsed, the Exception is raised "Lock Wait timeout exceeded". Here you are prompted with a Message Box with Retry and Cancel buttons. If you click Retry button, the new exception will be raised: "Lost Connection to the Server during query" or "Commands outof sync".

The question is: Why the behavior of the Command/Reader is different?
Should it do the same thing in both scenarios?
In the Scenario # 2 it should continue rasing the Exception of "Lock wait timeout exceeded" after you click the Retry button, because it will try to execute the same query again, and the Child Record is still locked by the Client # 2.

Our project is using transactions and a lot of Parent Child Relations so we can ensure that you cannot Delete Parent record on one client while the other client is editing a Child Record that is referencing the Parent Record.

If I do the scenarios above using the mysql clients, the behavior is the same - you will get the "Lock wait timeout exceeded" no matter how many times you execute the query for UPDATE within the same transaction, and no matter if you are locking a Parent-Child records or a Single Child Record.

Your help is very needed and appreciated.
Thank you for your time and consideration.

Tim

Posted: **Thu 31 Aug 2006 10:03**

We have fixed this problem for the MySQLDirect .NET.
Look forward for the next build.

Devart Forums

BeginExecuteReader() with ASyncResult Not Raising the Exception

BeginExecuteReader() with ASyncResult Not Raising the Exception

BeginExecuteReader() with ASyncResult Not Raising the Exception