I have a large query which generates summary/aggregates every night
Lately this has been failing with a deadlock error.
The query is below, though I doubt its really important other than the fact its doing SELECT… INSERT
insert into imageratingtotals_alltime (irta_charttype, irta_intervalid, irta_imageid, irta_userid, irta_datetime, irta_count, irta_total, irta_average, irta_tvcd, irta_ts, irta_maleadm, irta_femadm, irta_1825, irta_2530, irta_3035, irta_3540, irta_4045, irta_4550, irta_5060, irta_60, irta_vote1, irta_vote2, irta_vote3, irta_vote4, irta_vote5, irta_vote6, irta_vote7, irta_vote8, irta_vote9, irta_vote10) select 0, 3, ui_id, ui_userid, CURRENT_TIMESTAMP, count(ir_rating), SUM(IF(ir_rating IS NULL, 0, ir_rating)), AVG(ir_rating), SUM(IF(u_tvtype IN (1,8,9,10), IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_tvcd, SUM(IF(u_tvtype IN (2,3,6), IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_ts, SUM(IF(u_tvtype IN (4,11,5,7) and u_gender =1, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_maleadm, SUM(IF(u_tvtype IN (4,11,5,7) and u_gender IN (2,3,4), IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_femadm, SUM(IF(u_age >= 18 and u_age < 25, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_1825, SUM(IF(u_age >= 25 and u_age < 30, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_2530, SUM(IF(u_age >= 30 and u_age < 35, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_3035, SUM(IF(u_age >= 35 and u_age < 40, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_3540, SUM(IF(u_age >= 40 and u_age < 45, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_4045, SUM(IF(u_age >= 45 and u_age < 50, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_4550, SUM(IF(u_age >= 50 and u_age < 60, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_5060, SUM(IF(u_age >= 60, IF(ir_rating IS NULL, 0, ir_rating), 0)) irt_60, SUM(IF(ir_rating IS NOT NULL and ir_rating = 1, 1, 0)) irt_vote1, SUM(IF(ir_rating IS NOT NULL and ir_rating = 2, 1, 0)) irt_vote2, SUM(IF(ir_rating IS NOT NULL and ir_rating = 3, 1, 0)) irt_vote3, SUM(IF(ir_rating IS NOT NULL and ir_rating = 4, 1, 0)) irt_vote4, SUM(IF(ir_rating IS NOT NULL and ir_rating = 5, 1, 0)) irt_vote5, SUM(IF(ir_rating IS NOT NULL and ir_rating = 6, 1, 0)) irt_vote6, SUM(IF(ir_rating IS NOT NULL and ir_rating = 7, 1, 0)) irt_vote7, SUM(IF(ir_rating IS NOT NULL and ir_rating = 8, 1, 0)) irt_vote8, SUM(IF(ir_rating IS NOT NULL and ir_rating = 9, 1, 0)) irt_vote9, SUM(IF(ir_rating IS NOT NULL and ir_rating = 10, 1, 0)) irt_vote10 from imageratings inner join userimages on (ui_id = ir_imageid) inner join users on (u_id = ir_userid) where ir_datetime > DATE_SUB(CURRENT_TIMESTAMP, interval 12 month) group by ir_imageid
My question is, what are my options for avoiding this?
I understand SELECT…INSERT generates a table lock in innodb, and one alternative is to use an OUTFILE.
I want to avoid this.
Another alternative is to use READ_COMMITTED transaction mode, which for this query would be fine I think. However I cant find a way to set the isolation mode on an individual query, all references I found suggested putting it into my.cnf, which is not good for me because it would affect all queries.
Any suggestions please?
PS:
The table is truncated before the select…insert