mysql 有索引没走索引 更新锁全表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
Session 1:
mysql>
select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|
2 |
+-----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t200 where phoneNo between 30 and 40 for update;
+----+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+----+---------+-------------+--------+
| 30 | 30
|
2 |
1 |
| 34 | 34
|
3 |
2 |
| 39 | 39
|
3 |
2 |
+----+---------+-------------+--------+
3 rows in set (0.00 sec)
mysql>
select @@tx_isolation;
+-----------------+
| @@tx_isolation
|
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> explain
select * from t200 where phoneNo between 30 and 40 for update;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 | SIMPLE
| t200
| ALL
| t200_idx1
| NULL | NULL
| NULL |
34 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
Session 2:
mysql>
insert into t200 values(29,29,4,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
insert into t200 values(100,100,4,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
insert into t200 values(1000,1000,4,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
insert into t200 values(1000,1000,4,1);