MYSQL RR隔离级别下无索引更新是否表锁?

最新推荐文章于 2024-06-28 23:04:57 发布

Shark曾_小凡仙

最新推荐文章于 2024-06-28 23:04:57 发布

阅读量519

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/ZengMuAnSha/article/details/138524195

版权

最近在MYSQL菜鸟群有群友提问,说他看了某个公众号里面文章说 "MYSQL RR隔离级别下无索引更新会导致表锁! " 他表示疑惑,而且不仅是他,还有很多个她在不同的群里同样表示疑惑! 下面是群友的截图

是啊 MYSQL 以及进化到了8.3.0版本了,普遍都使用5.7和8.0版本.而且还听说 INNODB引擎会把不符合条件的记录给释放锁. 原理大概是这样的,一个表有100行记录,一条UPDATE语句需要更新10行记录. 而这10行记录分散在这100行里, 从第一个符合条件的行到最后一个不符合条件的行大约是30行. 好吧! 30行和100行差不多. 开始之处不管三七二十一,见行就上X锁.然后把行回传给MYSQL SERVER层,进行过滤处理. 需要的请留下,不需要的通知给INNODB,好让引擎把行锁个释放掉了.

从这个优化理论来说, RR下应该会把不需要的记录给释放掉,那么RR下的表锁就不可能出现.

套用某人的话说, 我以上都是胡说八道! 我们试验一把,先看下真正表锁是什么样子!
先上个表读锁

SESSION 2
MySQL 14:52:19 root@192.168.0.221 [dba]>lock tables dba.personal_identity_info read;
Query OK, 0 rows affected (0.01 sec)

我们SESSION 2是主决 SESSION3 负责查看监控, SESSION 1负责打酱油的.

SESSION 3
[sys]>select * from performance_schema.table_handles;
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+---------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME                | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+---------------+
| TABLE       | sys           | sys_config                 |       140317340487016 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info     |       140317120949096 |             101 |             29 | NULL          | READ EXTERNAL |
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+---------------+
235 rows in set (0.01 sec)


MySQL 15:41:03 root@192.168.0.221 [sys]>select * from performance_schema.metadata_locks;
+-------------+--------------------+------------------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME            | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE        | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+------------------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | dba                | personal_identity_info | NULL        |       140316984440272 | SHARED_READ_ONLY | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             105 |             29 |
| TABLE       | performance_schema | metadata_locks         | NULL        |       140316917412768 | SHARED_READ      | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             104 |             48 |
+-------------+--------------------+------------------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)

从TABLE_HANDLES说personal_identity_info是外部锁 READ EXTERNAL.
从metadata_locks 表看SHARED_READ_ONLY 共享只读锁
下面更新其它行,打酱油的试图更新且等待中

SESSION 1
MySQL 15:00:22 root@192.168.0.221 [dba]>update dba.personal_identity_info set surname='曾' where surname='曾君';
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Connection id:    60
Current database: dba

SESSION 3

MySQL 15:41:23 root@192.168.0.221 [sys]>SELECT PROCESSLIST_ID,THREAD_OS_ID,THREAD_ID,PROCESSLIST_INFO FROM performance_schema.threads where TYPE='FOREGROUND';
+----------------+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
| PROCESSLIST_ID | THREAD_OS_ID | THREAD_ID | PROCESSLIST_INFO                                                                                                      |
+----------------+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
|              5 |         6440 |        45 | NULL                                                                                                                  |
|              7 |         6444 |        49 | NULL                                                                                                                  |
|             34 |         6480 |        80 | NULL                                                                                                                  |
|             35 |         6566 |        81 | NULL                                                                                                                  |
|             49 |         6607 |        95 | NULL                                                                                                                  |
|             57 |         7822 |       103 | NULL                                                                                                                  |
|             58 |         6454 |       104 | SELECT PROCESSLIST_ID,THREAD_OS_ID,THREAD_ID,PROCESSLIST_INFO FROM performance_schema.threads where TYPE='FOREGROUND' |
|             59 |         6479 |       105 | NULL                                                                                                                  |
|             60 |         7823 |       106 | update dba.personal_identity_info set surname='曾' where surname='曾君'                                               |
+----------------+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)


