EndExecuteReader not catching "Lock Wait timeout exceeded" exception

EndExecuteReader not catching "Lock Wait timeout exceeded" exception

Postby kazachok » Fri 21 Sep 2007 13:59

HI everyone.
I am having the problem with EndExecuteReader again.
This issue was fixed in the version 3.50, but it came back.
Here is the topic from a year ago:
http://www.crlab.com/forums/viewtopic.p ... cutereader

There is a little difference to it now though, it works fine when you lock the 1st child, but it doesn't work when you lock the second child.

Here is how to reproduce:
1.Create records:
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;


2. On the client 1 lets lock the child 2:

Code: Select all
SET AUTOCOMMIT=0;
BEGIN;
SELECT * FROM childtable WHERE childid=2 FOR UPDATE;


3. Now, the Child 2 is Locked.

4. Run the Test Project that I provided.
Let's try to Select for UPDATE parent with parentid=1. Keep in mind that Child 2 is locked on the client 1.
Once the Form is shown, type 1 in the "Primary Key" field.
Click "Try to Update" button.
(NOTE: my 'innodb_lock_wait_timeout' variable is set to 10 seconds)

After 10 seconds the 'innodb_lock_wait_timeout' occurs, the EndExecuteReader(result) is called, where the Exception should have been raised that the 'lock_wait_timeout' occured.
Instead, it creates the reader and reader.HasRows equals true and reader.Read() is true because there was one row that was fetched.
And only when you try to do reader.Close() it raises "Index was outside the bounds of the array." exception.


// *********************************
If you Lock the Child 1 in step 3, the 'innodb_lock_wait_timeout' willbe caught in EndExecuteReader() call.

Please help us out, our Product was released erlier this week into prduction.
We desperately need the fix for this issue.


I will send the test project to you in a few minutes.

My System is XP Pro SP2, VS2005 C#, MyDirect 4.2.18[/code], MySql Server is 5.0.44 Commercial using innodb storage engin.

Please let me know if you need any help from my side.

Thanks.

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

Postby Alexey » Mon 24 Sep 2007 12:55

We are investigating your project.
Look forward to hearing from us again soon.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Serious » Mon 24 Sep 2007 14:51

We fixed the problem with fetching error message on MySqlDataReader.Close().

Concerning MySqlCommand.EndExecuteReader() behaviour, row level locking mechanism of InnoDB storage engine checks status of every row only when it is used by the query. MySQL Server reads rows sequentially (depending on query optimizer state), so state of row #2 (which is locked by the first client) is checked only after the row #1 was fetched and reader.Read() was successful. The next invocation of reader.Read() or reader.Close() will fail, because MySQL Server tries to access the locked row #2.

To avoid this problem you may restrict number of records fetched (with LOCK clause, for example), use MySqlCommand.FetchAll=true (with all advantages and disadvantages of this mode), or fetch all records of the result set in your code. MySqlCommand.FetchAll=true is the only way to retrieve an error message in the MySqlCommand.EndExecuteReader().
Serious
 

Postby kazachok » Mon 24 Sep 2007 18:01

Thank you for the quick response.

MySqlCommand.FetchAll will do the job, I just set that to true and the exception is raised, however,
I just simulated the same scenario using two mysql command line clients and regardless of the sequential read, the exception is raised eventhough the record of child # 1 is not locked.

Although, when I use the MySQL Query Browser, I get both outcomes:
1. The Lock Wait Timeout exceeded exception is raised
And
2. The first child row is fetched.

So, honestly, I think that the Exception should be raised first, and then based on the fetched rows in the Reader (if any) let the user decide to lock the fetched rows or not.

Please let me know if you are planning on addressing this behavior or not.
You, guys have a great product, and support for it.

Will be waiting for your response.

Thank you for your help and consideration.
Good day.

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

Postby Alexey » Tue 02 Oct 2007 08:16

We have answered you by email. Please check your box.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL