database - Mysql Innodb deadlock problems on REPLACE INTO -

i want update statistic count in mysql.

the sql follow:

replace `record_amount`(`source`,`owner`,`day_time`,`count`) values (?,?,?,?) 

schema :

create table `record_amount` (    `id` int(11) not null auto_increment comment 'id',    `owner` varchar(50) not null ,    `source` varchar(50) not null ,    `day_time` varchar(10) not null,    `count` int(11) not null,    primary key (`id`),    unique key `src_time` (`owner`,`source`,`day_time`) ) engine=innodb default charset=utf8mb4; 

however, caused deadlock exception in multi-processes running (i.e. map-reduce).

i've read materials online , confused locks. know innodb uses row-level lock. can use table-lock solve business problem little extreme. found possible solutions:

1. change replace into transaction select id update , update
2. change replace into insert ... on duplicate key update

i have no idea practical , better. can explain or offer links me read , study? thank you!

are building summary table, 1 source row @ time? , doing update ... count = count+1? throw away code , start over. map-reduce on using sledge hammer on thumbtack.

insert summary (source, owner, day_time, count)     select source, owner, day_time, count(*)         raw         group source, owner, day_time     on duplicate key update count = count + values(count); 

a single statement approximately work @ virtually disk i/o speed. no select ... update. no deadlocks. no multiple threads. etc.

further improvements:

• get rid of auto_increment; turn unique primary key.
• day_time -- datetime truncated hour? (or that.) use datetime, have more flexibility in querying.

to discuss further, please elaborate on source data (`create table, number of rows, frequency of processing, etc) , other details. if data warehouse application summary table, may have more suggestions.

if data coming file, load data shovel temp table raw above insert..select can work. if of manageable size, make raw engine=memory avoid i/o it.

if have multiple feeds, my high-speed-ingestion blog discusses how have multiple threads without deadlocks.