Unnecessary write lock blocks inserts
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Invalid
|
Undecided
|
Unassigned |
Bug Description
The queries below show this problem. In short, if you select something from a table that would lock almost all of it, still the inserts aren't blocked. If you do INSERT ... in another table ... for the same select, the source table gets locked and no inserts can go into it. This lock looks completely unnecessary and breaks some workloads which rely on constantly writing in the table.
This query blocks all inserts into table themes:
insert into l_themes select max(id), url from themes group by url;
This doesn't
select max(id), url from themes group by url;
And this also doesn't:
select t1.active_theme, count(t1.id) cnt from themes t1 inner join (select max(tt.id) as id from themes tt group by tt.url ) t2 on t2.id=t1.id group by t1.active_theme order by cnt desc limit 20;
schema of the tables:
CREATE TABLE `themes` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`ts` datetime NOT NULL,
`url` varchar(32) NOT NULL,
`active_theme` text,
PRIMARY KEY (`id`),
KEY `themes_url` (`url`),
KEY `themes_ts` (`ts`),
KEY `themes_
) ENGINE=InnoDB
CREATE TABLE `l_themes` (
`theme_url_id` bigint(20) NOT NULL,
`url` varchar(32) NOT NULL,
PRIMARY KEY (`theme_url_id`),
KEY `l_themes_url` (`url`)
) ENGINE=MEMORY
(this also happens if l_themes is innodb)
There's one work-around here http:// www.mysqlperfor manceblog. com/2006/ 07/12/insert- into-select- performance- with-innodb- tables/ - you have to disable the binlog and enable the "dangerous for replication" queries. But with innodb and versioned rows, the replication shouldn't be an issue.