Mysql batch replace into a deadlock-CodePudding

Batch replace into a deadlock, the overall data do not exist in the primary key of the same data, the only index has two ` name ` index (composite index: name + source_flag) and index_property2 index (composite index: id + property2), the id is the primary key, the red is the name and source_flag fields, the green is the id and property2 field
The REPLACE INTO company (id, base, NAME, legal_person_id legal_person_name, legal_person_type, reg_number, company_type, company_org_type, reg_location, estiblish_time, from_time and to_time, business_scope, reg_institute, approved_time, reg_status, reg_capital, actual_capital, org_number, org_approved_institute, flag, parent_id, updatetime, list_code, ownership_stake, source_flag, name_suffix, property1, property2, property3, property4, property5, crawledtime)
VALUES (' 139224108 ', 'sd', ' with the insurance company ', '25955644', 'money department are', '1', '37040011117153', '0', 'other limited liability company subsidiary company (private)', 'in the high-tech zone of zaozhuang of shandong province jin shui nan town in Internet 5 B 202', '2017-10-25', '2017-10-25', NULL, 'for the policy-holder insurance plan, choose the insurer, the insurance formalities; To assist the insured or the beneficiary to claim for compensation; Provide clients with disaster prevention, preventing damage or risk assessment, risk management consulting services, (subject to ratification in accordance with the project, the related department approval to operate) ', 'the administration for industry and commerce of zaozhuang', '2017-10-25', 'the camp', ' ', ' ', 'MA3EQ2TP1', ' ', '1', '506086', '2018-04-01', ' ', ' ', ' http://qyxy.baic.gov.cn/ ', ' ', '91370400 ma3eq2tp1r', ' ', ' ' ' ' ' ', '2017-10-30');

The REPLACE INTO company (id, base, NAME, legal_person_id legal_person_name, legal_person_type, reg_number, company_type, company_org_type, reg_location, estiblish_time, from_time and to_time, business_scope, reg_institute, approved_time, reg_status, reg_capital, actual_capital, org_number, org_approved_institute, flag, parent_id, updatetime, list_code, ownership_stake, source_flag, name_suffix, property1, property2, property3, property4, property5, crawledtime)
VALUES (' 50207412 ', 'gd', ' chaozhou Ann area clean mansion porcelain industry co., LTD. ', '10433735', 'Cai Yingqiu', '1', '445121000045309', '0', 'limited liability company (natural investment or holding)', 'chaozhou fengtang town Ann in panxi Yang Village sand Mr Films',' 2000-12-19 ', '2000-12-19', NULL, 'production: ceramic sanitary ware, porcelain, porcelain glaze (excluding dangerous goods and pollutant emission) involved, porcelain clay, pottery; Sales: plumbing equipment, stainless steel products, wujinjiaodian, electrical appliances, plastic products, (subject to ratification in accordance with the project, the related department approval to operate) ', 'caitang chaoan chaozhou guangdong province administration for industry and commerce', '2018-04-11', 'the camp (opening) enterprise', '20 million yuan,' ' ', '41741471', ' ', '1', '0', '2018-09-06', ' ', ' ', ' http://qyxy.baic.gov.cn/ ', ' ', '91445103741741471', 'l ', 'Jiexia Ceramic Industry Co., Ltd.,' ' ' ' ', '2018-09-06')

Log screenshot below

CodePudding user response:

The replace into itself is likely to lead to a deadlock, you have so many items, only you want mysql according to which is better?

This kind of situation, you just use the update + insert,

 start transaction; 
Update the set of TB XXX where a=@ a and b=@ b; 
Insert the select * from TB (select @ a as a, @ b as b) x where row_count ()=0; 
Commit

CodePudding user response:

This is in the pit, import to another schema, and then insert language sentence

CodePudding user response:

reference 1/f, gypsy song response:

replace into itself is likely to lead to a deadlock, you have so many items, only you to mysql according to which is better?

This kind of situation, you just use the update + insert,
 start transaction; 
Update the set of TB XXX where a=@ a and b=@ b; 
Insert the select * from TB (select @ a as a, @ b as b) x where row_count ()=0; 
Commit 

When I was in Java code batch submission, tried according to the id, if there id for the data is updated, there is no is deleted, but will still be a deadlock

CodePudding user response:

reference mywood bamboo reply: 3/f

Quote: refer to 1st floor gypsy song response:

The replace into itself is likely to lead to a deadlock, you have so many items, only you want mysql according to which is better?

This kind of situation, you just use the update + insert,
start transaction;
Update the set of TB XXX where a=@ a and b=@ b;
Insert the select * from TB (select @ a as a, @ b as b) x where row_count ()=0;
Commit

When I was in Java code batch submission, tried according to the id, if there id for the data is updated, there is no is deleted, but will still be a deadlock

It is better than you now at least,
For very is busy, a large quantity of data system, not suitable for big table, not suitable for too many index ,
Id is the primary key, itself can guarantee only, but also increase the combination index: id + property2 stem what?
The primary key index generally don't and other fields, and do,

How big is your table now?

In addition, the query is best:

 SET the TRANSACTION ISOLATION LEVEL the READ UNCOMMITTED. 
SELECT * FROM TABLE_NAME; 
COMMIT;

CodePudding user response:

reference 4 floor gypsy song response:

Quote: mywood reference 3 floor bamboo response:

Quote: refer to 1st floor gypsy song response:

The replace into itself is likely to lead to a deadlock, you have so many items, only you want mysql according to which is better?

This kind of situation, you just use the update + insert,
start transaction;
Update the set of TB XXX where a=@ a and b=@ b;
Insert the select * from TB (select @ a as a, @ b as b) x where row_count ()=0;
Commit

When I was in Java code batch submission, tried according to the id, if there id for the data is updated, there is no is deleted, but will still be a deadlock

It is better than you now at least,
For very is busy, a large quantity of data system, not suitable for big table, not suitable for too many index ,
Id is the primary key, itself can guarantee only, but also increase the combination index: id + property2 stem what?
The primary key index generally don't and other fields, and do,

How big is your table now?

In addition, the query is best:
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull