【 使用环境 】测试环境
【 OB or 其他组件 】ob
【 使用版本 】4.1
【问题描述】
在事务中，比如，有两个表A、B，B创建了对A的外键.
事务中，先删除A表，再删B表，正常情况下是可执行的，mysql也可执行（正常情况下，先删A表，再删B表是没问题的，因为B依赖A，所以要先删A）.
但在OceanBase的事务中，删A表正常，删B表报错了，有外键约束.
分析后认为：在OceanBase的事务中，因为还没提交，数据库认为A表还没删，所有B表删除时报错.
后将数据库事务级别改为可重复读，结果一样。
事务级别改可重复读，执行语句为:
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
报错信息：
Cannot delete or update a parent row: a foreign key constraint fails
【复现路径】
【问题现象及影响】
【附件】
我用你的方法并没有在4.1版本上复现 方便提供下你对应的DDL语句吗
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 48
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> use test;
Database changed
obclient [test]> create table aa(a int primary key,b varchar(20));
Query OK, 0 rows affected (0.163 sec)
obclient [test]> create table bb(id int primary key, a_fk int, c varchar(20), FOREIGN KEY (a_fk) REFERENCES aa (a));
Query OK, 0 rows affected (0.174 sec)
obclient [test]> insert into aa values(1,"aa"),(2,"bb"),(3,"cc");
Query OK, 3 rows affected (0.036 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient [test]> insert into bb values(1,1,"aaaa"),(2,2,"bbbb"),(3,3,"ccc");
Query OK, 3 rows affected (0.026 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient [test]> drop table aa;
ERROR 3730 (HY000): Cannot drop table 'aa' referenced by a foreign key constraint 'bb_OBFK_1680786970843957' on table 'bb'
obclient [test]> select version();
+------------------------------+
| version() |
+------------------------------+
| 5.7.25-OceanBase_CE-v4.1.0.0 |
+------------------------------+
1 row in set (0.002 sec)
这是我执行的结果，很奇怪，这个库会出现这个问题，但其他库是好的：
[admin@localhost ~]$ obclient -h127.0.0.1 -P2883 -ucbs@tenant1 -pcbs -Dts_cbs -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 2097432
Server version: OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [ts_cbs]> create table aa(id int primary key, b varchar(20));
Query OK, 0 rows affected (0.106 sec)
obclient [ts_cbs]> create table bb(id int primary key, a_fk int, c varchar(20), FOREIGN KEY (a_fk) REFERENCES aa (id));
Query OK, 0 rows affected (0.153 sec)
obclient [ts_cbs]> insert into aa values(1,"aa"),(2,"bb"),(3,"cc");
Query OK, 3 rows affected (0.042 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient [ts_cbs]> insert into bb values(1,1,"aaaa"),(2,2,"bbbb"),(3,3,"ccc");
Query OK, 3 rows affected (0.028 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient [ts_cbs]> start transaction;
Query OK, 0 rows affected (0.001 sec)
obclient [ts_cbs]> delete from bb where id=1;
Query OK, 1 row affected (0.011 sec)
obclient [ts_cbs]> delete from aa where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
obclient [ts_cbs]>
我用你的步骤没有复现 这个库有什么特别之处吗
应该没有什么特殊处理，都是一起建的库，有什么原因可能引起这个问题呢？我看了事务隔离级别都是一样的，都是可重复读。
还有别的表创建了对这个表的外键吗？
你可以在失败后查询下 select last_trace_id(); 根据trace_id去日志捞下，看下具体日志的报错
[2023-07-19 11:35:59.221947] WDIAG [SQL.ENG] check_exist (ob_table_modify_op.cpp:253) [685][T1_L0_G0][T1][YB427F000001-000600A675E6BEC2-0-0] [lt=34][errcode=-5314] child row is exist(ret=-5314, fk_arg={ref_action:1, database_name:“test”, table_name:“bb”, columns:[{column_name:“a_fk”, index:0, index:-1}], is_self_ref:false}, row=[{type:“T_REF_COLUMN”, datum_meta:{type:“INT”, cs_type:“binary”, scale:0, precision:11}, obj_meta:{type:“INT”, collation:“binary”, coercibility:“NUMERIC”}, obj_datum_map:3, flag:3, eval_func:null, eval_batch_func:null, inner_functions:null, inner_func_cnt:0, arg_cnt:0, parent_cnt:1, frame_idx:1, datum_off:0, res_buf_off:13548, dyn_buf_header_offset:9436, res_buf_len:8, eval_flags_off:9340, pvt_skip_off:3072, expr_ctx_id:4294967295, extra:0, batch_idx_mask:18446744073709551615, this:0x7f0cf102c060}, {type:“T_REF_COLUMN”, datum_meta:{type:“VARCHAR”, cs_type:“utf8mb4_general_ci”, scale:-1, precision:-1}, obj_meta:{type:“VARCHAR”, collation:“utf8mb4_general_ci”, coercibility:“IMPLICIT”}, obj_datum_map:1, flag:3, eval_func:null, eval_batch_func:null, inner_functions:null, inner_func_cnt:0, arg_cnt:0, parent_cnt:0, frame_idx:1, datum_off:3104, res_buf_off:15596, dyn_buf_header_offset:9436, res_buf_len:20, eval_flags_off:9372, pvt_skip_off:6176, expr_ctx_id:4294967295, extra:0, batch_idx_mask:18446744073709551615, this:0x7f0cf102c110}])
[2023-07-19 11:35:59.222007] WDIAG [SQL.ENG] do_handle (ob_table_modify_op.cpp:79) [685][T1_L0_G0][T1][YB427F000001-000600A675E6BEC2-0-0] [lt=36][errcode=-5314] failed to check exist(ret=-5314, fk_arg={ref_action:1, database_name:“test”, table_name:“bb”, columns:[{column_name:“a_fk”, index:0, index:-1}], is_self_ref:false}, old_row=[{type:“T_REF_COLUMN”, datum_meta:{type:“INT”, cs_type:“binary”, scale:0, precision:11}, obj_meta:{type:“INT”, collation:“binary”, coercibility:“NUMERIC”}, obj_datum_map:3, flag:3, eval_func:null, eval_batch_func:null, inner_functions:null, inner_func_cnt:0, arg_cnt:0, parent_cnt:1, frame_idx:1, datum_off:0, res_buf_off:13548, dyn_buf_header_offset:9436, res_buf_len:8, eval_flags_off:9340, pvt_skip_off:3072, expr_ctx_id:4294967295, extra:0, batch_idx_mask:18446744073709551615, this:0x7f0cf102c060}, {type:“T_REF_COLUMN”, datum_meta:{type:“VARCHAR”, cs_type:“utf8mb4_general_ci”, scale:-1, precision:-1}, obj_meta:{type:“VARCHAR”, collation:“utf8mb4_general_ci”, coercibility:“IMPLICIT”}, obj_datum_map:1, flag:3, eval_func:null, eval_batch_func:null, inner_functions:null, inner_func_cnt:0, arg_cnt:0, parent_cnt:0, frame_idx:1, datum_off:3104, res_buf_off:15596, dyn_buf_header_offset:9436, res_buf_len:20, eval_flags_off:9372, pvt_skip_off:6176, expr_ctx_id:4294967295, extra:0, batch_idx_mask:18446744073709551615, this:0x7f0cf102c110}])
[2023-07-19 11:35:59.222057] WDIAG [SQL.ENG] handle_after_row_processing_batch (ob_dml_service.cpp:2033) [685][T1_L0_G0][T1][YB427F000001-000600A675E6BEC2-0-0] [lt=49][errcode=-5314] failed to handle foreign key constraints(ret=-5314)
[2023-07-19 11:35:59.222061] WDIAG [SQL.ENG] submit_all_dml_task (ob_table_modify_op.cpp:1087) [685][T1_L0_G0][T1][YB427F000001-000600A675E6BEC2-0-0] [lt=4][errcode=-5314] perform batch foreign key constraints and after row trigger failed(ret=-5314)
[2023-07-19 11:35:59.222064] WDIAG [SQL.ENG] inner_get_next_row (ob_table_modify_op.cpp:1168) [685][T1_L0_G0][T1][YB427F000001-000600A675E6BEC2-0-0] [lt=3][errcode=-5314] failed to submit the remaining dml tasks(ret=-5314)
提供下具体的版本信息 你的环境还在吗
还在，也是4.1， OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:57)
这个问题已经通过拉小群的方式确认，此问题在早期版本会有这样的问题，升级到最新版本4.1BP2就可以的
请问社区版4.1 有说是什么原因导致的吗？
和外键检查的隔离级别有关，在同一个事务里先删除子表，再删除父表，父表删除失败；看起来是父表检查的时候看到了子表已经删除但是未提交的行；
和外键检查的隔离级别有关系，但是我昨天修改了oceanBase的隔离级别，它默认是RC（读已提交），我改成了RR（可重复读），验证了一下，还是仍然报错，所使用的的oceanBase版本为社区版5.7.25-0ceanBase CE-V4.1.0.0。
请问您最终采取了什么解决方案？还是说使用哪个版本的oceanBase最终解决了这个问题
升级到最新版本4.1BP2就可以解决这个问题
或者等4.1.0_BP3版本 最近会发布