Lock conflicts and deadlocks

Lock Introduction
InnoDB supports row-level locks and table-level locks. The default value is Row-level locks, which include table-level locks, row-level locks, and page locks.
Introduction to deadlocks
Lock conflicts and deadlocks are not a concept.Lock conflicts are executed in sequence. A deadlock occurs when two or more processes compete for resources during execution,
If there is no external force, they will not be able to push forward.
Table-level locks: low overhead and fast locking; row-level locks: high overhead and slow locking. Table-level locks do not produce deadlocks. Therefore, the most commonly used InnoDB is used to solve deadlocks.
In MySQL, row-level locks do not directly lock records, but lock indexes. Indexes are classified into primary key indexes and non-primary key indexes. If an SQL statement operates the primary key index,
MySQL will lock this primary key index. If a statement operates on a non-primary key index, MySQL will first lock the non-primary key index and then lock the relevant primary key index.
In case of crossover, a deadlock occurs. During the update and delete operations, MySQL not only locks all index records scanned by the where condition, but also locks adjacent key values,
The so-called next-key locking,This is also the reason why the row lock is slow.. Select only supports read-only locks, but update... select changes to write locks.
General
1 update .. when you select a table with only a primary key, the entire table will be locked. The solution is to add an index for the fields following the where condition.
2 update .. the deadlock defined above may occur when the select non-primary key and the update where primary key. The solution is update .. select first select and then update