mysql - `InnoDB` 在没有`insert` 或`update` 操作挂起的表上加锁
问题描述
我目前有一个漫长的过程,将行插入表中,该表A
源自 和 之间的复杂连接A
查询B
。自此过程开始以来,所有insert
对表的操作B
都已超时。这就是我这样做时显示的内容SHOW ENGINE INNODB STATUS
:
---TRANSACTION 3597993, ACTIVE 108 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 173507, OS thread handle 140455303333632, query id 1051475 10.0.0.6 root update
INSERT INTO `B` (`id`, `time`, `elem_1`, `elem_2`) VALUES (251322486, '2018-04-27 19:29:59', 'PTY', 'USD')
------- TRX HAS BEEN WAITING 108 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 5859 n bits 96 index PRIMARY of table `default`.`B` trx id 3597993 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
我对InnoDB
规则的理解是,不应该发生锁B
,因为唯一正在运行的进程是从中读取,而不是更新或插入。
正在进行的插入操作具有以下结构:
insert into `default`.A
select * from (select i1.id, min(IFNULL(i2.id,-1)) as next_id, i1.item_1, i1.item_2 from
(select idx.id, idx.item_2, idx.item_2
from `default`.B idx left join `default`.A map on
idx.id = map.id and idx.item_1 = map.item_1 where map.id is null or map.next_id = -1) i1 left join
`default`.B i2 on i1.id < i2.id and i1.item_1 = i2.item_1 and i1.item_2 = i2.item_2
group by i1.id, i1.item_1, i1.item_2) new_entries
on duplicate key update next_id = new_entries.next_id
这是我正在使用的数据库版本:
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+--------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------+
| innodb_version | 5.7.21 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 10.2.13-MariaDB-10.2.13+maria~jessie |
| version_comment | mariadb.org binary distribution |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| version_malloc_library | system |
| version_ssl_library | OpenSSL 1.0.1t 3 May 2016 |
| wsrep_patch_version | wsrep_25.21 |
+-------------------------+--------------------------------------+
更新
我停止了长期存在的操作并重新启动它,现在插入似乎畅通无阻,所以我倾向于考虑这是一些暂时的错误
解决方案
如果您以某种方式使用 SELECT 的结果将数据保存到表或变量中,InnoDB 确实会在您 SELECT 的表中的行上创建共享锁。
INSERT INTO T1 SELECT ... FROM T2 ...;
CREATE TABLE T1 AS SELECT ... FROM T2 ...;
SET @my_variable = (SELECT ... FROM T2 ...);
SELECT ... INTO @my_variable, ... FROM T2 ...;
在上述所有情况下,T2 中的行都获得共享锁。共享意味着多个并发语句可以在同一行上拥有共享锁,但是当这些行上存在任何共享锁时,没有并发语句可以在一组行上获得排他锁。
见https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
推荐阅读
- sql - Oracle 程序所有输入变量都设置为大写
- java - Export spring desktop application as jar to be used as utility
- javascript - 加入数组Javascript
- php - Laravel : change the contents of the variable with the database field
- python - Numpy 字符串数组既有字符串又有数组
- python - TensorFlow:具有两个 GPU 的“mnist_replica.py”比单个 GPU 运行时间更长
- python - 无法在pygame中的按钮上添加文本
- python - How to make python package refer to packaged, non-python executable (.exe); Windows
- swift - 如何打印我刚刚记录并保存到 Healthkit 商店的 HKQuantity 类型的值?
- html - IE11 上的表上存在 Flexbox 问题