MySQL row lock, table lock

1. When more than one transaction operates on the same row of data, subsequent transactions are in a blocking wait state. This avoids the problem of data consistency such as dirty reading. Later transactions can manipulate other row data, which solves the problem of high concurrency performance of table lock

2,InnoDB Lock is for the index plus lock, not for the record plus lock. And the index cannot be invalidated, otherwise it will be promoted from a row lock to a table lock . field updates that do not have an index become table locks.

3, intermittent lock refers to the update where ID >6 greater than 6 of this gap. Hazard (PIT): If the condition is too large, InnoDB will lock all index key values in the entire range, which can easily affect performance

1 InnoDB supports table locks and row locks, row locks are used when modifying data using indexes as retrieval criteria, or table locks are used.
2 InnoDB automatically locks the modification operation and does not automatically lock the query operation.
A 3 row lock may be promoted to a table lock because it is not using an index, so in addition to checking that the index is created, it is also necessary to query whether the index is actually used through the explain execution plan.
4 rows of locks in relation to the table lock, the advantage lies in the high concurrency scene is more prominent, after all, the size of the lock is small.
5 table locks are preferable to row locks when most of the table's data needs to be modified, or if it is a multi-table complex association query.
6 in order to ensure consistent integrity of the data, any database has a locking mechanism. The advantages and disadvantages of the locking mechanism directly affect the concurrency processing ability and performance of a database.

Reference Address: https://www.cnblogs.com/itdragon/p/8194622.html

MySQL row lock, table lock