For update and deadlock issues with MySQL

First, to say the concept of lock

Lock level:

1. Row-level Lock: InnoDB engine (also supports table-level lock, default is row-level lock), high overhead, locking slow; a deadlock occurs. Locking granularity is minimal, the probability of lock collision is the lowest, and the concurrency is the highest.

2. Table-level Lock: Mylsam engine and memory engine, low overhead, lock fast, no deadlock, lock granularity maximum, the probability of lock conflict is highest, the concurrency is lowest.

3. Page-level Lock: BDB engine (also supports table-level locks), overhead and lock time between row and table locks, a deadlock occurs, and granularity is between row and table locks, and concurrency is common.

Where: Lock granularity is the level of lock

Record:recordlock is locking a line of records
Gap:gaplock locks a range of records in a row
Next-keylocks: The effect of the above two combined

Row locks are divided into two types:

1. Shared Lock: S Lock: SELECT * FROM Test where ... Lock in share mode; Shared read-only lock on one row for a transaction

2. Exclusive Lock: X Lock: SELECT * FROM Test where ... for update; An exclusive read-write lock on one row for a transaction

Two

1. View the Deadlock log command: show engine InnoDB status \g;

Specific deadlock reference: 1190000009469556

Session 1:
SELECT * FROM test where id = 1 for update;

Session 2:
Update Test Set name = "QQ" WHERE id = 1;

When Session1 and Session2 are running simultaneously, Session1 is in the locking of Id=1 (exclusive lock: Other things cannot read or write to the line until it is unlocked). Session2 is in conflict with the row lock held by the session. The database needs to avoid this conflict, which means that the Session2 application is blocked until Session1 releases the row lock.

For update and deadlock issues with MySQL