MySQL 15:44:41 root@192.168.0.221 [sys]> select * from performance_schema.metadata_locks where owner_thread_Id != sys.ps_thread_id(connection_id());
+-------------+---------------+------------------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME            | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+------------------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | dba           | personal_identity_info | NULL        |       140316984440272 | SHARED_READ_ONLY    | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             105 |             29 |
| GLOBAL      | NULL          | NULL                   | NULL        |       140317857342752 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:3038  |             106 |             23 |
| TABLE       | dba           | personal_identity_info | NULL        |       140317856664544 | SHARED_WRITE        | TRANSACTION   | PENDING     | sql_parse.cc:5768 |             106 |             23 |
+-------------+---------------+------------------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set, 1 warning (0.00 sec)

表要上共享写,确被共享度给阻塞了

SESSION 2
UNLOCK TABLES



MySQL 15:58:46 root@192.168.0.221 [sys]>select * from performance_schema.table_handles;
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME                | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK  |
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+----------------+
| TABLE       | sqle          | workflows                  |       140317118649272 |            NULL |           NULL | NULL          | NULL           |
| TABLE       | dba           | personal_identity_info     |       140316984445496 |             105 |         177468 | NULL          | WRITE EXTERNAL |
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+----------------+
238 rows in set (0.00 sec)

MySQL 16:01:57 root@192.168.0.221 [sys]>select * from performance_schema.metadata_locks where owner_thread_Id != sys.ps_thread_id(connection_id());
+-------------+---------------+----------------------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME                | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+----------------------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL          | NULL                       | NULL        |       140316984580720 | INTENTION_EXCLUSIVE  | STATEMENT     | GRANTED     | sql_base.cc:5436  |             105 |         176218 |
| SCHEMA      | dba           | NULL                       | NULL        |       140316988086032 | INTENTION_EXCLUSIVE  | TRANSACTION   | GRANTED     | sql_base.cc:5423  |             105 |         176218 |
| TABLE       | dba           | personal_identity_info     | NULL        |       140316984440272 | SHARED_NO_READ_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             105 |         176219 |
| TABLESPACE  | NULL          | dba/personal_identity_info | NULL        |       140316987539760 | INTENTION_EXCLUSIVE  | TRANSACTION   | GRANTED     | lock.cc:802       |             105 |         177465 |
+-------------+---------------+----------------------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.00 sec)

释放掉共享读,再上表写锁出现 WRITE EXTERNAL SHARED_NO_READ_WRITE;

接下来我们看看RR模式下无索更新是否会出现上面的情况? 这次让SESSION 1当主角,先看执行计划,走主键索引

MySQL 16:04:15 root@192.168.0.221 [dba]>explain update dba.personal_identity_info set surname='王X' where surname='张';
+----+-------------+------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table                  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | personal_identity_info | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1001 |   100.00 | Using where |
+----+-------------+------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

然后开始SESSION 1 的表演

SESSION 1:

MySQL 14:05:00 root@192.168.0.221 [dba]>show session variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

MySQL 14:05:56 root@192.168.0.221 [dba]>show global variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

MySQL 14:06:17 root@192.168.0.221 [dba]>set session sql_safe_updates=off;
Query OK, 0 rows affected (0.00 sec)

MySQL 14:06:37 root@192.168.0.221 [dba]>BEGIN;
Query OK, 0 rows affected (0.00 sec)

MySQL 14:16:58 root@192.168.0.221 [dba]>select * from dba.personal_identity_info where surname='张';
+-----+--------------------+---------+-----------+-------------+-----+---------------------+-----+---------------+-------------+----------------------------+----------------------------+
| id  | identity_number    | surname | name      | mobile      | sex | birthday            | age | top_education | income_year | create_time                | update_time                |
+-----+--------------------+---------+-----------+-------------+-----+---------------------+-----+---------------+-------------+----------------------------+----------------------------+
| 551 | 13.544431931846246 | 张      | 梦冰师    | 13552049405 | 男  | 1970-05-18 00:00:00 |  33 | 初中          |       56482 | 2023-05-18 15:32:12.735522 | 2023-05-18 15:32:12.735522 |
| 800 | 133777832243384635 | 张      | 栋家恩    | 14899421941 | 男  | 1971-05-18 00:00:00 |  49 | 文盲          |       27376 | 2023-05-18 15:32:19.409945 | 2023-05-18 15:32:19.409945 |
| 912 | 13.393129107908466 | 张      | 顺水娜    | 13317756125 | 男  | 1965-05-18 00:00:00 | 127 | 文盲          |       77904 | 2023-05-18 15:32:21.144088 | 2023-05-18 15:32:21.144088 |
+-----+--------------------+---------+-----------+-------------+-----+---------------------+-----+---------------+-------------+----------------------------+----------------------------+
3 rows in set (0.01 sec)

