数据库的事务 及分区表的概念与使用

数据库的事务
什么是事务：在数据库里面，处理数据的最小的功能模块
转账的功能：验证账号、验证密码、a转出、b转入等等
begin
事务处理的sql语句和代码;
end;

declare
user1 varchar2(20);
user2 varchar2(20);
money number;
c number;
s1 number;
s2 number;
s1_2 number;
s2_2 number;
begin
user1:=‘&输入你的名字’;
user2:=‘&输入对方的名字’;
money:=&输入转账金额;

–验证a有没有这个用户
begin
select count(1) into c from scott.emp where ename=user1;
if c=0 then
raise_application_error(-20001,‘没有这个账号’);
end if;
end;

–验证b有没有这个用户
begin
select count(1) into c from scott.emp where ename=user2;
if c=0 then
raise_application_error(-20001,‘没有这个账号’);
else
select sal into s2 from scott.emp where ename=user2;
end if;
end;

–验证a有没有这么多钱
begin
select sal into s1 from scott.emp where ename=user1;
if s1<money then
raise_application_error(-20002,‘余额不足’);
else
update scott.emp set sal=sal-money where ename=user1;
update scott.emp set sal=sal+money where ename=user2;
end if;
end;

–验证是否转出成功，验证是否转入成功
begin
select sal into s1_2 from scott.emp where ename=user1;
select sal into s2_2 from scott.emp where ename=user2;
if s1_2=s1-money and s2_2=s2+money then
commit;
dbms_output.put_line(‘转账成功’);
else
rollback;
end if;
end;
end;

事务的几个特征：
原子性：每一个事务都是最小的功能模块
一致性：同时成功同时失败
隔离性：同时操作事务的时候，事务之间不会互相影响
持久性：通过事务操作的数据，是永久保存的
数据库的锁：
为了避免多个用户对同一个数据同时进行操作，操作数据的时候数据保存出错产生脏数据
从锁的范围上看
行锁：
A用户在对数据做dml操作的时候，数据会被上锁，B用户不能对这个数据所在的行同时进行操作
表锁：
A用户在对数据做dml操作的时候，数据所在的表会被上锁，B用户不能对表做ddl（除了新增表格结构之外）的操作

从上锁的主动性和被动性看
悲观锁：通过for update提前占用数据的资源
select 语句 + for update;
select * from scott.emp where ename=‘SMITH’ for update;

乐观锁：数据库默认的就是乐观锁

数据库的死锁：
同时操作的进程里面，每个进程都互相占用了对方的资源，谁都无法释放资源和成功跑完

怎么解决死锁的问题？
–查看所有被上了锁的对象 69539
select * from vKaTeX parse error: Expected 'EOF', got '#' at position 132: …-找锁的id的 serial#̲ 9555 select *…session where sid=131;
–根据这两个编号，杀掉这个锁
alter system kill session ‘131,9555’;
分区表和表分区：***
表分区：将一个表里面，所有的拥有相同特征的数据，在硬盘上规划出一块区域，整合在一起
分区表：拥有表分区的划分规律的表格
表分区的类型划分：
1. 散列分区：hash 用来划分那么没有什么规律的列
create table emp_hash(
empno number,
ename varchar2(20),
job varchar2(20),
mgr number,
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number,
mobile char(11)
)partition by hash(ename) partitions 4;
通过数据库的内部的哈希算法，将所有的行，根据名字的内容，一共放到4个不同的分区中进行分开的保存。哈希算法的分区的数量，最好是写2的次方数。

2. 列表分区 list
create table emp_list(
empno number,
ename varchar2(20),
job varchar2(20),
mgr number,
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number,
mobile char(11)
)partition by list(deptno)
(
partition dept10 values(10),
partition dept20 values(20),
partition dept30 values(30)
);
当某个列的值，是大量的重复的相同数据数据，使用列表分区，对这个列进行特征和内容的归纳和划分，将拥有相同数据的行，放在同一个分区中

3. 范围分区 range
create table emp_range(
empno number,
ename varchar2(20),
job varchar2(20),
mgr number,
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number,
mobile char(11)
)partition by range(sal)
(
partition sal_0_1000 values less than (1001),
partition sal_1001_2000 values less than (2001),
partition sal_2001_3000 values less than (3001),
partition sal_3001 values less than (maxvalue)
);
日期分区
partition s20201125 values less than(date’2020-11-26’),
partition s20201126 values less than(date’2020-11-27’)
数字类型、日期类型的数据，例如金额、时间、年龄等等，将某个范围内的数据，存放在一起，那么就使用范围分区

给表格添加140W行的数据：
declare
begin
for i in 1…100000 loop
insert into emp_range select * from scott.emp;
commit;
end loop;
end;

如果一个表的数据越来越大，查询很慢的话，重新创建一个分区表，复制原有的数据，删除旧表。

4. 组合分区
create table 表名(
列名 数据类型
)partition by 父分区类型(列名)
subpartition by 子分区类型(列名)
(
partition 父分区名字 values 分区规则(
subpartition 子分区名字 values 分区规则，
subpartition 子分区名字 values 分区规则，
…
)
);

create table emp_zuhe(
empno number,
ename varchar2(20),
job varchar2(20),
mgr number,
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number,
mobile char(11)
)partition by list(deptno)
subpartition by range(sal)
(
partition d10 values(10)(
subpartition sal1000_10 values less than (1001),
subpartition sal2000_10 values less than (2001),
subpartition salmax_10 values less than (maxvalue)
),
partition d20 values(20)(
subpartition sal1000_20 values less than (1001),
subpartition sal2000_20 values less than (2001),
subpartition salmax_20 values less than (maxvalue)
),
partition d30 values(30)(
subpartition sal1000_30 values less than (1001),
subpartition sal2000_30 values less than (2001),
subpartition salmax_30 values less than (maxvalue)
)
);

首先划分父分区，设置父分区的规则，然后在父分区中划分子分区的规则，这样可以在同时对两个列进行查询的时候，有效的减少查询需要消耗的资源和时间。

2000W以上的数据，就需要划分分区表了。

查看分区
select * from user_indexes; --查看所有的索引
select * from user_tables; --查看所有的表
select * from user_tab_partitions; --查看所有的分区表
select * from user_procedures

alter table 表名 drop partition 分区名;

alter table 表名 add partition 分区名 values 设置的规则;