Bug #86790 FTWRL continues to freeze the server even if it times out ...

• Submitted: 22 Jun 2017 9:18
• Modified: 22 Jun 2017 10:33
• Reporter: Arnaud Adant
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S2 (Serious)
• Version: 5.7.18
• OS: Any
• CPU Architecture: Any
• Tags: FTWRL

[22 Jun 2017 9:18] Arnaud Adant

Description:
I noticed one annoying thing with FTWRL.

If I run this in session 1

drop table if exists t;

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ;

set autocommit = 0;

insert into t values(1);

select sleep(10) from t;

Session 2 :

set lock_wait_timeout = 1;flush tables with read lock;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Back in session 1, there is a read lock although FTWRL failed ...
That's quite surprising. You think the statement failed but it is still active !

So this statement waits until UNLOCK TABLES is executed or session 2 is disconnected ...

insert t values (2);
Query OK, 1 row affected (15.76 sec)

How to repeat:
Session 1

drop table if exists t;

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ;

set autocommit = 0;

insert into t values(1);

select sleep(10) from t;

Session 2 :

set lock_wait_timeout = 1;flush tables with read lock;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session 1 :

notice that session 1 is frozen unless session 2 disconnects or UNLOCK TABLES.

insert t values (2);

Suggested fix:

Fix the code to unlock the tables on FTWRL errors.

[22 Jun 2017 9:18] Arnaud Adant

Fixed the title

[22 Jun 2017 10:33] MySQL Verification Team

Dear Arnaud,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

[22 Jun 2017 10:34] MySQL Verification Team

test results

Attachment: 86790_5.7.18.results (application/octet-stream, text), 3.04 KiB.