事务的四大特性
原子性:
1.设置会话session为非自动提交
set auto_commit off
2.创建基础表
create table test_11(c int primary key,d int);
3.插入测试数据
insert into test_11 values(1,1);
insert into test_11 values(2,2);
4.回滚插入数据
rollback;
5.查询表数据
select * from test_11;
6.再次插入测试数据
insert into test_11 values(1,1);
insert into test_11 values(2,2);
7.提交插入数据
commit;
8.查询表数据
select * from test_11;
一致性:
1.创建测试表:
create table test_1(a int not null,b int,c char(8));
2.设置会话session1为非自动提交,session1:
set auto_commit off;
3.在会话session1中插入数据
insert into test_1 values(1,11,'test1')(2,12,'test2');
4.设置会话session2为非自动提交,session2:
set auto_commit off;
5.会话session2中查询表test_1数据
select * from test_1;
6.切换回会话session1执行提交操作,session1:
commit;
7.切换回会话session2再次执行查询操作,session2:
select * from test_1;
隔离性:
1.设置会话session1为非自动提交,session1:
set auto_commit off;
2.在会话session1中插入数据
insert into test_1 values(3,13,'test3');
3.设置会话session2为非自动提交,session2:
set auto_commit off;
4.在会话session2中插入数据
insert into test_1 values(3,33,'test333');
5.切换回会话session1中查询数据,session1:
select * from test_1 where a=3;
6.切换回会话session2中查询数据,session2:
select * from test_1 where a=3;
7.切换回会话session1中执行提交操作,session1:
commit;
8.切换回会话session2中执行提交操作,session2:
commit;
9.切换回会话session1中执行查询操作,session1:
select * from test_1 where a=3;
10.切换回会话session2中执行查询操作,session2:
select * from test_1 where a=3;
持久性:
关闭数据库后重启执行查询操作
select * from test_1;
事务回滚点:
1.创建基础测试表并插入数据:
create table test_save_point_tab(id int);
insert into test_save_point_tab values(1)(2);
commit;
2.设置会话为非自动提交
set auto_commit off
3.插入数据设置回滚点s1:
insert into test_save_point_tab values(3)(4);
savepoint s1;
4.再次插入数据设置回滚点s2:
insert into test_save_point_tab values(5)(6);
savepoint s2;
5.再次插入数据:
insert into test_save_point_tab values(7)(8);
6.查询当前数据
select * from test_save_point_tab;
7.回滚到回滚点s2并查询数据:
rollback to s2;
select * from test_save_point_tab;
8.回滚到回滚点s1并查询数据:
rollback to s1;
select * from test_save_point_tab;
9.全部回滚并查询数据:
rollback;
select * from test_save_point_tab;
无限制行级锁:
1.创建测试表:
create table test_lock(id int,name varchar);
2.插入基础数据:
insert into test_lock values(1,'test1') (2,'test2') (3,'test3');
3.session1锁定id为1的数据
Session1:set auto_commit off;
select * from test_lock where id=1 for update;
4.session2查询锁定id为1的数据并未被限制:
select * from test_lock where id=1;
5.session2更新未锁定数据并未等待session1放锁
update test_lock set name='xyz' where id!=1;
6.session2更新锁定数据处于等待session1放锁状态
update test_lock set name='xyz' where id=1;
7.session1执行commit放锁后,session2执行成功
commit;
死锁检测:
1.创建测试表并插入基础数据
create table tab_dead_lock(id int,name varchar(20));
insert into tab_dead_lock values(1,'a')(2,'b');
2.session1非自动提交下更新id为1的1行数据
session1:
set auto_commit off
update tab_dead_lock set id=10 where id=1;
3.session2非自动提交下更新id为2的1行数据
session2:
set auto_commit off
update tab_dead_lock set id=20 where id=2;
4.回到session1更新id为2的1行数据,此时控制台处于等待现象
session1:
update tab_dead_lock set id=20 where id=2;
5.回到session2更新id为1的1行数据,控制台等待几秒后报错发生死锁,事务被回滚自动解除死锁
session2:
update tab_dead_lock set id=10 where id=1;
session1:
数据一致性:
1.创建测试表:
create table test_data(c1 int,c2 varchar);
2.插入基础测试数据:
declare
begin
for i in 1..100 loop
insert into test_data values(i,'test'||i);
end loop;
end;
3.查询数据分布节点情况:
select STORE_STA,STORE_NUM,NODE_ID1,NODE_ID2,NODE_ID3 from sys_gstores where head_no=(select gsto_no from sys_tables where table_name='TEST_DATA');
select STORE_STA,NODEID from sys_all_stores where gsto_no=(select gsto_no from sys_tables where table_name='TEST_DATA');
4.模拟主版本存储节点出现故障(kill -9 进程)
5.查询版本状态:
select STORE_STA,STORE_NUM,NODE_ID1,NODE_ID2,NODE_ID3 from sys_gstores where head_no=(select gsto_no from sys_tables where table_name='TEST_DATA');
select STORE_STA,NODEID from sys_all_stores where gsto_no=(select gsto_no from sys_tables where table_name='TEST_DATA');
6.各节点分别查询数据量
select count(*) from test_data;