Lock problem in mysql

Lock problem
13.1 Get lock wait status
You can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables:
mysql> show status like 'Table%';
+---------- ------------------+----------+
| Variable_name | Value |
+------------ ----------------+----------+
| Table_locks_immediate | 105 |
| Table_locks_waited | 3 |
+-------- --------------------+----------+
2 rows in set (0.00 sec)
can be analyzed by checking the Innodb_row_lock status variable Row lock contention on the system:
mysql> show status like 'innodb_row_lock%';
+---------------------------- ------------+----------+
| Variable_name | Value |
+------------------ -----------------------+----------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 2001 |
| Innodb_row_lock_time_avg | 667 |
| Innodb_row_lock_time_max | 845 |
| Innodb_row_lock_waits | 3 |
+---------------------------------- ------+----------+
5 rows in set (0.00 sec)
In addition, for Innodb type tables, if you need to check the current lock waiting status, you can set InnoDB Monitors. Then check through Show innodb status. The setting method is:
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
The monitor can be stopped by issuing the following statement:
DROP TABLE innodb_monitor;
After setting the monitor, in show innodb In the display content of status, there will be detailed information about the current lock waiting, including table name, lock type, lock record status, etc., which facilitates further analysis and problem determination. After opening the monitor, by default, the monitored content will be recorded in the log every 15 seconds. If it is opened for a long time, the .err file will become very large. Therefore, after confirming the cause of the problem, we must remember to delete the monitoring table to close it. monitor. Or start the server with the --console option to turn off writing the log file.
13.2 Under what circumstances should table locks be used? Table-level locks are superior to row-level locks in the following situations:
1. Many operations are table reads.
2. Read and update on a strictly conditional index, when updates or deletes can be read using a separate index:
3. UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
4. DELETE FROM tbl_name WHERE unique_key_col=key_value;
5. SELECT and INSERT statements are executed concurrently, but there are only a few UPDATE and DELETE statements.
6. Many table scans and GROUP BY operations on the entire table, but no table writes.
13.3 When to use row locks Advantages of row-level locking:
1. There are only a few lock conflicts when different rows are accessed in many threads.
2. Only small changes when rolling back.
3. A single row can be locked for a long time.
Disadvantages of row-level locking:
1. It takes up more memory than page-level or table-level locking.
2. When used on large parts of a table, slower than page-level or table-level locking because you have to acquire more locks.
3. If you frequently perform GROUP BY operations on most of the data or must frequently scan the entire table, it will be significantly slower than other locks.
4. With high-level locking, by supporting different types of locks, you can also easily adjust the application because the lock cost is less than row-level locking.