mirror of
https://github.com/MariaDB/server.git
synced 2025-08-18 16:31:36 +02:00
9703c90712
Server-level UNIQUE constraints (namely, WITHOUT OVERLAPS and USING HASH) only worked with InnoDB in REPEATABLE READ isolation mode, when the constraint was checked first and then the row was inserted or updated. Gap locks prevented race conditions when a concurrent connection could've also checked the constraint and inserted/updated a row at the same time. In READ COMMITTED there are no gap locks. To avoid race conditions, we now check the constraint *after* the row operation. This is enabled by the HA_CHECK_UNIQUE_AFTER_WRITE table flag that InnoDB sets in the READ COMMITTED transactions. Checking the constraint after the row operation is more complex. First, the constraint will see the current (inserted/updated) row, and needs to skip it. Second, IGNORE operations become tricky, as we need to revert the insert/update and continue statement execution. write_row() (INSERT IGNORE) is reverted with delete_row(). Conveniently it deletes the current row, that is, the last inserted row. update_row(a,b) (UPDATE IGNORE) is reverted with a reversed update, update_row(b,a). Conveniently, it updates the current row too. Except in InnoDB when the PK is updated - in this case InnoDB internally performs delete+insert, but does not move the cursor, so the "current" row is the deleted one and the reverse update doesn't work. This combination now throws an "unsupported" error and will be fixed in MDEV-37233
254 lines
6.8 KiB
Text
254 lines
6.8 KiB
Text
#
|
|
# MDEV-37199 UNIQUE KEY USING HASH accepting duplicate records
|
|
#
|
|
## INSERT
|
|
create table t1 (
|
|
col1 int primary key,
|
|
col2 varchar(3000),
|
|
unique (col2) using hash) engine=innodb;
|
|
# Keep a Read View open to prevent purge
|
|
start transaction;
|
|
select * from t1;
|
|
col1 col2
|
|
connect con1,localhost,root;
|
|
# Create delete marked secondary index Record ('a', 10)
|
|
insert t1 values(10, 'a');
|
|
delete from t1;
|
|
# Insert secondary index key ('a', 15) in the GAP between ('a', 10) and Supremum
|
|
set transaction isolation level read committed;
|
|
set debug_sync="innodb_row_ins_step_enter SIGNAL checked_duplicate WAIT_FOR do_insert";
|
|
insert t1 values(15, 'a');
|
|
connect con2,localhost,root;
|
|
set debug_sync="now WAIT_FOR checked_duplicate";
|
|
# Insert secondary index key ('a', 5) in the GAP between Infimum and ('a', 10)
|
|
set transaction isolation level read committed;
|
|
insert t1 values(5, 'a');
|
|
set debug_sync="now SIGNAL do_insert";
|
|
connection con1;
|
|
ERROR 23000: Duplicate entry 'a' for key 'col2'
|
|
connection default;
|
|
select * from t1;
|
|
col1 col2
|
|
commit;
|
|
select * from t1;
|
|
col1 col2
|
|
5 a
|
|
disconnect con1;
|
|
disconnect con2;
|
|
set debug_sync='RESET';
|
|
drop table t1;
|
|
## INSERT, row-level locking proof
|
|
create table t1 (
|
|
col1 int primary key,
|
|
col2 varchar(3000),
|
|
unique (col2) using hash) engine=innodb;
|
|
# Keep a Read View open to prevent purge
|
|
start transaction;
|
|
select * from t1;
|
|
col1 col2
|
|
connect con1,localhost,root;
|
|
# Create delete marked secondary index Record ('a', 10)
|
|
insert t1 values(10, 'a');
|
|
delete from t1;
|
|
# Insert secondary index key ('a', 15) in the GAP between ('a', 10) and Supremum
|
|
set transaction isolation level read committed;
|
|
set debug_sync="ha_write_row_end SIGNAL checked_duplicate WAIT_FOR do_insert";
|
|
insert t1 values(15, 'a');
|
|
connect con2,localhost,root;
|
|
set debug_sync="now WAIT_FOR checked_duplicate";
|
|
# Insert secondary index key ('a', 5) in the GAP between Infimum and ('a', 10)
|
|
set session innodb_lock_wait_timeout= 1;
|
|
set transaction isolation level read committed;
|
|
insert t1 values(5, 'a');
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
set debug_sync="now SIGNAL do_insert";
|
|
connection con1;
|
|
connection default;
|
|
select * from t1;
|
|
col1 col2
|
|
commit;
|
|
select * from t1;
|
|
col1 col2
|
|
15 a
|
|
disconnect con1;
|
|
disconnect con2;
|
|
set debug_sync='RESET';
|
|
drop table t1;
|
|
## UPDATE
|
|
create table t1 (
|
|
col1 int primary key,
|
|
col2 varchar(3000),
|
|
unique (col2) using hash) engine=innodb;
|
|
start transaction;
|
|
select * from t1;
|
|
col1 col2
|
|
connect con1,localhost,root;
|
|
insert into t1 values(10, 'a');
|
|
delete from t1;
|
|
insert into t1 values( 5, 'b');
|
|
insert into t1 values(15, 'c');
|
|
set transaction isolation level read committed;
|
|
set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
|
|
update t1 set col2='a' where col1=5;
|
|
connect con2,localhost,root;
|
|
set debug_sync="now WAIT_FOR checked_duplicate";
|
|
set transaction isolation level read committed;
|
|
update t1 set col2='a' where col1=15;
|
|
set debug_sync="now SIGNAL do_insert";
|
|
connection con1;
|
|
ERROR 23000: Duplicate entry 'a' for key 'col2'
|
|
connection default;
|
|
select * from t1;
|
|
col1 col2
|
|
commit;
|
|
select * from t1;
|
|
col1 col2
|
|
5 b
|
|
15 a
|
|
disconnect con1;
|
|
disconnect con2;
|
|
set debug_sync='RESET';
|
|
drop table t1;
|
|
## INSERT IGNORE
|
|
create table t1 (
|
|
col1 int primary key,
|
|
col2 varchar(3000),
|
|
unique (col2) using hash) engine=innodb;
|
|
start transaction;
|
|
select * from t1;
|
|
col1 col2
|
|
connect con1,localhost,root;
|
|
insert t1 values(10, 'a');
|
|
delete from t1;
|
|
set transaction isolation level read committed;
|
|
set debug_sync="innodb_row_ins_step_enter SIGNAL checked_duplicate WAIT_FOR do_insert";
|
|
insert ignore t1 values(15, 'a'), (16, 'b');
|
|
connect con2,localhost,root;
|
|
set debug_sync="now WAIT_FOR checked_duplicate";
|
|
set transaction isolation level read committed;
|
|
insert t1 values(5, 'a');
|
|
set debug_sync="now SIGNAL do_insert";
|
|
connection con1;
|
|
Warnings:
|
|
Warning 1062 Duplicate entry 'a' for key 'col2'
|
|
connection default;
|
|
select * from t1;
|
|
col1 col2
|
|
commit;
|
|
select * from t1;
|
|
col1 col2
|
|
5 a
|
|
16 b
|
|
disconnect con1;
|
|
disconnect con2;
|
|
set debug_sync='RESET';
|
|
drop table t1;
|
|
## UPDATE IGNORE
|
|
create table t1 (
|
|
col1 int primary key,
|
|
col2 varchar(3000),
|
|
unique (col2) using hash) engine=innodb;
|
|
start transaction;
|
|
select * from t1;
|
|
col1 col2
|
|
connect con1,localhost,root;
|
|
insert into t1 values(10, 'a');
|
|
delete from t1;
|
|
insert into t1 values( 5, 'b');
|
|
insert into t1 values(15, 'c');
|
|
insert into t1 values( 9, 'd');
|
|
set transaction isolation level read committed;
|
|
set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
|
|
update ignore t1 set col2=chr(92+col1) where col1<=9;
|
|
connect con2,localhost,root;
|
|
set debug_sync="now WAIT_FOR checked_duplicate";
|
|
set transaction isolation level read committed;
|
|
update t1 set col2='a' where col1=15;
|
|
set debug_sync="now SIGNAL do_insert";
|
|
connection con1;
|
|
connection default;
|
|
select * from t1;
|
|
col1 col2
|
|
commit;
|
|
select * from t1;
|
|
col1 col2
|
|
5 b
|
|
9 e
|
|
15 a
|
|
disconnect con1;
|
|
disconnect con2;
|
|
set debug_sync='RESET';
|
|
drop table t1;
|
|
## UPDATE modifying PK
|
|
create table t1 (
|
|
col1 int primary key,
|
|
col2 varchar(3000),
|
|
unique (col2) using hash) engine=innodb;
|
|
start transaction;
|
|
select * from t1;
|
|
col1 col2
|
|
connect con1,localhost,root;
|
|
insert into t1 values(10, 'a');
|
|
delete from t1;
|
|
insert into t1 values( 5, 'b');
|
|
insert into t1 values(15, 'c');
|
|
set transaction isolation level read committed;
|
|
set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
|
|
update t1 set col2='a', col1=4 where col1=5;
|
|
connect con2,localhost,root;
|
|
set debug_sync="now WAIT_FOR checked_duplicate";
|
|
set transaction isolation level read committed;
|
|
update t1 set col2='a' where col1=15;
|
|
set debug_sync="now SIGNAL do_insert";
|
|
connection con1;
|
|
ERROR 23000: Duplicate entry 'a' for key 'col2'
|
|
connection default;
|
|
select * from t1;
|
|
col1 col2
|
|
commit;
|
|
select * from t1;
|
|
col1 col2
|
|
5 b
|
|
15 a
|
|
disconnect con1;
|
|
disconnect con2;
|
|
set debug_sync='RESET';
|
|
drop table t1;
|
|
## UPDATE IGNORE modifying PK
|
|
create table t1 (
|
|
col1 int primary key,
|
|
col2 varchar(3000),
|
|
unique (col2) using hash) engine=innodb;
|
|
start transaction;
|
|
select * from t1;
|
|
col1 col2
|
|
connect con1,localhost,root;
|
|
insert into t1 values(10, 'a');
|
|
delete from t1;
|
|
insert into t1 values( 5, 'b');
|
|
insert into t1 values(15, 'c');
|
|
insert into t1 values( 9, 'd');
|
|
set transaction isolation level read committed;
|
|
set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
|
|
update ignore t1 set col2=chr(92+col1), col1=col1-1 where col1<=9;
|
|
connect con2,localhost,root;
|
|
set debug_sync="now WAIT_FOR checked_duplicate";
|
|
set transaction isolation level read committed;
|
|
update t1 set col2='a' where col1=15;
|
|
set debug_sync="now SIGNAL do_insert";
|
|
connection con1;
|
|
ERROR 42000: UPDATE IGNORE that modifies a primary key of a table with a UNIQUE constraint USING HASH is not currently supported
|
|
connection default;
|
|
select * from t1;
|
|
col1 col2
|
|
commit;
|
|
select * from t1;
|
|
col1 col2
|
|
5 b
|
|
9 d
|
|
15 a
|
|
disconnect con1;
|
|
disconnect con2;
|
|
set debug_sync='RESET';
|
|
drop table t1;
|
|
# End of 10.6 tests
|