Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL手记22 — Tips:不走索引就锁全表数据吗?


MySQL手记22 — Tips:不走索引就锁全表数据吗?

今天和小伙伴讨论到:
如果MySQL的加锁,没有走索引,走全表扫描的话,那么加锁是把所有的数据行都锁住,还是只锁住符合where条件的数据?
确实,我们在工作中经常提醒开发人员,让SQL都能走索引,以使得加锁的范围越小越好。所以这个问题,还需要看一个方面:事务的隔离级别

Repeatable_Read隔离级别

表结构:name字段没有索引,在name字段上进行update操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE if not exists `test3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
​
mysql> select * from test3;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | aaa       |
|  2 | bbb       |
|  3 | ccc333ccc |
|  4 | ddd       |
|  5 | eee       |
+----+-----------+
5 rows in set (0.00 sec)

开启一个事务,执行update进行加锁:

1
2
3
4
5
6
7
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
​
​
mysql> update test3 set name='abc' where name='ccc333ccc';
Query OK, 1 row affected (0.02 sec)

查看当前加锁的情况:

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
root:information_schema> select * from INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458026
                 trx_state: RUNNING
               trx_started: 2020-06-12 05:00:40
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 972
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 6
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

或者可使用**show engine innodb status **查看:

1
2
3
4
5
6
7
8
9
10
------------
TRANSACTIONS
------------
Trx id counter 458027
Purge done for trx's n:o < 458010 undo n:o  begin;
Query OK, 0 rows affected (0.00 sec)
​
root:wstestdb> update test3 set name
----等待

第二个事务执行后,处于等待状态,此时查看加锁的情况:

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
mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458027
                 trx_state: LOCK WAIT
               trx_started: 2020-06-12 05:10:27
     trx_requested_lock_id: 458027:77:3:2
          trx_wait_started: 2020-06-12 05:10:27
                trx_weight: 2
       trx_mysql_thread_id: 993
                 trx_query: update test3 set name='abc' where name='eee'
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 458026
                 trx_state: RUNNING
               trx_started: 2020-06-12 05:00:40
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 972
                 trx_query: select * from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 6
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)

第二个事务的状态显示:trx_state: LOCK WAIT,即由于不能马上获得锁,所以需要等待。

结果二:

即使第二个事务中,update的是不同的行,但是由于name字段上没有索引,所以InnoDB需要对所有的行及间隙上锁,所以会出现“LOCK WAIT”的状态。

Read-Committed隔离级别

由于RC隔离级别是没有GAP锁的,所以在进行加锁的时候,只会对于符合条件的数据进行加锁:

1
2
3
4
5
6
7
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
​
mysql> update test3 set name='abc' where name='ccc333ccc';
Query OK, 1 row affected (0.00 sec)

查看此时的加锁情况:

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
mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458034
                 trx_state: RUNNING
               trx_started: 2020-06-12 06:15:58
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 998
                 trx_query: select * from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.01 sec)

第二个session执行:

1
2
3
4
5
6
7
8
root:wstestdb> begin;
Query OK, 0 rows affected (0.00 sec)
​
​
root:wstestdb> update test3 set name='abc' where name='eee';
Query OK, 1 row affected (0.01 sec)
​

结果一:

可以看出,第二个session也是能够成功执行的,因为更改的是不同的行。

查看加锁的情况:

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
mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 458035
                 trx_state: RUNNING
               trx_started: 2020-06-12 06:16:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 997
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 458034
                 trx_state: RUNNING
               trx_started: 2020-06-12 06:15:58
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 998
                 trx_query: select * from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)

小结

这个例子也是“不可重复读”的一个体现:
RC:没走索引时,可以更新不同的行
RR:没走索引时,不可以更新不同的行

在RC级别下,虽然只更新了一行数据update test3 set name=’abc’ where name=’ccc333ccc’;,但是提交之后,再进行查询时,得到name=‘eee’的这行数据也被更新了。
而RR级别通过GAP锁,防止其它的session更新所有的行与间隙,从而得到了一个可重复读取的结果。

comments powered by Disqus