MySQL 14:17:09 root@192.168.0.221 [dba]>update dba.personal_identity_info set surname='王X' where surname='张';
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0

监控SESSION 3 看了它上了好多X索还有 SUPREMUM PSEUNDO-RECORD

SESSION 3
MySQL 14:17:02 root@192.168.0.221 [dba]>SELECT LOCK_TYPE,LOCK_MODE,LOCK_DATA FROM performance_schema.data_locks;
+-----------+-----------+------------------------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA              |
+-----------+-----------+------------------------+
| TABLE     | IX        | NULL                   |
| RECORD    | X         | supremum pseudo-record |
| RECORD    | X         | 1                      |
| RECORD    | X         | 2                      |
| RECORD    | X         | 3                      |
| RECORD    | X         | 4                      |
| RECORD    | X         | 5                      |
| RECORD    | X         | 6                      |
| RECORD    | X         | 7                      |
| RECORD    | X         | 8                      |
| RECORD    | X         | 9                      |
| RECORD    | X         | 10                     |
| RECORD    | X         | 1000                   |
| RECORD    | X         | 1005                   |
| RECORD    | X         | 1009                   |
| RECORD    | X         | 1008                   |
| RECORD    | X         | 1010                   |
| RECORD    | X         | 1011                   |
| RECORD    | X         | 1012                   |
| RECORD    | X         | 937                    |
| RECORD    | X         | 964                    |
| RECORD    | X         | 969                    |
| RECORD    | X         | 1001                   |
| RECORD    | X         | 912                    |
+-----------+-----------+------------------------+
1018 rows in set (0.03 sec)

AI 3.5 说得差不多

在MySQL中，DATA_LOCK表用于存储由各种锁机制创建的锁信息。LOCK_DATA列中的SUPREMUM PSEUDO-RECORD表示的是一个伪记录，它在内部用于表示一个范围的锁。

具体来说，SUPREMUM PSEUDO-RECORD表示的是一个最大值或者上限，它在锁定范围的最顶部。这种伪记录通常用于表示一个范围锁的起始点或终止点，以便清晰地表示锁定的范围。

在MySQL中，范围锁可以用于锁定一个范围内的数据，而不仅仅是单个行或表。这种锁机制可以在多个会话之间提供更灵活的锁定策略，以确保数据的一致性和完整性。

打酱油的出场....... 舔狗舔到一无所有,直到被拒绝!

SESSION 2
MySQL 14:22:12 root@192.168.0.221 [dba]>set session sql_safe_updates=off;
Query OK, 0 rows affected (0.00 sec)

MySQL 14:22:29 root@192.168.0.221 [dba]>update dba.personal_identity_info set surname='曾君' where surname='曾';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MySQL 14:24:43 root@192.168.0.221 [dba]>INSERT INTO dba.personal_identity_info (IDENTITY_NUMBER,SURNAME,NAME,MOBILE,SEX,BIRTHDAY,AGE,TOP_EDUCATION,INCOME_YEAR) VALUES(135478728875032375,'燕','燕太杰','13984280466','男','2001-05-18 00:00:00','100','博士','27701');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

没有出现表锁的字样!

SESSION 3
MySQL 15:53:59 root@192.168.0.221 [sys]>select * from performance_schema.table_handles WHERE OBJECT_NAME='personal_identity_info';
+-------------+---------------+------------------------+-----------------------+-----------------+----------------+---------------+---------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME            | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
+-------------+---------------+------------------------+-----------------------+-----------------+----------------+---------------+---------------+
| TABLE       | dba           | personal_identity_info |       140317187639336 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info |       140317320812936 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info |       140317320824264 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info |       140317340006888 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info |       140316917541176 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info |       140316986582840 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info |       140317120949096 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info |       140316984445496 |            NULL |           NULL | NULL          | NULL          |
+-------------+---------------+------------------------+-----------------------+-----------------+----------------+---------------+---------------+
8 rows in set (0.00 sec)


