mirror of
https://github.com/MariaDB/server.git
synced 2025-07-08 04:18:14 +02:00
18eab4a832
The purpose of non-exclusive locks in a transaction is to guarantee that the records covered by those locks must remain in that way until the transaction is committed. (The purpose of gap locks is to ensure that a record that was nonexistent will remain that way.) Once a transaction has reached the XA PREPARE state, the only allowed further actions are XA ROLLBACK or XA COMMIT. Therefore, it can be argued that only the exclusive locks that the XA PREPARE transaction is holding are essential. Furthermore, InnoDB never preserved explicit locks across server restart. For XA PREPARE transations, we will only recover implicit exclusive locks for records that had been modified. Because of the fact that XA PREPARE followed by a server restart will cause some locks to be lost, we might as well always release all non-exclusive locks during the execution of an XA PREPARE statement. lock_release_on_prepare(): Release non-exclusive locks on XA PREPARE. trx_prepare(): Invoke lock_release_on_prepare() unless the isolation level is SERIALIZABLE or this is an internal distributed transaction with the binlog (not actual XA PREPARE statement). This has been discussed with Sergei Golubchik and Andrei Elkin. Reviewed by: Sergei Golubchik
283 lines
7.6 KiB
Text
283 lines
7.6 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
connection master;
|
|
create table t1 (a int, b int) engine=InnoDB;
|
|
insert into t1 values(0, 0);
|
|
xa start 't';
|
|
insert into t1 values(1, 2);
|
|
xa end 't';
|
|
xa prepare 't';
|
|
xa commit 't';
|
|
connection slave;
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
connection master;
|
|
xa start 't';
|
|
insert into t1 values(3, 4);
|
|
xa end 't';
|
|
xa prepare 't';
|
|
xa rollback 't';
|
|
connection slave;
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
connection master;
|
|
SET pseudo_slave_mode=1;
|
|
create table t2 (a int) engine=InnoDB;
|
|
xa start 't';
|
|
insert into t1 values (5, 6);
|
|
xa end 't';
|
|
xa prepare 't';
|
|
xa start 's';
|
|
insert into t2 values (0);
|
|
xa end 's';
|
|
xa prepare 's';
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
1 1 0 s
|
|
1 1 0 t
|
|
connection master;
|
|
xa commit 't';
|
|
xa commit 's';
|
|
SET pseudo_slave_mode=0;
|
|
connection slave;
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
include/diff_tables.inc [master:t2, slave:t2]
|
|
connection master;
|
|
*** At the start of read-only section gtid list is:
|
|
flush logs;
|
|
show binlog events in 'master-bin.000002' limit 1,1;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000002 # Gtid_list 1 # [0-1-11]
|
|
set @query1="select 1";
|
|
set @query2="select count(*) into @s2 from t1";
|
|
connection master;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
xa start 'ro_2';
|
|
select count(*) into @s2 from t1;
|
|
xa end 'ro_2';
|
|
xa prepare 'ro_2';;
|
|
disconnect master_ro_2;
|
|
connection master;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
xa start 'ro_1';
|
|
select 1;
|
|
1
|
|
1
|
|
xa end 'ro_1';
|
|
xa prepare 'ro_1';;
|
|
disconnect master_ro_1;
|
|
connection master;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
xa start 'ro_2';
|
|
select count(*) into @s2 from t1;
|
|
xa end 'ro_2';
|
|
xa prepare 'ro_2';;
|
|
disconnect master_ro_2;
|
|
connection master;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
xa start 'ro_1';
|
|
select 1;
|
|
1
|
|
1
|
|
xa end 'ro_1';
|
|
xa prepare 'ro_1';;
|
|
disconnect master_ro_1;
|
|
*** 2 prepared xa:s must be in the list:
|
|
connection master;
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
1 4 0 ro_1
|
|
1 4 0 ro_2
|
|
*** Zero prepared xa:s must be in the list:
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
*** At the end of read-only section gtid list has 0 more compare with previous check:
|
|
flush logs;
|
|
show binlog events in 'master-bin.000003' limit 1,1;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000003 # Gtid_list 1 # [0-1-11]
|
|
create database test_ign;
|
|
set @@sql_log_bin = 0;
|
|
create table test_ign.t (a int) engine=InnoDB;
|
|
set @@sql_log_bin = 1;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
xa start 'rw_no_binlog';
|
|
insert into test_ign.t set a=1;
|
|
xa end 'rw_no_binlog';
|
|
xa prepare 'rw_no_binlog';;
|
|
disconnect master_rw_no_binlog;
|
|
*** rw_no_binlog must be in the list:
|
|
connection master;
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
1 12 0 rw_no_binlog
|
|
*** Zero must be in the list:
|
|
connection master;
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
*** At the end of --binlog-ignore-db section gtid list has 2 more:
|
|
flush logs;
|
|
show binlog events in 'master-bin.000004' limit 1,1;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000004 # Gtid_list 1 # [0-1-13]
|
|
connection master;
|
|
create table t3 (a int) engine=innodb;
|
|
*** the disconnected prepare case
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
set @@binlog_format=statement;
|
|
xa start 'rw_binlog_only';
|
|
delete from t3;
|
|
xa end 'rw_binlog_only';
|
|
xa prepare 'rw_binlog_only';
|
|
disconnect master_rw_binlog_only;
|
|
connection master;
|
|
*** rw_binlog_only must be in the list:
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
1 14 0 rw_binlog_only
|
|
*** Zero must be in the list:
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
*** the same connection complete case.
|
|
connection master;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
set @@binlog_format=statement;
|
|
xa start 'rw_binlog_only';
|
|
delete from t3;
|
|
xa end 'rw_binlog_only';
|
|
xa prepare 'rw_binlog_only';
|
|
*** rw_binlog_only must be in the list:
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
1 14 0 rw_binlog_only
|
|
disconnect master_rw_binlog_only;
|
|
*** Zero must be in the list:
|
|
connection master;
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
*** At the end of ineffective in engine section gtid list has 5 more:
|
|
flush logs;
|
|
show binlog events in 'master-bin.000005' limit 1,1;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000005 # Gtid_list 1 # [0-1-18]
|
|
create table tm (a int) engine=myisam;
|
|
connection master;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
xa start 'rw_myisam';
|
|
insert into tm set a=1;
|
|
xa end 'rw_myisam';
|
|
xa prepare 'rw_myisam';;
|
|
disconnect master_rw_myisam;
|
|
connection master;
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
xa start 'rw_myisam';
|
|
insert into tm set a=1;
|
|
xa end 'rw_myisam';
|
|
xa prepare 'rw_myisam';;
|
|
disconnect master_rw_myisam;
|
|
*** rw_myisam prepared must be in the list:
|
|
connection master;
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
1 9 0 rw_myisam
|
|
*** Zero prepared xa:s must be in the list:
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
*** At the end of MyISAM "xa" section gtid list has 7 more compare with previous check:
|
|
flush logs;
|
|
show binlog events in 'master-bin.000006' limit 1,1;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000006 # Gtid_list 1 # [0-1-25]
|
|
connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,;
|
|
set @@session.sql_log_bin = OFF;
|
|
xa start 'skip_binlog';
|
|
insert into t2 values(1);
|
|
xa end 'skip_binlog';
|
|
xa prepare 'skip_binlog';
|
|
disconnect master_skip_binlog;
|
|
*** skip_binlog must be in the list:
|
|
connection master;
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
1 11 0 skip_binlog
|
|
set @@session.sql_log_bin = OFF;
|
|
xa rollback 'skip_binlog';
|
|
set @@session.sql_log_bin = ON;
|
|
include/save_master_gtid.inc
|
|
*** Zero must be in the list:
|
|
connection master;
|
|
xa recover;
|
|
formatID gtrid_length bqual_length data
|
|
*** At the end of skip_log_binb section gtid list has 0 more:
|
|
flush logs;
|
|
show binlog events in 'master-bin.000007' limit 1,1;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000007 # Gtid_list 1 # [0-1-25]
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
connection master;
|
|
drop database test_ign;
|
|
drop table t1, t2, t3, tm;
|
|
#
|
|
# MDEV-26682 slave lock timeout with XA and gap locks
|
|
#
|
|
create table t1 (a int primary key, b int unique) engine=innodb;
|
|
insert t1 values (1,1),(3,3),(5,5);
|
|
connection slave;
|
|
set session tx_isolation='repeatable-read';
|
|
start transaction;
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
3 3
|
|
5 5
|
|
connect m2, localhost, root;
|
|
delete from t1 where a=3;
|
|
xa start 'x1';
|
|
update t1 set b=3 where a=5;
|
|
xa end 'x1';
|
|
xa prepare 'x1';
|
|
connect m3, localhost, root;
|
|
insert t1 values (2, 2);
|
|
-->slave
|
|
connection slave;
|
|
commit;
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
5 5
|
|
connection m2;
|
|
xa rollback 'x1';
|
|
disconnect m2;
|
|
disconnect m3;
|
|
connection master;
|
|
drop table t1;
|
|
create table t1 (id int auto_increment primary key, c1 int not null unique)
|
|
engine=innodb;
|
|
create table t2 (id int auto_increment primary key, c1 int not null,
|
|
foreign key(c1) references t1(c1), unique key(c1)) engine=innodb;
|
|
insert t1 values (869,1), (871,3), (873,4), (872,5), (870,6), (877,7);
|
|
insert t2 values (795,6), (800,7);
|
|
xa start '1';
|
|
update t2 set id = 9, c1 = 5 where c1 in (null, null, null, null, null, 7, 3);
|
|
connect con1, localhost,root;
|
|
xa start '2';
|
|
delete from t1 where c1 like '3%';
|
|
xa end '2';
|
|
xa prepare '2';
|
|
connection master;
|
|
xa end '1';
|
|
xa prepare '1';
|
|
->slave
|
|
connection slave;
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
connection con1;
|
|
xa commit '2';
|
|
disconnect con1;
|
|
connection master;
|
|
xa commit '1';
|
|
drop table t2, t1;
|
|
include/rpl_end.inc
|