326 lines
6.5 KiB
Plaintext
326 lines
6.5 KiB
Plaintext
create database my_db;
|
|
create user 'u0'@'%';
|
|
grant all privileges on my_db.* to 'u0'@'%';
|
|
grant reload on *.* to 'u0'@'%';
|
|
grant SESSION_VARIABLES_ADMIN on *.* to 'u0'@'%';
|
|
use my_db;
|
|
set session transaction_isolation="READ-COMMITTED";
|
|
use my_db;
|
|
set session transaction_isolation="READ-COMMITTED";
|
|
use my_db;
|
|
set session transaction_isolation="READ-COMMITTED";
|
|
create table t(id int primary key, col1 int);
|
|
insert into t values(1, 1);
|
|
commit;
|
|
create table t2(id int);
|
|
-------------------------------------------------
|
|
test autocommit.
|
|
-------------------------------------------------
|
|
set autocommit =1;
|
|
insert /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t values(1, 1);
|
|
ERROR HY000: Inventory transactinal hints didn't allowed in autocommit mode
|
|
update /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t set col1=2 where id =1;
|
|
ERROR HY000: Inventory transactinal hints didn't allowed in autocommit mode
|
|
delete /*+ commit_on_success rollback_on_fail target_affect_row(1) */ from t;
|
|
ERROR HY000: Inventory transactinal hints didn't allowed in autocommit mode
|
|
-------------------------------------------------
|
|
test sub statement
|
|
-------------------------------------------------
|
|
CREATE TRIGGER tri_1
|
|
before INSERT ON t FOR EACH ROW
|
|
BEGIN
|
|
INSERT /*+ commit_on_success */ INTO t2 values(1);
|
|
END//
|
|
set autocommit = 0;
|
|
insert /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t values(2, 1);
|
|
ERROR HY000: Inventory transactional hints didn't alllowed in stored procedure
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
select * from t2;
|
|
id
|
|
drop trigger tri_1;
|
|
-------------------------------------------------
|
|
test insert
|
|
-------------------------------------------------
|
|
set autocommit = 0;
|
|
insert into t2 values(10);
|
|
insert /*+ commit_on_success rollback_on_fail */ into t values(10, 10);
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
select * from t2;
|
|
id
|
|
10
|
|
set autocommit = 0;
|
|
insert into t2 values(11);
|
|
insert /*+ commit_on_success rollback_on_fail */ into t values(10, 11);
|
|
ERROR 23000: Duplicate entry '10' for key 'PRIMARY'
|
|
commit;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
select * from t2;
|
|
id
|
|
10
|
|
set autocommit = 0;
|
|
insert into t2 values(12);
|
|
insert /*+ commit_on_success rollback_on_fail target_affect_row(1) */ into t values(12, 12);
|
|
commit;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
set autocommit = 0;
|
|
insert into t2 values(13);
|
|
insert /*+ commit_on_success rollback_on_fail target_affect_row(1) */ into t values(13, 13),(14,14);
|
|
ERROR HY000: Inventory conditional hints didn't match with result
|
|
commit;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
-------------------------------------------------
|
|
test update
|
|
-------------------------------------------------
|
|
set autocommit =0;
|
|
insert into t values(30, 30), (31, 31), (32, 32), (33,33);
|
|
commit;
|
|
set autocommit=0;
|
|
insert into t2 values(30);
|
|
update /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t set col1=col1+1 where id =30;
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
30 31
|
|
31 31
|
|
32 32
|
|
33 33
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
set autocommit=0;
|
|
insert into t2 values(31);
|
|
update /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t set col1=col1+1 where id >=30 and id <= 31;
|
|
ERROR HY000: Inventory conditional hints didn't match with result
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
30 31
|
|
31 31
|
|
32 32
|
|
33 33
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
set autocommit=0;
|
|
insert into t2 values(32);
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
30 31
|
|
31 31
|
|
32 32
|
|
33 33
|
|
call dbms_trans.returning("*", "update /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t set col1=col1+1 where id=30");
|
|
id col1
|
|
30 32
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
30 32
|
|
31 31
|
|
32 32
|
|
33 33
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
32
|
|
set autocommit=0;
|
|
insert into t2 values(33);
|
|
call dbms_trans.returning("*", "update /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t set col1=col1+1 where id >=30 and id <= 31");
|
|
ERROR HY000: Inventory conditional hints didn't match with result
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
30 32
|
|
31 31
|
|
32 32
|
|
33 33
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
32
|
|
-------------------------------------------------
|
|
test insert returning
|
|
-------------------------------------------------
|
|
set autocommit = 0;
|
|
insert into t2 values(20);
|
|
call dbms_trans.returning("*", "insert /*+ commit_on_success rollback_on_fail */ into t values(20, 20)");
|
|
id col1
|
|
20 20
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
20 20
|
|
30 32
|
|
31 31
|
|
32 32
|
|
33 33
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
32
|
|
20
|
|
set autocommit = 0;
|
|
insert into t2 values(21);
|
|
call dbms_trans.returning("*", "insert /*+ commit_on_success rollback_on_fail target_affect_row(1) */ into t values(20, 21)");
|
|
ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
|
|
commit;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
20 20
|
|
30 32
|
|
31 31
|
|
32 32
|
|
33 33
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
32
|
|
20
|
|
-------------------------------------------------
|
|
test delete
|
|
-------------------------------------------------
|
|
set autocommit =0;
|
|
insert into t values(50, 50), (51,51);
|
|
commit;
|
|
set autocommit=0;
|
|
insert into t2 values(50);
|
|
delete /*+ commit_on_success rollback_on_fail target_affect_row(1) */ from t;
|
|
ERROR HY000: Inventory conditional hints didn't match with result
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
20 20
|
|
30 32
|
|
31 31
|
|
32 32
|
|
33 33
|
|
50 50
|
|
51 51
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
32
|
|
20
|
|
set autocommit=0;
|
|
insert into t2 values(51);
|
|
delete /*+ commit_on_success rollback_on_fail target_affect_row(1) */ from t where id =50;
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
20 20
|
|
30 32
|
|
31 31
|
|
32 32
|
|
33 33
|
|
51 51
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
32
|
|
20
|
|
51
|
|
set autocommit=0;
|
|
insert into t2 values(52);
|
|
call dbms_trans.returning("*", "delete /*+ commit_on_success rollback_on_fail target_affect_row(1) */ from t where id =51");
|
|
id col1
|
|
51 51
|
|
rollback;
|
|
select * from t;
|
|
id col1
|
|
1 1
|
|
10 10
|
|
12 12
|
|
20 20
|
|
30 32
|
|
31 31
|
|
32 32
|
|
33 33
|
|
select * from t2;
|
|
id
|
|
10
|
|
12
|
|
30
|
|
32
|
|
20
|
|
51
|
|
52
|
|
-------------------------------------------------
|
|
test explain
|
|
-------------------------------------------------
|
|
set autocommit=0;
|
|
explain update /*+ commit_on_success rollback_on_fail target_affect_row(-1) */ t set col1=col1+1 where id >=30 and id <= 31;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t NULL range PRIMARY PRIMARY 4 const 2 100.00 Using where
|
|
Warnings:
|
|
Warning 1064 Optimizer hint syntax error near '-1) */ t set col1=col1+1 where id >=30 and id <= 31' at line 1
|
|
Note 1003 update /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ `my_db`.`t` set `my_db`.`t`.`col1` = (`my_db`.`t`.`col1` + 1) where ((`my_db`.`t`.`id` >= 30) and (`my_db`.`t`.`id` <= 31))
|
|
drop user 'u0'@'%';
|
|
drop database my_db;
|