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");
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