Mysql DUPLICATE KEY UPDATE

Duplicate key update batch execution deadlock Error

 

Background

1. mysql insert and duplicate key:

Typical insert statement:

Multiple: insert into tablename (columnA, columnB, columnC)

VALUES ('A', 1, 2), ('B', 7, 5)

Single entry: insert into tablename SET columnA = 'A', columnB = 1, columnC = 2

Copy: INSERT [options1] [INTO] tablename [(columnlist)]
SELECT...

If primary keys such as columnA have been set in the table, repeated insertion is invalid.

ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

If a data entry already exists in the database, the following two statements can be equivalent:

INSERT INTO tablename (id, data) VALUES (1, 10)ON DUPLICATE KEY UPDATE data=data+10;

UPDATE tablename SET data=data+10 WHERE id=1;

The duplicate key statement is generally used to format multiple update statements:

Insert into tablename (id, data) VALUES (1, 10), (2, 15)
On duplicate key update data = data + VALUE (data)

 

Ii. innodb tables improve insertion Efficiency

Engine Used to query tables: show create table tablename;

The innodb Storage engine provides row-level locks, supports two lock modes: Shared locks and exclusive locks, and four different isolation levels.

For Innodb tables, we can improve the import efficiency in the following ways:
A. Because Innodb tables are saved in the order of primary keys, import data according to the primary key
Sequential sorting can effectively improve the efficiency of data import. If the Innodb table does not have a primary key, the system defaults
Create an internal column as the primary key, so if you can create a primary key for the table, you can use this advantage to improve
Data Import efficiency.
B. Run SET UNIQUE_CHECKS = 0 before importing data, disable uniqueness check, and run SET
UNIQUE_CHECKS = 1. Restoring the uniqueness check improves the import efficiency.
C. If the application uses the automatic submission method, we recommend that you execute set autocommit = 0 before importing and disable the automatic
Submit. After the import is complete, execute set autocommit = 1 to enable automatic submission, which can also improve the import efficiency.

If you INSERT many rows from the same customer at the same time, use the INSERT Statement of multiple value tables. This is faster than using separate INSERT statements (several times in some cases ).

You can INSERT many rows from different customers at a higher speed by using the insert delayed statement. The meaning of Delayed is to let the insert statement be executed immediately. In fact, the data is put in the memory queue and is not actually written to the disk; this is much faster than inserting each statement separately; LOW_PRIORITY, on the contrary, is inserted only after all other users have finished reading the table;

 

This insert processing is used when a project encounters a problem, but the data table type is innodb and tablename is different in database/table sharding. The duplicate key is only used to merge update and insert statements.

"Java. SQL. batchUpdateException: Deadlock found when trying to get lock; try restarting transaction "the error state is that the first insert is an error, or a duplicate key in a batch is inserted into multiple values. Looks like mysql has this bug (http://bugs.mysql.com/bug.php? Id = 52020)

Use threadlocal to obtain database objects and static objects, obtain pool connections, and execute batch processing

 

PRIMARY KEY

Unique key differences