MySQL 15:54:06 root@192.168.0.221 [sys]>select * from performance_schema.metadata_locks where owner_thread_Id != sys.ps_thread_id(connection_id());
+-------------+---------------+------------------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME            | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE    | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+------------------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | dba           | personal_identity_info | NULL        |       140316984580720 | SHARED_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             105 |            849 |
+-------------+---------------+------------------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)

小结
RR 模式下无索引更新 INNODB 表走的是主键聚集索引对所有行加X锁,以及行之间的GAP锁, 且事务提交会被回滚才释放锁. 其实行锁X+GAP锁就是NEXT-KEY锁
再来一张图,会更加让人记忆深刻

RC 模式下

SESSION 1:
set session transaction isolation level read committed;



MySQL 14:39:17 root@192.168.0.221 [dba]>show session variables like '%iso%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)

MySQL 14:39:46 root@192.168.0.221 [dba]>show global variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

MySQL 14:39:53 root@192.168.0.221 [dba]>BEGIN;
Query OK, 0 rows affected (0.01 sec)

MySQL 14:40:44 root@192.168.0.221 [dba]>update dba.personal_identity_info set surname='王X' where surname='张';
Query OK, 3 rows affected (0.37 sec)
Rows matched: 3  Changed: 3  Warnings: 0

上的锁真少


SESSION 3:
MySQL 14:35:09 root@192.168.0.221 [sys]>SELECT LOCK_TYPE,LOCK_MODE,LOCK_DATA FROM performance_schema.data_locks;
+-----------+---------------+-----------+
| LOCK_TYPE | LOCK_MODE     | LOCK_DATA |
+-----------+---------------+-----------+
| TABLE     | IX            | NULL      |
| RECORD    | X,REC_NOT_GAP | 551       |
| RECORD    | X,REC_NOT_GAP | 800       |
| RECORD    | X,REC_NOT_GAP | 912       |
+-----------+---------------+-----------+
4 rows in set (0.01 sec)

酱油会话可颜可甜

ESSION 2:
set session transaction isolation level read committed;
set session sql_safe_updates=off;


MySQL 14:42:13 root@192.168.0.221 [dba]>set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

MySQL 14:42:16 root@192.168.0.221 [dba]>set session sql_safe_updates=off;
Query OK, 0 rows affected (0.00 sec)

MySQL 14:42:29 root@192.168.0.221 [dba]>update dba.personal_identity_info set surname='曾君' where surname='曾';
Query OK, 26 rows affected (0.12 sec)
Rows matched: 26  Changed: 26  Warnings: 0

MySQL 14:42:55 root@192.168.0.221 [dba]>INSERT INTO dba.personal_identity_info (IDENTITY_NUMBER,SURNAME,NAME,MOBILE,SEX,BIRTHDAY,AGE,TOP_EDUCATION,INCOME_YEAR) VALUES(135478728875032375,'燕','燕太杰','13984280466','男','2001-05-18 00:00:00','100','博士','27701');
Query OK, 1 row affected (0.37 sec)

没有表锁!

SESSION 3
MySQL 14:44:55 root@192.168.0.221 [sys]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,ISOLATION_LEVEL,AUTOCOMMIT  FROM performance_schema.events_transactions_current;
+-----------+-------------+-----------+-----------------+-----------------+------------+
| THREAD_ID | EVENT_NAME  | STATE     | TRX_ID          | ISOLATION_LEVEL | AUTOCOMMIT |
+-----------+-------------+-----------+-----------------+-----------------+------------+
|         1 | transaction | COMMITTED | 421796018412648 | REPEATABLE READ | YES        |
|        36 | transaction | COMMITTED | 421796018411608 | REPEATABLE READ | YES        |
|        46 | transaction | COMMITTED | 421796018413688 | REPEATABLE READ | YES        |
|        49 | transaction | COMMITTED | 421796018412648 | REPEATABLE READ | YES        |
|        80 | transaction | COMMITTED | 421796018415768 | REPEATABLE READ | YES        |
|        81 | transaction | COMMITTED | 421796018416808 | REPEATABLE READ | YES        |
|        92 | transaction | COMMITTED | 421796018413688 | REPEATABLE READ | YES        |
|        95 | transaction | COMMITTED | 421796018418888 | REPEATABLE READ | YES        |
|        99 | transaction | ACTIVE    |            NULL | READ COMMITTED  | NO         |
|       100 | transaction | COMMITTED | 421796018417848 | READ COMMITTED  | YES        |
+-----------+-------------+-----------+-----------------+-----------------+------------+
10 rows in set (0.00 sec)


