search

matrixorigin / matrixone

Hyperconverged cloud-edge native database
https://docs.matrixorigin.cn/en
Apache License 2.0
1.76k stars 273 forks source link

[Bug]: r-w error when update or delete table which operation batch more than 200w on standlone #18286

Open aressu1985 opened 3 weeks ago

aressu1985 commented 3 weeks ago

Is there an existing issue for the same bug?

Branch Name

main

Commit ID

80509e68b5130621f62ce10c1f9c72904f9d4591

Other Environment Information

- Hardware parameters: 64C 256G
- OS type:
- Others:

Actual Behavior

mysql> select count() from update_insert_test; +----------+ | count() | +----------+ | 49599996 | +----------+ 1 row in set (0.02 sec)

mysql> select count() from update_insert_test where area = '安徽'; +----------+ | count() | +----------+ | 2155186 | +----------+ 1 row in set (0.08 sec)

mysql> select count() from update_insert_test where area = '湖北'; +----------+ | count() | +----------+ | 2157985 | +----------+ 1 row in set (0.01 sec)

mysql> select count() from update_insert_test where area_part = '华北'; +----------+ | count() | +----------+ | 8264314 | +----------+ 1 row in set (0.08 sec)

mysql> select count() from update_insert_test where area_part = '华南'; +----------+ | count() | +----------+ | 8264878 | +----------+ 1 row in set (0.11 sec)

mysql> update update_insert_test set in_time = now() where area = '安徽'; Query OK, 2155186 rows affected (34.08 sec)

mysql> update update_insert_test set in_time = now() where area = '安徽'; ERROR 20618 (HY000): r-w conflict mysql> update update_insert_test set in_time = now() where area = '安徽'; ERROR 20618 (HY000): r-w conflict mysql> mysql> mysql> explain analyze uuupdate update_insert_test set in_time = now() where area = '安徽'; ERROR 20618 (HY000): r-w conflict mysql> mysql> mysql> mysql> mysql> explain analyze dddddddelete from update_insert_test where area_part = '华北';

ERROR 20618 (HY000): r-w conflict

mo-log: mo-service.log.zip

Expected Behavior

No response

Steps to Reproduce

DDL:
create table update_insert_test ( 
id bigint auto_increment primary key,
tenant_id varchar(50) NOT null,
parent_id bigint default 0 null,
pack_level int default 0 null,
reel_id varchar(50) NOT NULL comment '物料唯一码',
material_code varchar(50) NOT NULL comment '物料编码',
material_name varchar(1000) null,
material_description varchar(1000) null,
cur_position int default 0 null,
bill_no varchar(50) NULL comment '当前所属未完成单据号',
inventory_bill_no varchar(255) NULL comment '盘点单号',
lot_no varchar(50) NULL comment '内部批次',
date_code varchar(50) NULL comment '生产周期',
feeding varchar(50) NULL comment '供料角度,0,90,180,270',
bin_code varchar(50) NULL comment '照明等级',
chip_bin varchar(255) NULL comment '芯片BIN等级',
chip_device varchar(255) NULL comment '芯片机型',
chip_test_program varchar(255) NULL comment '芯片测试程序',
chip_marking varchar(50) NULL comment '芯片MARKING',
msl varchar(50) NULL comment '物料潮敏等级',
rohs varchar(50) NULL comment '环保属性',
supplier_material_code varchar(255) NULL comment '供应商料号',
supplier_code varchar(50) NULL comment '供应商代码',
supplier_name varchar(50) NULL comment '供应商名称',
supplier_lot_no varchar(50) NULL comment '供应商批次',
csr_material_code varchar(50) NULL comment '客户料号',
csr_code varchar(50) NULL comment '客户代码',
csr_name varchar(50) NULL comment '客户名称',
csr_lot_no varchar(1000) null,
csr_reel_id varchar(1000) null,
manufacturer_code varchar(255) NULL comment '制造商代码',
manufacturer_name varchar(255) NULL comment '制造商名称',
produce_time timestamp NULL comment '生产时间',
receipt_time timestamp NULL comment '收料时间',
inspection_time timestamp NULL comment '送检时间',
in_time timestamp NULL comment '入库时间',
out_time timestamp NULL comment '出库时间',
out_bill_no varchar(200) NULL comment '出库关联的计划',
recovery_time timestamp NULL comment '退库时间',
unpacking_time timestamp NULL comment '拆包时间',
packing_time timestamp NULL comment '封包时间',
count_exposure_time bigint default 0 NULL comment '潮敏物料累计暴露时间',
init_num decimal(15, 6) default 0.000000 NULL comment '初始数量',
cur_num decimal(15, 6) default 0.000000 NULL comment '当前数量',
stock_num decimal(15, 6) NULL comment '物料库存数量，未入库时为空，入库后为物料数量，退库后为物料数量，出库后为0',
unit varchar(50) NULL comment '单位',
warehouse_code varchar(50) null,
area varchar(50) null,
area_part varchar(50) null,
lock_status int null,
lock_order_category varchar(50) null,
lock_order_no varchar(50) null,
forbid_status int NULL comment '隔离状态，0-未隔离，1-已隔离',
forbid_times int default 0 NULL comment '隔离次数',
forbid_time timestamp NULL comment '隔离时间',
scrap_status int NULL comment '报废状态，0-未报废，1-已报废',
scrap_times int default 0 NULL comment '报废次数',
scrap_time timestamp NULL comment '报废时间',
expired_status int NULL comment '超期状态，0-未超期，1-已超期',
expired_times int default 0 NULL comment '超期次数',
expired_time timestamp NULL comment '超期时间',
check_status int NULL comment '检测状态',
light_status int NULL comment '亮灯状态',
light_order_no varchar(255) NULL comment '亮灯工单',
print_times int NULL comment '打印次数',
print_time timestamp NULL comment '打印时间',
register_type int NULL comment '注册类型，1-原材料普通注册，2-客供注册，3-采购注册，4-自制注册，5-委外注册',
source_from varchar(50) NULL comment '物料注册来源',
reference_type varchar(50) NULL comment '注册凭证类型',
reference_no varchar(50) NULL comment '注册凭证号',
reference_entry_id varchar(50) NULL comment '注册凭证号行号',
delivery_order_no varchar(50) NULL comment '送货单号',
remark text NULL comment '备注',
create_user_name varchar(50) NULL comment '创建人员',
create_user bigint NOT NULL comment '创建人员',
create_dept bigint NOT null,
create_time timestamp NOT NULL comment '创建时间',
update_user bigint NOT NULL comment '更新人员',
update_time timestamp NOT NULL comment '更新时间',
update_user_name varchar(50) default '' NULL comment '更新人员',
status int default 0 NULL comment '当前状态 0-正常,1-已拆分、2-已合盘、3-已退库，4-已退货，5-已作废',
is_deleted int default 0 NULL comment '是否已删除',
key area (area),
key area_part (area_part)
) comment '物料唯一码表' collate = utf8mb4_general_ci;

1. login to 129
2. cd /data2/sudong/matrixone 启动mo
3. 建表
3. laod data 
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_1.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_2.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_3.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_4.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_5.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_6.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_7.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_8.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';
load data infile '//data2/sudong/mock-data-generator/data/update_delete_test_9.tbl' into table test.update_insert_test  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' parallel 'true';

Additional information

No response

Wenbin1002 commented 3 weeks ago

在复现过程中跑出来一次w-w，初步推测是transfer page被gc导致的，需要进一步确认 image