drop table if exists t;
create table t(id int, a int, unique index idx(id));
begin pessimistic;
insert into t values (1,1);
savepoint s1;
insert into t values (2,2);
rollback to s1;
insert into t values (2,2);
select * from t;
id	a
1	1
2	2
rollback to s1;
select * from t;
id	a
1	1
commit;
select * from t;
id	a
1	1
delete from t;
insert into t values (1,1);
begin pessimistic;
delete from t where id = 1;
savepoint s1;
insert into t values (1,2);
rollback to s1;
select * from t;
id	a
commit;
select * from t;
id	a
drop table if exists t;
CREATE TABLE t (a enum('B','C') NOT NULL,UNIQUE KEY idx_1 (a),KEY idx_2 (a));
begin pessimistic;
savepoint sp0;
insert ignore into t values ( 'B' ),( 'C' );
update t set a = 'C' where a = 'B';
Error 1062 (23000): Duplicate entry 'C' for key 't.idx_1'
select * from t where a = 'B' for update;
a
B
rollback to sp0;
delete from t where a = 'B' ;
rollback;
set session tidb_txn_mode='optimistic';
drop table if exists tmp1;
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
insert into tmp1 values(1, 11, 101);
begin;
savepoint sp0;
insert into tmp1 values(2, 22, 202);
savepoint sp1;
insert into tmp1 values(3, 33, 303);
rollback to sp1;
select * from tmp1 order by id;
id	u	v
1	11	101
2	22	202
commit;
select * from tmp1 order by id;
id	u	v
1	11	101
2	22	202
set session tidb_txn_mode='pessimistic';
drop table if exists tmp1;
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
insert into tmp1 values(1, 11, 101);
begin;
savepoint sp0;
insert into tmp1 values(2, 22, 202);
savepoint sp1;
insert into tmp1 values(3, 33, 303);
rollback to sp1;
select * from tmp1 order by id;
id	u	v
1	11	101
2	22	202
commit;
select * from tmp1 order by id;
id	u	v
1	11	101
2	22	202
set session tidb_txn_mode='';
drop table if exists tmp1;
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
insert into tmp1 values(1, 11, 101);
begin;
savepoint sp0;
insert into tmp1 values(2, 22, 202);
savepoint sp1;
insert into tmp1 values(3, 33, 303);
rollback to sp1;
select * from tmp1 order by id;
id	u	v
1	11	101
2	22	202
commit;
select * from tmp1 order by id;
id	u	v
1	11	101
2	22	202
set session tidb_txn_mode='optimistic';
drop table if exists tmp1;
create global temporary table tmp1 (id int primary key auto_increment, u int unique, v int) on commit delete rows;
begin;
savepoint sp0;
insert into tmp1 values(2, 22, 202);
savepoint sp1;
insert into tmp1 values(3, 33, 303);
savepoint sp2;
insert into tmp1 values(4, 44, 404);
rollback to sp2;
select * from tmp1 order by id;
id	u	v
2	22	202
3	33	303
rollback to sp1;
select * from tmp1 order by id;
id	u	v
2	22	202
commit;
select * from tmp1 order by id;
id	u	v
set session tidb_txn_mode='pessimistic';
drop table if exists tmp1;
create global temporary table tmp1 (id int primary key auto_increment, u int unique, v int) on commit delete rows;
begin;
savepoint sp0;
insert into tmp1 values(2, 22, 202);
savepoint sp1;
insert into tmp1 values(3, 33, 303);
savepoint sp2;
insert into tmp1 values(4, 44, 404);
rollback to sp2;
select * from tmp1 order by id;
id	u	v
2	22	202
3	33	303
rollback to sp1;
select * from tmp1 order by id;
id	u	v
2	22	202
commit;
select * from tmp1 order by id;
id	u	v
set session tidb_txn_mode='';
drop table if exists tmp1;
create global temporary table tmp1 (id int primary key auto_increment, u int unique, v int) on commit delete rows;
begin;
savepoint sp0;
insert into tmp1 values(2, 22, 202);
savepoint sp1;
insert into tmp1 values(3, 33, 303);
savepoint sp2;
insert into tmp1 values(4, 44, 404);
rollback to sp2;
select * from tmp1 order by id;
id	u	v
2	22	202
3	33	303
rollback to sp1;
select * from tmp1 order by id;
id	u	v
2	22	202
commit;
select * from tmp1 order by id;
id	u	v
set session tidb_txn_mode=default;