mysql 更新一条不存在的数据,加了什么锁?
问题
如题
数据准备
mysql> select * from t_age;
+----+-----+---------+
| id | age | address |
+----+-----+---------+
| 1 | 1 | a |
| 2 | 5 | b |
| 3 | 8 | c |
| 4 | 5 | d |
| 5 | 15 | e |
+----+-----+---------+
5 rows in set (0.02 sec)
更新一条不存在的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_age set address='ll' where age=6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
查看锁信息
# show engine innodb status
TABLE LOCK table `t_age` trx id 255076 lock mode IX
RECORD LOCKS space id 18 page no 5 n bits 80 index idx_age of table `t_age` trx id 255076 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 4; hex 80000003; asc ;;
显示的是使用了 lock_mode X locks gap before rec 即 间隙锁
注意
不同类型的锁,其各自的关键字为:
- 表级的意向排它锁(IX):lock mode IX。
- 表级的插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention
- 行级的记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
- 行级的间隙锁(LOCK_GAP): lock_mode X locks gap before rec
- 行级的 Next-key 锁(LOCK_ORNIDARY): lock_mode X