SELECT...FOR UPDATE

一、悲观锁、乐观锁

在了解 SELECT...FRO UPDATE 时，先了解其几个概念，悲观锁、乐观锁。

1.1 乐观锁

乐观锁(Optimistic Locking) 假设并发冲突的概率较低，因此不会阻塞其他事务或线程的读写操作。乐观锁的核心思想是：在进行数据更新前，需先检查数据是否被其他事务修改过，如果没有修改，则进行更新操作，如果有修改，则进行冲突处理。

乐观锁适用于读多写少的场景，这样可以提高程序的吞吐量。

乐观锁采取了更加宽松的加锁机制，避免了数据库幻读、业务处理时间过长等原因引起数据处理错误。乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性。

乐观锁的实现通常涉及一下几个步骤：

1. 读取数据：需记录读取时的版本号或时间戳。
2. 执行操作：执行数据修改。
3. 检查冲突：在更新前，再次读取数据，并比较之前记录的版本号或时间戳。
4. 处理冲突：如果检查到冲突，可以选择进行冲突处理，确保数据的一致性，如：终止当前事务、重试操作、抛出异常等。

常见的乐观锁的实现：

1. CAS机制：Java 中 J.U.C 并发包下非常多的类使用了 CAS 机制来处理并发问题。
2. 版本号机制：一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数。当数据被修改时，version 值会 +1。当线程 A 要更新数据时，在读取数据的同时也会读取并记录 version 值，在提交更新时，满足 oldVersion=nowVerson 才更新。

1.2 悲观锁

悲观锁(Pessimistic Locking) ，顾名思义，在处理多个并发操作时，同一时间点，只能有一个线程可能访问其资源，其他线程需等待锁的释放，才能进行操作。

悲观锁的典型应用场景是数据库操作，其中常见的实现方式是使用数据库提供的行锁、表锁。当一个事务需要对某个数据进行操作时，会先获取锁，同时，其他事务如果也需要访问该数据，就需要等待锁的释放。

优点：保证了数据的一致性。
缺点：降低了并发性能，同一时间只能有一个线程可以对资源进行修改，其他线程需等待锁的释放。

悲观锁主要分为共享锁和排它锁：

• 共享锁(Shared Lock)：也叫 S锁，表示同一时间点可以允许多个线程或事务同时对资源进行 读 操作。
• 排它锁(Exclusive Lock)：也叫 X锁，表示同一时间点只允许一个线程或事务对资源进行 读写 操作。

二、SELECT…FOR UPDATE

SELECT...FOR UPDATE 是一种数据库查询语句，用于在事务中对选定的行或表进行加锁，以防止其他事务同时对这些数据进行修改。

SELECT...FOR UPDATE 语句通常与悲观锁机制一起使用。当一个事务执行 SELECT...FOR UPDATE 语句时，它会获取所选 行或表 的 排它锁，确保其他事务不能同时对这些数据进行修改，从而保持数据的一致性。

在 MySQL 中，SELECT...FOR UPDATE 仅适用于 InnoDB，且必须在事务块中才能生效。InnoDB 默认是行锁，当有明确指定的主键时为行锁，否则为表锁。

语法：

SELECT col1,col2,col3... FROM table_name WHERE 条件1 AND 条件2 FOR UPDATE

注意：

• 当 SELECT... FOR UPDATE 查询条件明确指定主键时，为行锁。
• 当 SELECT... FOR UPDATE 查询条件明确指定索引时，为行锁，所有满足此条件的行都会被加锁。
• 当 SELECT... FOR UPDATE 查询条件所指定主键为一个范围时，为行锁，所有满足此条件的行都会被加锁。
• 当 SELECT... FOR UPDATE 查询条件无主键、无索引时，为表锁。
• 当 SELECT... FOR UPDATE 查询条件明确指定索引或主键时，但查询无结果时，不会加行锁，更不会加表锁

测试如下：

创建一个表，sql如下：

CREATE TABLE `sys_user` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`name` VARCHAR(50) NOT NULL COMMENT '姓名',
	`sex` CHAR(1) NOT NULL COMMENT '性别(0-无 1-男 2-女)',
	`login_id` VARCHAR(20) NOT NULL COMMENT '登陆账号',
	`email` VARCHAR(20) NULL DEFAULT NULL COMMENT '邮箱',
	`tel` VARCHAR(15) NULL DEFAULT NULL COMMENT '联系电话',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `login_id` (`login_id`) USING BTREE
)
COMMENT='系统用户表'
;

----init table

INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (1, '张三', '1', 'zhangsan', 'zhangsan@qq.com', '13599998888');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (2, '李四', '1', 'lisi', 'lisi@qq.com', '13522223333');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (3, '王五', '1', 'wangwu', 'wangwu@qq.com', '13533334444');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (4, '秋葵', '1', 'qiukui', 'qiukui@qq.com', '13555554444');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (5, '鲁智深', '1', 'luzhishen', 'luzhishen@qq.com', '13555556666');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (6, '武松', '1', 'wusong', 'wusong@qq.com', '13566667777');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (7, '诸葛亮', '1', 'zhugeliang', 'zhugeliang@qq.com', '13577778888');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (8, '李忠', '1', 'lizhong', 'lizhong@qq.com', '13588889999');
INSERT INTO `sys_user` (`id`, `name`, `sex`, `login_id`, `email`, `tel`) VALUES (9, '黄忠', '1', 'huangzhong', 'huangzhong@qq.com', '13599990000');

其中 login_id 列创建了一个普通索引。

测试1：当 SELECT... FOR UPDATE 查询条件明确指定主键时，为行锁。

# 事务1：新建连接，执行sql如下
BEGIN;
SELECT * FROM sys_user WHERE id = 1 FOR UPDATE;
-- commit;

# 事务2：新建连接，执行同一个sql，结果会阻塞，需等待事务1释放锁
BEGIN;
SELECT * FROM sys_user WHERE id = 1 FOR UPDATE;
commit;

# 事务3：新建连接，执行sql，结果不会阻塞，事务1只对id=1的数据添加行锁
BEGIN;
SELECT * FROM sys_user WHERE id = 3 FOR UPDATE;
commit;

测试2：当 SELECT... FOR UPDATE 查询条件明确指定索引时，为行锁，所有满足此索引条件的行都会被加锁。

# 事务1：新建连接，执行sql如下
BEGIN;
SELECT * FROM sys_user WHERE login_id LIKE 'l%' FOR UPDATE; -- 最左匹配原则，使用到了索引
-- commit;

# 事务2：新建连接，执行sql，结果会阻塞，需等待事务1释放锁
BEGIN;
SELECT * FROM sys_user WHERE login_id = 'lizhong' FOR UPDATE;
COMMIT;

# 事务3：新建连接，执行sql，结果不会阻塞，事务1只对满足以字母 l 开头的数据添加行锁
BEGIN;
SELECT * FROM sys_user WHERE login_id = 'wusong' FOR UPDATE;
commit;

测试3：当 SELECT... FOR UPDATE 查询条件所指定主键为一个范围时，为行锁，所有满足此主键条件的行都会被加锁。

# 事务1：新建连接，执行sql如下
BEGIN;
SELECT * FROM sys_user WHERE id > 5 FOR UPDATE;
-- commit;

# 事务2：新建连接，执行sql，结果会阻塞，需等待事务1释放锁
BEGIN;
SELECT * FROM sys_user WHERE id = 6 FOR UPDATE;
COMMIT;

# 事务3：新建连接，执行sql，结果不会阻塞，事务1只对 id>5 的数据添加行锁
BEGIN;
SELECT * FROM sys_user WHERE id = 1 FOR UPDATE;
commit;

测试4：当 SELECT... FOR UPDATE 查询条件无主键、无索引时，为表锁

# 事务1：新建连接，执行sql如下
BEGIN;
SELECT * FROM sys_user WHERE NAME = '诸葛亮' FOR UPDATE;
-- commit;

# 事务2：新建连接，执行sql，结果会阻塞，需等待事务1释放表锁
BEGIN;
SELECT * FROM sys_user WHERE id = 6 FOR UPDATE;
COMMIT;

测试5：当 SELECT... FOR UPDATE 查询条件明确指定索引或主键时，但查询无结果时，不会加行锁，更不会加表锁

# 事务1：新建连接，执行sql如下
BEGIN;
SELECT * FROM sys_user WHERE id = 100 FOR UPDATE;
-- commit;

# 事务2：新建连接，执行sql，结果不会阻塞，事务1没有添加任何行锁、表锁
BEGIN;
SELECT * FROM sys_user WHERE NAME = '诸葛亮' FOR UPDATE;
COMMIT;

总结：当 SELECT... FOR UPDATE 查询条件明确指定索引(主键索引、唯一索引、普通索引等)时，如果查询有数据，为行锁，所有满足此索引条件的行都会被加锁。如果没有数据，则不加锁。如果没有明确指定索引，则添加表锁。