方案概述
1. 现象
当实际生产中，明明查询只有一条数据，没有其它用户修改这一笔数据，当我们需要修改这一笔数据时，往往会卡住，到底是怎么回事呢？
2. 模拟
mysql的事务级别是RC
master [localhost:21829] {root} (test) > show variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)Session 1:
Session 1:
master [localhost:21829] {root} (test) > begin;
Query OK, 0 rows affected (0.00 sec)
master [localhost:21829] {root} (test) > select * from test_idx where age=30;
+----+----------+------+------+
| id | name | age | dept |
+----+----------+------+------+
| 1 | zhangsan | 30 | hhh |
| 2 | zhang | 30 | it |
| 5 | lisi | 30 | it |
+----+----------+------+------+
3 rows in set (0.00 sec)
master [localhost:21829] {root} (test) > select * from test_idx where name='zhang' and age=30 for update;
+----+-------+------+------+
| id | name | age | dept |
+----+-------+------+------+
| 2 | zhang | 30 | it |
+----+-------+------+------+
1 row in set (0.01 sec)Session 2:
Session 2:
master [localhost:21829] {root} (test) > begin;
Query OK, 0 rows affected (0.01 sec)
master [localhost:21829] {root} (test) > select * from test_idx where name='zhangsan' and age=30;
+----+----------+------+------+
| id | name | age | dept |
+----+----------+------+------+
| 1 | zhangsan | 30 | hhh |
+----+----------+------+------+
1 row in set (0.00 sec)
master [localhost:21829] {root} (test) > update test_idx set dept='ab' where name='zhangsan' and age=30;
。。。。。。
这里卡住了。Session 3:
Session 3:
查锁
master [localhost:21829] {root} ((none)) > select locked_table,locked_table_name,locked_index,locked_type,waiting_trx_id,waiting_pid,blocking_trx_id,blocking_trx_started,sql_kill_blocking_query,sql_kill_blocking_connection,blocking_lock_mode from sys.innodb_lock_waits \G
*************************** 1. row ***************************
locked_table: `test`.`test_idx`
locked_table_name: test_idx
locked_index: idx_age
locked_type: RECORD
waiting_trx_id: 23098
waiting_pid: 11
blocking_trx_id: 23094
blocking_trx_started: 2024-05-06 19:05:32
sql_kill_blocking_query: KILL QUERY 8
sql_kill_blocking_connection: KILL 8
blocking_lock_mode: X,REC_NOT_GAP
1 row in set (0.00 sec)可以看到锁类型是记录锁，同时锁在索引idx_age,并且是X,REC_NOT_GAP。
先看一下表结构：
master [localhost:21829] {root} (test) > show create table test_idx;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_idx | CREATE TABLE `test_idx` (
`id` int NOT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int DEFAULT NULL,
`dept` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci可以看出第二索引idx_age是单列age的索引。mysql查找数据先是通过二级索引，找到主键的键值，再通过主键键值找到主键的物理行索引到数据的。从上面的例上可以看出,索引idx_age值为30并不知道能索引到多少笔数据，mysql在修改数据时，就把这个idx_age值为30的都上锁，idx_age对应的主键键值有id为1，2，5，这三行数据也需要对应的加锁，所以sql select * from test_idx where name='zhang' and age=30 for update;虽然只索引到1笔数据，但是索引加锁是锁了3笔。那这个问题要如何解决呢？
其实很简单，把单列改索改为复合索引就可以 idx_age（age,name）。
3. 小结
- mysql锁数据是通过索引来加锁数据。（表没有索引的是通过主键来锁数据，如果表没主键，mysql会自动创建隐藏的主键）
- mysql建索引是不能随便建的，需要根据实际的应用SQL建索引。
