MySQL行级锁Row-Level Locking及表锁Table-Level Locking 简介及示例

• 内部锁
• • 行级锁
  • • 简介
    • 关于死锁
    • 行级锁的优势
    • 示例
  • 表锁
  • • 表级锁的优势
    • 示例

定义
This section discusses internal locking; that is, locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs.

内部锁：在MySQL服务器内部执行的锁，以管理多个会话对表内容的争用。这种类型的锁是内部的，因为它完全由MySQL服务器执行，不涉及其他程序。

分类

行级锁

表锁

MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications

行级锁适用于InnoDB类型的表。

用于支持多个会话同时进行写访问。

适合适用于多用户、高并发和OLTP（在线事务处理）应用程序。

To avoid deadlocks when performing multiple concurrent write operations on a single InnoDB table, acquire necessary locks at the start of the transaction by issuing a SELECT … FOR UPDATE statement for each group of rows expected to be modified, even if the data change statements come later in the transaction. If transactions modify or lock more than one table, issue the applicable statements in the same order within each transaction. Deadlocks affect performance rather than representing a serious error, because InnoDB automatically detects deadlock conditions by default and rolls back one of the affected transactions.
当对一个InnoDB表执行多个并发写操作时，为了避免死锁，可以在事务开始时，通过对期望修改的每一行（或多行）使用SELECT…FOR UPDATE来对改行（多行）上锁，即使数据更改语句稍后出现在事务中。如果事务修改或锁定多个表，则在每个事务中以相同的顺序发出适用的语句。死锁不会导致严重的错误，而是会影响性能，因为InnoDB默认情况下会自动检测死锁，并回滚其中一个

On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.
在高并发性系统中，当多个线程等待同一个锁时，死锁检测可能会导致减速。有时，禁用死锁检测并在死锁发生时依赖于innodb_lock_wait_timeout设置进行事务回滚可能会更有效。可以使用innodb_deadlock_detect配置选项禁用死锁检测。

Advantages of row-level locking:

Fewer lock conflicts when different sessions access different rows.

Fewer changes for rollbacks.

Possible to lock a single row for a long time.

可参见文章https://blog.csdn.net/qq_29025955/article/details/126164245 ， 其中包括死锁检测的启用/禁用

MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, permitting only one session to update those tables at a time. This locking level makes these storage engines more suitable for read-only, read-mostly, or single-user applications.

MySQL对MyISAM, MEMORY和MERGE表使用表级锁，一次只允许一个会话更新这些表。这种锁定级别使这些存储引擎更适合只读、多读或单用户应用程序。

These storage engines avoid deadlocks by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The tradeoff is that this strategy reduces concurrency; other sessions that want to modify the table must wait until the current data change statement finishes.

这些存储引擎总是在查询开始时一次请求所有需要的锁，并且总是以相同的顺序锁定表，从而避免死锁。这种策略的代价是降低了并发性;其他想要修改表的会话必须等待，直到当前数据更改语句完成。

Advantages of table-level locking:(表级锁的优势)

Relatively little memory required (row locking requires memory per row or group of rows locked)

Fast when used on a large part of the table because only a single lock is involved.

Fast if you often do GROUP BY operations on a large part of the data or must scan the entire table frequently.

创建一张存储引擎为MyISAM 的测试表

CREATE TABLE `table_lock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL,
  `version` float(255,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入几条数据

关于表的READ锁

上锁语句

LOCK TABLES table_lock_test READ;

解释：
当前线程/session给表table_lock_test加上了一个READ锁。
所有线程/session都可以对表able_lock_test 进行读操作。
当前线程/session对表table_lock_test进行写操作时，MySQL会报错。
其他线程/session对表table_lock_test进行写操作时，会被阻塞，等待当前/seesion解锁（以下3种解锁方式）

解锁语句，有3种

 1. UNLOCK TABLES;

 2. 再次使用 LOCK TABLES 其他表 READ/WRITE;

 3. 当前线程关闭

演示

• 所有线程/session都可以对表table_lock_test 进行读操作。

• 当前线程/session对表table_lock_test进行写操作时，MySQL会报错。
  
• 其他线程/session对表table_lock_test进行写操作时，会被阻塞，等待当前/seesion解锁（以下3种解锁方式）
  
  解锁

关于表的WRITE锁

上锁语句

LOCK TABLES table_lock_test WRITE;

解释：
当前线程/session给表table_lock_test加上了一个WRITE锁。
只有当前线程/session才能对表table_lock_test进行写操作。
当前线程/session对表table_lock_test进行写操作时，会被阻塞，等待当前/seesion解锁（以下3种解锁方式）。
其他线程/session对表table_lock_test进行读/写操作时，会被阻塞，等待当前/seesion解锁（以下3种解锁方式）

解锁语句

同READ锁

示例

• 只有当前线程/session才能对表table_lock_test进行写操作。
  
• 当前线程/session对表table_lock_test进行写操作时，会被阻塞，等待当前/seesion解锁（以下3种解锁方式）。

• 其他线程/session对表table_lock_test进行读/写操作时，会被阻塞，等待当前/seesion解锁（以下3种解锁方式）

参考：

1. MySQL官方手册第8章 8.11 优化
2. https://www.cnblogs.com/jpfss/p/9214974.html