mariadb 为什么选择…FOR SHARE没有被另一个事务的UPDATE阻塞？

9rygscc1  于 2023-08-05  发布在  其他
关注(0)|答案(3)|浏览(114)

架构：

CREATE TABLE `table0` (
  `pkId` int DEFAULT NULL,
  `pkAttr0` int NOT NULL,
  `coAttr0_0` int DEFAULT NULL,
  PRIMARY KEY (`pkAttr0`),
  KEY `table0index_pk` (`pkAttr0`),
  KEY `table0index_commAttr0` (`coAttr0_0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into table0 values(1,1,1);

字符串
然后执行：

session1 > begin;
session1 > update table0 set coAttr0_0=2;
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked

型
session1 > update table0 set coAttr0_0=2;已经锁定了该行，为什么session2 > select pkAttr0 from table0 for share;仍然可以执行？

mariadb

3条答案

按热度按时间
laik7k3q

laik7k3q1#

我不是锁方面的Maven，但我认为这取决于这样一个事实，即您正在select-ing一个不涉及update操作的属性，而select *将需要首先释放锁，因为coAttr0_0（可能）正在更新。

赞(0）分享  回复(0）举报  2023-08-05
zengzsys

zengzsys2#

检查自动提交模式：
说明书：
如果autocommit设置为1（默认值），则LOCK IN SHARE MODE和FOR UPDATE子句无效。
https://mariadb.com/kb/en/lock-in-share-mode/

赞(0）分享  回复(0）举报  2023-08-05
7vhp5slm

7vhp5slm3#

这个答案是基于我的测试。我发现，如果选择列表中的所有列（不包括被更新的列，在本例中为列coAttr0_0）都包含在单个索引中，则SELECT for share不会被阻塞，而不管它是否唯一。
根据更新是否实际更改了值，如果更改了，则索引中的列必须是精确的（例如，select子句中只有一列，则该列必须有单列索引）。覆盖该列的复合索引将不计算在内，即使该列是索引中最左侧的列）。如果没有进行实际的更改，则索引中的列只需是包含性的（例如，select子句中有两列，则索引可以是包含这两列的5列索引）。
这是使用表结构的索引。

+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          0 | PRIMARY               |            1 | pkAttr0     | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | table0index_pk        |            1 | pkAttr0     | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

字符串
正如我们所看到的，列pkId没有索引。让我们开始测试：

session1 > begin;
session1 > update table0 set coAttr0_0=2;
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked
session2 > select pkId from table0 for share; -- blocked
session2 > select coAttr0_0 from table0 for share; -- blocked

型
如上所述，列pkId（未索引）和coAttr0_0（挂起的更新）都不成功。
现在，我们删除pkAttr 0得PK属性与索引，同时添加pkId得索引.然后再次测试。注：在我们进行结构更改之前，应提交Commit。

session1 > commit;
session2 > commit;

alter table table0 drop primary key;
drop index table0index_pk on table0;
create index idx_pkid on table0(pkId);

 show index in table0;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_pkid              |            1 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

型
现在我们只有pkId和coAttr0_0的索引。让我们继续测试：

session1 > begin;
session1 > update table0 set coAttr0_0=20; -- value to be changed from 2 to 20
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- blocked
session2 > select * from table0 for share; -- blocked
session2 > select pkId from table0 for share; -- is not blocked
session2 > select coAttr0_0 from table0 for share; -- blocked

session1 > commit;
session2 > commit;

型
但是，如果我们运行一个实际上并不改变值的update语句会怎样呢？例如，在上一步中，我们将coAttr0_0从2更改为20。如果我们做了同样的更新语句，实际上并没有改变什么呢？

session1 > begin;
session1 > update table0 set coAttr0_0=20; -- the new value is the same as the old one so no change is made
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- blocked
session2 > select * from table0 for share; -- blocked
session2 > select pkId from table0 for share; -- is not blocked
session2 > select coAttr0_0 from table0 for share; -- is not blocked !!

session1 > commit;
session2 > commit;

型
这一次，不会封锁select coAttr0_0 from table0 for share;陈述式。
接下来，我们将为pkAttr0创建一个密钥并进行更深入的研究。

create index idx_pkattr0 on table0(pkAttr0);
show index in table0;

+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_pkid              |            1 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_pkattr0           |            1 | pkAttr0     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

session1 > begin;
session1 > update table0 set coAttr0_0=200; -- value from 20 to 200
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked
session2 > select pkId from table0 for share; -- is not blocked
session2 > select coAttr0_0 from table0 for share; -- blocked 

-- Pay attention to this one
session2 > select pkId,pkAttr0 from table0 for share; -- blocked

session1 > commit;
session2 > commit;

型
如上所强调的，当select子句同时具有pkId和pkAttr 0时，即使对这两列分别进行了索引，该语句也会被阻塞。现在，让我们创建一个复合索引来覆盖这两个列。

create index idx_both on table0( pkAttr0,pkId);

 show index in table0;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_pkid              |            1 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_pkattr0           |            1 | pkAttr0     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_both              |            1 | pkAttr0     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_both              |            2 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

session1 > begin;
session1 > update table0 set coAttr0_0=2000; -- value from 200 to 2000
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked
session2 > select pkId from table0 for share; -- is not blocked
session2 > select coAttr0_0 from table0 for share; -- blocked 

-- see what happens this time:
session2 > select pkId,pkAttr0 from table0 for share; -- is not blocked !!

session1 > commit;
session2 > commit;

型
正如我们所看到的，对于复合索引，包含两列的select子句没有被阻塞。
但是，如果我们删除pkId和pkAttr 0的各个索引，会怎样呢？这会改变输出吗？

drop index idx_pkid on table0;
drop index idx_pkattr0 on table0;

show index in table0;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_both              |            1 | pkAttr0     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_both              |            2 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

session1 > begin;
session1 > update table0 set coAttr0_0=2000; -- value does not change this time
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked

-- BOOKMARK this one
session2 > select pkId from table0 for share; -- is not blocked

session2 > select coAttr0_0 from table0 for share; -- is not blocked 
session2 > select pkId,pkAttr0 from table0 for share; -- is not blocked 

session1 > commit;
session2 > commit;

型
如上所示，在我们删除了列pkId和pkAttr0的各个索引之后，输出与上一个测试相同（除了这次选择coAttr0_0的索引没有被阻止，因为没有发生实际的更改）。
现在，让我们将注意力转移到带有书签的查询上。即select pkId from table0 for share;。有趣的是，与选择pkAttr0的那个不同，由于索引利用的最左边前缀规则，带有pkId的这个不使用索引来执行，因为现在只剩下复合索引，并且pkAttr0是索引序列中的第一个。因此，只要索引包含该列，索引序列在阻塞方面就没有关系。
最后，让我们删除当前的复合索引，并创建一个包含所有列的新索引。

drop index idx_both on table0;
create index idx_all on table0(coAttr0_0,pkId,pkAttr0);

show index in table0;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all               |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all               |            2 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all               |            3 | pkAttr0     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

session1 > begin;
session1 > update table0 set coAttr0_0=2000; -- value still does not change 
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- is not blocked !!

session2 > select pkId from table0 for share; -- is not blocked
session2 > select coAttr0_0 from table0 for share; -- is not blocked 
session2 > select pkId,pkAttr0 from table0 for share; -- is not blocked 

session1 > commit;
session2 > commit;

型
最后，查询select * from table0 for share;这一次没有被阻塞。当然，如果UPDATE语句实际上更改了值，则除了选择列表中包含coAttr0_0的那些值之外，结果都是相同的。人们通常会这么想。错误！！

session1 > begin;
session1 > update table0 set coAttr0_0=32167; -- value changed from 2000 
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- blocked !!
session2 > select * from table0 for share; -- blocked 

session2 > select pkId from table0 for share; -- blocked !!
session2 > select coAttr0_0 from table0 for share; -- blocked 
session2 > select pkId,pkAttr0 from table0 for share; -- blocked !!
 

session1 > commit;
session2 > commit;

型
由于coAttr0_0的值实际上已更改，因此在select for share语句中不允许包含所有列的复合索引中的列。我们为列添加一个单独的索引如何？这一次我们为pkAttr0创建一个。此外，列coAttr0_0应更改回2000。

create index idx_pkAttr0 on table0(pkAttr0);

show index in table0;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all               |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all               |            2 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all               |            3 | pkAttr0     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_pkAttr0           |            1 | pkAttr0     | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

session1 > begin;
session1 > update table0 set coAttr0_0=2000; -- value changed from 32167 back to 2000 
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked 
session2 > select * from table0 for share; -- blocked 

session2 > select pkId from table0 for share; -- blocked 
session2 > select coAttr0_0 from table0 for share; -- blocked 
session2 > select pkId,pkAttr0 from table0 for share; -- blocked 
 

session1 > commit;
session2 > commit;

型
在为pkAttr0创建了一个单独的索引后，当coAttr0_0的值发生更改时，select pkAttr0 from table0 for share;不会被阻止。如果没有列pkId的单个索引，select pkId from table0 for share;将被阻塞。
索引序列真的不重要吗？让我们通过删除复合索引和单独索引（对于pkAttr0）来找出答案，然后创建一个全新的复合索引，该索引覆盖所有列，但顺序不同。

drop index idx_all on table0;
drop index idx_pkAttr0 on table0;

create index idx_all_new on table0 (pkId,pkAttr0,coAttr0_0);

show index in table0;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all_new           |            1 | pkId        | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all_new           |            2 | pkAttr0     | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all_new           |            3 | coAttr0_0   | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

session1 > begin;
session1 > update table0 set coAttr0_0=9999; -- value changed from 2000 to 9999
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- blocked 
session2 > select * from table0 for share; -- blocked 

session2 > select pkId from table0 for share; -- blocked 
session2 > select coAttr0_0 from table0 for share; -- blocked 
session2 > select pkId,pkAttr0 from table0 for share; -- blocked 

session1 > commit;
session2 > commit;

型
如上所示，即使我们将pkId放在复合索引的第一位，当coAttr0_0发生变化时，select pkId from table0 for share;仍然被阻止。好的。让我们为pkId创建一个单独的索引。

create index idx_pkid on table0(pkId);

show index in table0;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table0 |          1 | table0index_commAttr0 |            1 | coAttr0_0   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all_new           |            1 | pkId        | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all_new           |            2 | pkAttr0     | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_all_new           |            3 | coAttr0_0   | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| table0 |          1 | idx_pkid              |            1 | pkId        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

session1 > begin;
session1 > update table0 set coAttr0_0=7777; -- value changed from 9999 to 7777
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- blocked 
session2 > select * from table0 for share; -- blocked 

session2 > select pkId from table0 for share; -- no longer blocked !! 
session2 > select coAttr0_0 from table0 for share; -- blocked 
session2 > select pkId,pkAttr0 from table0 for share; -- blocked 

session1 > commit;
session2 > commit;

型
对于pkId的单独索引，当coAttr0_0发生更改时，select pkId from table0 for share;不再被阻止。
现在让我们做一个最后的测试。（这次是真实的的）在最后一次测试中，所有的索引都和上次一样，但是coAttr0_0这次没有改变。

session1 > begin;
session1 > update table0 set coAttr0_0=7777; -- value does not change
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked 
session2 > select * from table0 for share; -- is not blocked 

session2 > select pkId from table0 for share; -- is not blocked even without an individual index for itself 
session2 > select coAttr0_0 from table0 for share; -- is not blocked 
session2 > select pkId,pkAttr0 from table0 for share; -- is not blocked 

session1 > commit;
session2 > commit;

型

结束语

当数据行更新未变更值时，只要选取清单中的所有数据行都包含在单一索引中，就不会封锁select for share陈述式，该索引的数据行数目可以比选取子句中的数据行数目还多。
但是，当update确实更改了列的值时，仅当选择列表中的列与索引中的列之间可能存在精确匹配时，才不会阻止select for share语句。例如，pkId的单列索引必须存在，查询select pkId from table0 for share;才能成功运行。在这种情况下，包含pkId的复合索引将不起作用。也就是说，不能多，不能少。

但是，在这两种情况下，复合索引中的列序列在阻塞方面都是微不足道的。

赞(0）分享  回复(0）举报  2023-08-05
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com