MySQLDirect BeginExecuteReader Issue

MySQLDirect BeginExecuteReader Issue

Postby kazachok » Thu 02 Mar 2006 21:33

Hello everyone,

Here is my environment:
1. MySQL Server 5.0.18 with InnoDB storage engin
2. VS2005 (.NET 2.0) C# in Windows Forms application
3. Windows XP Pro SP2
4. MySqlDirect version 3.20.9,
(Also Just installed 3.50 Beta and problem persist)

I am having a problem with asynchronization on BeginExecuteReader when there are triggers involved for a given table.
Here is a basic concept:
Code: Select all
if (mySqlConnection1.State != ConnectionState.Open) {
   mySqlConnection1.Open();
}

DbCommandBase myCommand = new CoreLab.MySql.MySqlCommand("SELECT * FROM table1 WHERE id = " + recordID + " FOR UPDATE", this.mySqlConnection1, myTransaction);
DbDataReader myReader = null;
try {
   IAsyncResult result = myCommand.BeginExecuteReader(null, null, System.Data.CommandBehavior.SingleRow);
   while (!result.IsCompleted) {
       Console.Write(".");
   }
   myReader = myCommand.EndExecuteReader(result);
   while (myReader.Read() && myReader.HasRows ) {
       this.textBox1.Text = myReader.GetInt32(myReader.GetOrdinal("id")).ToString();
       this.textBox2.Text = myReader.GetString(myReader.GetOrdinal("name"));
       this.textBox3.Text = myReader.GetString(myReader.GetOrdinal("description"));
   }
}
catch (MySqlException mysqlEx) {
    mySqlConnection1.Rollback();
    MessageBox.Show(mysqlEx.Message);
}



Here is what I am trying to acheive when a two users are trying to update the same record in a WindowsForm:
User # 1 retrieves the record, locks it untill he clicks OK button to close the form.
User # 2 tries to get that record but it is locked by User # 1, so he waits untill the User # 1 releases the record or a message is displayed that that record is already locked by another user (User # 1).

In order to do that I have to use Asynchronous execution.

And when User # 1 and User # 2 are getting different records for Update, it works as well, because those different records are not locked.

It works great when there are no triggers associated with the table that contains that given record. User # 2 is notified that the record is locked when the "innodb_lock_wait_timeout" occurs. Also if during that timeout period the User # 1 releases that record, then the User # 2 aquires is for update.

However, when there are triggers for that table, and two users are trying to aquire DIFFERENT records for Update the IAsyncResult is always set to Not Completed -> (result.IsComplete always returns FALSE) thus does not allow another user to update any records from that table if one of the users has already locked only One (Different) record.

As soon as I drop the triggers for that table everything works like a charm.

I simulated the same scenario using command line client MySQLBrowser and everything works the way it should (with triggers and without triggers).

Please help me out with this, this is very important.

Please see below the tables and triggers defs.:

Code: Select all
CREATE TABLE test.table1 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  description VARCHAR(254),
  PRIMARY KEY (id),
  UNIQUE INDEX namekey USING BTREE (name)
)
AUTO_INCREMENT=1
CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE test.table2 (
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  description VARCHAR(255),
  PRIMARY KEY (id),
  UNIQUE INDEX name USING BTREE (name)
)
AUTO_INCREMENT=1
CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TRIGGER test.trg_delete_table1 BEFORE DELETE
ON test.table1 FOR EACH ROW BEGIN
     DELETE FROM table2 WHERE table2.id = OLD.id;
END;


CREATE TRIGGER test.trg_insert_table1 AFTER INSERT
ON test.table1 FOR EACH ROW BEGIN
   INSERT INTO table2(name, description)
   VALUES (NEW.name, "Generated Description via trigger");
END;

CREATE TRIGGER test.trg_update_table1 AFTER UPDATE
ON test.table1 FOR EACH ROW BEGIN
   UPDATE table2 SET name = NEW.name, description = NEW.description
   WHERE table2.id = NEW.id;
END;



How to simulate:
1. Create the Database with table1 and table2, triggers (see above).
2. Insert 2 records into table1:
Code: Select all
insert into table1(name, description) values("Record1", "Description 1");
insert into table1(name, description) values("Record2", "Description 2");

3. You will see that there are two records in table2 as well.
4. When the Form is loaded BEGIN Transaction.
5. Try to SELECT for UPDATE Record1 from table1 with the Asynchronous mechansm (see C# code above). Don't close the Form yet (The record will be locked untill you perform the update and COMMIT.
6. Execute another instance of the application and try to do the same thing, but Retrieve the Record2.
7. You will see that it will not get the data and will issue Exception with code 1205 "Lock wait timeout exceeded" even though those records are completely different.

You can also then drop the triggers and everything will work.

Need your help desperately, please.

P.S. let me know if you need a sample project (I have it, and can e-mail it to you)

Thanks,

Tim
kazachok
 
Posts: 25
Joined: Fri 21 Oct 2005 12:38

Postby Serious » Fri 03 Mar 2006 14:48

You have to increase MySqlCommand.Timeout value to be greater than innodb_lock_wait_timeout.
Serious
 

Postby kazachok » Fri 03 Mar 2006 15:00

Command Timeout is 30;
Connection Timeout is 15;
InnoDB Lock wait time out is 10 sec.

The issue is somewhere in DbDataReader because it works just fine when there are not triggers for that table.

Let me know if you need the Sample Project that shows that issues.

Thanks.

Kaz
kazachok
 
Posts: 25
Joined: Fri 21 Oct 2005 12:38

Postby Guest » Tue 07 Mar 2006 02:38

Ladies and Gentlemen,

Please let me know if you experience the same problem with this driver.
So far I have not recieved any responce yet.

Dear CoreLab, could you please let me know if you can reproduce it or not.
Because my project is stuck right now.

Let me know if you need a sample project, and where to send it.

Thank you for your support and consideration.

Kaz (Tim)
Guest
 

Postby Serious » Tue 07 Mar 2006 16:51

Please send us your test project and MySQL Server configuration file (exclude any confidential information if need). Specify also steps to reproduce the problem with MySQL command line client. Please use e-mail provided in the "readme" file.
Serious
 

Postby Serious » Thu 09 Mar 2006 13:11

We reproduced the problem easily after you sent us the example. You did not specify that you invoke "set autocommit=0" query.

Asynchronous execution is not the reason of error, it is reproducible using synchronous methods. The same behavior as MySQLDirect .NET have both mysql command-line client (5.0.18 ) and MySQL Query Browser (1.1.20 ).

I executed following commands to reproduce the error (I changed "id" each time):
Code: Select all
set autocommit=0;
start transaction;
select * from table1 where id=1 for update;
We think this behavior is caused by MySQL Server.
Serious
 

Postby Guest » Thu 09 Mar 2006 17:40

Thank you very much for the responce and help.
I confirm that I was able to simulate the same problem with autocommit=0.

I contacted MySQL support on this issue, and they verified the bug.
They are working on it right now (I have Silver Support from them).

I will post the results as soon as this problem is solved.

Thank you for your time and consideration.
Guest
 

Postby kazachok » Mon 13 Mar 2006 12:33

Hello everyone,
This issue is fixed on MySQL server side with the new release 5.0.19

Thank you to all for your time and consideration.

Kaz
kazachok
 
Posts: 25
Joined: Fri 21 Oct 2005 12:38


Return to dotConnect for MySQL