MySQL中悲观锁是锁表还是锁行？

什么是悲观锁

悲观锁是一种“先获取锁，再操作数据”的机制。在MySQL的InnoDB引擎中，使用select ··· from ··· for update来实现悲观锁。但悲观锁到底是锁行还是锁表呢？

示例表结构

create table if not exists t_user
(
    id    bigint      not null auto_increment,
    name  varchar(64) not null,
    phone varchar(64) not null,
    primary key(id),
    key(phone) using btree
) engine = innodb auto_increment = 1 default charset = utf8mb4;

创建示例表t_user，其中id为主键，自增序列；phone为索引；name为普通字段；

插入示例数据

insert into t_user(`name`, `phone`) values('张龙', '13600010001');
insert into t_user(`name`, `phone`) values('赵虎', '13600020002');
insert into t_user(`name`, `phone`) values('王朝', '13600030003');
insert into t_user(`name`, `phone`) values('马汉', '13600040004');

场景1：使用主键

终端1

# 检查事务是否自动提交
mysql > show variables like '%commit%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| autocommit                              | ON                |
| binlog_group_commit_sync_delay          | 0                 |
| binlog_group_commit_sync_no_delay_count | 0                 |
| binlog_order_commits                    | ON                |
| innodb_api_bk_commit_interval           | 5                 |
| innodb_commit_concurrency               | 0                 |
| innodb_flush_log_at_trx_commit          | 1                 |
| original_commit_timestamp               | 36028797018963968 |
| replica_preserve_commit_order           | ON                |
| replication_sender_observe_commit_only  | OFF               |
| slave_preserve_commit_order             | ON                |
+-----------------------------------------+-------------------+
11 rows in set, 1 warning (0.00 sec)
# 设置事务手动提交
mysql > set autocommit = 0;
# 手动添加悲观锁
mysql > select * from t_user where id = 1 for update;
+----+------+-------------+
| id | name | phone       |
+----+------+-------------+
|  1 | 张龙 | 13600010001 |
+----+------+-------------+
1 row in set (0.00 sec)

终端2

msyql > update t_user set name = '张龙龙' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql > update t_user set name = '赵虎虎' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

可知，悲观锁走主键约束时，加的是 行级锁。

场景2：使用索引

索引查出1条记录

终端1

mysql > select * from t_user where phone = '13600010001';
+----+------+-------------+
| id | name | phone       |
+----+------+-------------+
|  1 | 张龙 | 13600010001 |
+----+------+-------------+
1 row in set (0.00 sec)

终端2

msyql > update t_user set name = '张龙龙' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql > update t_user set name = '赵虎虎' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

索引查出多条记录

终端1

mysql> select * from t_user;
+----+-------+-------------+
| id | name  | phone       |
+----+-------+-------------+
|  1 | 张龙  | 13600010001 |
|  2 | 赵虎  | 13600020002 |
|  3 | 王朝  | 13600030003 |
|  4 | 马汉  | 13600040004 |
+----+-------+-------------+
4 rows in set (0.00 sec)
mysql> update t_user set phone = '13600010001' where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t_user;
+----+-------+-------------+
| id | name  | phone       |
+----+-------+-------------+
|  1 | 张龙  | 13600010001 |
|  2 | 赵虎  | 13600010001 |
|  3 | 王朝  | 13600030003 |
|  4 | 马汉  | 13600040004 |
+----+-------+-------------+
4 rows in set (0.00 sec)

终端2

msyql > update t_user set name = '张龙龙' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql > update t_user set name = '赵虎虎' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql > update t_user set name = '王朝朝' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

可知，使用索引时，加的是 行级锁。

场景3：使用普通列

终端1

mysql> select * from t_user where name = '张龙' for update;
+----+------+-------------+
| id | name | phone       |
+----+------+-------------+
|  1 | 张龙 | 13600010001 |
+----+------+-------------+
1 row in set (0.00 sec)

终端2

mysql > update t_user set name = '张龙龙' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql > update t_user set name = '赵虎虎' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可知，使用普通列时，加的是 表级锁。