Row level locks in innodb

Question

Innodb implements two row-level locks, one is a shared lock and the other is an exclusive lock.
Excuse me:
(1) Can row-level locks only load row records? Why do I see some mention that shared locks and exclusive locks can also be used at the table level?

Can you give me some tips?

Answer 1

InnoDB's row lock is only effective when the lock statement (that is, FOR UPDATE and LOCK IN SHARE MODE) uses an index, because InnoDB actually locks the row based on the index. That is, if no index is used, the lock is automatically raised to the table level.

Answer 2

As mentioned above, when the transaction isolation level is repeat read (mysql default level)
When the InnoDB engine modifies and deletes data, it will first find the corresponding index. The indexes are all sorted, so it will lock a certain value or a range.
If this range is the entire index segment, then the entire table data will be locked; in addition, if there is no index, the entire table will be scanned for data during modification and deletion, and the entire table will naturally be locked.

Answer 3

InnoDB implements the following two types of row locks.
Shared lock(s): An exclusive lock that allows one transaction to read a row and prevents other transactions from obtaining the same data set.
Exclusive lock (X): Allows transactions that acquire exclusive locks to update data, and prevents other transactions from acquiring shared read locks and exclusive write locks on the same data set.
In addition, in order to allow row locks and table locks to coexist and implement a multi-granularity locking mechanism, InnoDB also has two internally used intention locks (Intention Locks), both of which are table locks.
Intention shared lock (IS): The transaction intends to share the lock on the data row. The transaction must first obtain the IS lock of the table before adding a shared lock to a data row.
Intention exclusive lock (IX): The transaction intends to add an exclusive lock to the data row. The transaction must first obtain the IX lock of the table before adding an exclusive lock to a data row.

Row locks are added to rows, while table locks correspond to the entire table. Row locks and table locks can coexist!

Answer 4

Locks are common to the mysql engine. Row locks and table locks are distinguished from the perspective of lock granularity