Description:
Hello,
I'd like to report following problem:
while executing query (SELECT .. FOR UPDATE) in transaction, the mysql process goes into 'statistics' state in which it hangs. Query is killed after some time, there is no lock waiting. I've updated MySQL to 5.0.45 revision (using fc7 repository), previous version was probably 5.0.12.
# Time:
# User@Host:
# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use www_extra;
SELECT * FROM `www_extra`.`restaurant_dates`
WHERE idx_edition = 'T10_10_01'
AND id = 2
FOR UPDATE;
mysql> show processlist;
| Query | 33 | statistics | SELECT * FROM `www_extra`.`restaurant_dates`
WHERE idx_edition = 'T10_10_01'
AND id = 2
FOR UPDATE |
mysql> show create table restaurant_dates;
| restaurant_dates | CREATE TABLE `restaurant_dates` (
`id` int(16) unsigned NOT NULL auto_increment,
`idx_edition` varchar(10) NOT NULL,
`meeting_date` datetime NOT NULL,
`end_meeting_date` datetime NOT NULL,
`tables_count` int(16) NOT NULL,
`persons_at_table` int(16) NOT NULL,
`current_max` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 |
no foreign keys, no joins, just simple relation.
I found another problems (http://bugs.mysql.com/bug.php?id=26339 and http://bugs.mysql.com/bug.php?id=20932) which looks like related, but no answer found in there.
Executed on 64bit.
[]# uname -a
Linux 2.6.21-1.3228.fc7 #1 SMP Tue Jun 12 14:56:37 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
How to repeat:
I was executing consecutive (not parallel) queries in separate transactions. Sometimes, I was in the same transaction updating selected row, sometimes not. Transaction was always finished with commit (if update performed) or rollback (if not).
i.e.
START TRANSACTION;
SELECT * FROM `www_extra`.`restaurant_dates`
WHERE idx_edition = 'T10_10_01'
AND id = 2
FOR UPDATE;
UPDATE `www_extra`.`restaurant_date` SET `current_max` = 3 WHERE `id` = 2;
COMMIT;
START TRANSACTION;
SELECT * FROM `www_extra`.`restaurant_dates`
WHERE idx_edition = 'T10_10_01'
AND id = 2
FOR UPDATE;
ROLLBACK;
....
....
....
....
Description: Hello, I'd like to report following problem: while executing query (SELECT .. FOR UPDATE) in transaction, the mysql process goes into 'statistics' state in which it hangs. Query is killed after some time, there is no lock waiting. I've updated MySQL to 5.0.45 revision (using fc7 repository), previous version was probably 5.0.12. # Time: # User@Host: # Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 use www_extra; SELECT * FROM `www_extra`.`restaurant_dates` WHERE idx_edition = 'T10_10_01' AND id = 2 FOR UPDATE; mysql> show processlist; | Query | 33 | statistics | SELECT * FROM `www_extra`.`restaurant_dates` WHERE idx_edition = 'T10_10_01' AND id = 2 FOR UPDATE | mysql> show create table restaurant_dates; | restaurant_dates | CREATE TABLE `restaurant_dates` ( `id` int(16) unsigned NOT NULL auto_increment, `idx_edition` varchar(10) NOT NULL, `meeting_date` datetime NOT NULL, `end_meeting_date` datetime NOT NULL, `tables_count` int(16) NOT NULL, `persons_at_table` int(16) NOT NULL, `current_max` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 | no foreign keys, no joins, just simple relation. I found another problems (http://bugs.mysql.com/bug.php?id=26339 and http://bugs.mysql.com/bug.php?id=20932) which looks like related, but no answer found in there. Executed on 64bit. []# uname -a Linux 2.6.21-1.3228.fc7 #1 SMP Tue Jun 12 14:56:37 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux How to repeat: I was executing consecutive (not parallel) queries in separate transactions. Sometimes, I was in the same transaction updating selected row, sometimes not. Transaction was always finished with commit (if update performed) or rollback (if not). i.e. START TRANSACTION; SELECT * FROM `www_extra`.`restaurant_dates` WHERE idx_edition = 'T10_10_01' AND id = 2 FOR UPDATE; UPDATE `www_extra`.`restaurant_date` SET `current_max` = 3 WHERE `id` = 2; COMMIT; START TRANSACTION; SELECT * FROM `www_extra`.`restaurant_dates` WHERE idx_edition = 'T10_10_01' AND id = 2 FOR UPDATE; ROLLBACK; .... .... .... ....