Table-level and row-level locks for Mysql

Table-Level Locks MySQL table-level locks are divided into read and write locks. Read lock Usage: LOCK TABLE table_name [as Alias_name] READ Release the lock using unlock tables. Aliases can be used for tables, and aliases must be used if aliases are used. A successful request for a read lock assumes that no thread is currently using a write lock on the table, otherwise the statement will be blocked. After the request for a read lock succeeds, other threads can also read the table, but the thread is not allowed to write to it, even if it is not allowed by the current threads. When a table is locked, it is only possible to read a table, and an error occurs when reading other tables (TableName is not locked with lock TABLES) Write lock Usage: LOCK TABLE table_name [as Alias_name] [low_priority] WRITE Aliases can also be used, unlike read locks, where the priority of a lock can be specified in a write lock. Low_priority is a lock with lower priority than read lock, and low_priority has the lowest priority when multiple threads request multiple locks at the same time (Low_priority,read,write). The prerequisite for a read lock application is that there are no threads reading and other write locks on the table, otherwise it will be blocked. Table-level locks are useful in both MyISAM and InnoDB, where the cost of creating a lock is small and there is no deadlock, because the entire table is locked, so the concurrency is low. Table-level locks are recommended when you need to do GROUP by operations on most data frequently or when you need to scan the entire table frequently. Row-level lock row level lock is the most granular locking lock in MySQL, which can greatly reduce the conflict of database operation, because of its small granularity, the cost of locking is the most. Row-level locks are divided into shared and exclusive locks. Shared Lock (S Lock) Usage: SELECT ... LOCK in SHARE MODE; MySQL adds a shared lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, the shared lock can be successfully requested or it will be blocked. Other threads can also read tables that use shared locks, and those threads are reading the same version of data. Exclusive lock (X Lock) Usage: SELECT ... LOCK for UPDATE; MySQL adds an exclusive lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, it can successfully request an exclusive lock or it will be blocked. Row-level locks are index-based, and table-level locks are used if an SQL statement does not use a row-level lock for the index. The disadvantage of row-level locks is that they require a large number of lock resources to be requested, so they are slow and memory consuming.

Table-level and row-level locks for Mysql