MySQL 14:47:06 root@192.168.0.221 [sys]>select * from performance_schema.metadata_locks ;
+-------------------+--------------------+------------------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE       | OBJECT_SCHEMA      | OBJECT_NAME            | COLUMN_NAME           | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------------+--------------------+------------------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE             | performance_schema | metadata_locks         | NULL                  |       140317723039376 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |              98 |            101 |
| SCHEMA            | performance_schema | NULL                   | NULL                  |       140317724590224 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | dd_schema.cc:108  |              98 |            101 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | column_name           |       140317725246048 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | lock_duration         |       140317725390880 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | lock_status           |       140317722479760 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | lock_type             |       140317722480144 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | object_instance_begin |       140317722479968 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | object_name           |       140317725245648 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | object_schema         |       140317725245808 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | object_type           |       140317722784752 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | owner_event_id        |       140317724827824 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | owner_thread_id       |       140317724827456 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| COLUMN STATISTICS | performance_schema | metadata_locks         | source                |       140317722490432 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576   |              98 |            102 |
| TABLE             | dba                | personal_identity_info | NULL                  |       140316917486288 | SHARED_WRITE        | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |              99 |             60 |
+-------------------+--------------------+------------------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
14 rows in set (0.02 sec)

MySQL 14:50:12 root@192.168.0.221 [sys]>select * from performance_schema.table_handles;
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+---------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME                | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+---------------+
| TABLE       | sys           | sys_config                 |       140317340487016 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info     |       140317340006888 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info     |       140316917541176 |            NULL |           NULL | NULL          | NULL          |
| TABLE       | dba           | personal_identity_info     |       140316986582840 |            NULL |           NULL | NULL          | NULL          |
+-------------+---------------+----------------------------+-----------------------+-----------------+----------------+---------------+---------------+

RC 模式下无表锁,且在事务提交前就释放掉了不符合条件的记录锁

总的来说 RR下无索引更新INNODB引擎的表, 确实是达到表锁的效果.
不过这么说会显得不那么专业,当然忽悠小白,或者是不懂MYSQL的开发人员,还是可以可以的!

如果面对DBA说的话,套用某人的话来说简直是胡说八道!

思考题:

RC隔离级别下,这种无索引更新,最后还是会释放掉那些不符合条件的记录.那么在它上锁到释放锁的过程里,时间里,高并发下,或许会阻塞并发.

那么引擎和服务层是怎么交互的? 按文章头部列子说, 100行,扫描了30行,需要的是10行.

方式一
扫描一行,就返回一行给服务层,然后服务层判断,下达通知,引擎INNODB根据通知判断是继续保持锁,还是释放锁; 然后继续扫描下一行.

方式二
扫描,扫描,把30行全扫描完后,一次性提交给服务层进行判断,然后再释放锁.

思考题2
如果是两表关联更新,这时候JOIN_BUFFER参与关联会采用方式一还是方式二?

这些只能有空的时候调试源码可知一二.
其实大家学会调试源码,就知道真实的情况,就不会被肤浅的人给忽悠了!

最后作为DBA 需要开启安全更新参数,就是防止无主键,无索引,无LIMIT下DML操作

set global sql_safe_updates=on;

Shark曾_小凡仙

关注

24
点赞
踩
7

收藏

觉得还不错? 一键收藏
1
评论
MYSQL RR隔离级别下无索引更新是否表锁?

是啊 MYSQL 以及进化到了8.3.0版本了,普遍都使用5.7和8.0版本.而且还听说 INNODB引擎会把不符合条件的记录给释放锁. 原理大概是这样的,一个表有100行记录,一条UPDATE语句需要更新10行记录. 而这10行记录分散在这100行里, 从第一个符合条件的行到最后一个不符合条件的行大约是30行. 好吧!RC隔离级别下,这种无索引更新,最后还是会释放掉那些不符合条件的记录.那么在它上锁到释放锁的过程里,时间里,高并发下,或许会阻塞并发.套用某人的话说, 我以上都是胡说八道!
复制链接

扫一扫