1.存储过程
1.1.返回table类型
create or replace function public.function_name(v_id bigint, v_name character varying, v_comment text, v_other_key integer)
returns table(id bigint, name text, comment text, other_key integer)
AS $function$
begin
return query
select * from public.table_name;
end;
$function$
language plpgsql;
1.2.返回普通类型
可以是text、double、integer、boolean、void、bigint
create or replace function public.function_name(_key bigint, _qty integer)
returns text
AS $function$
declare
num text;
begin
--sql 代码
return '';
end;
$function$
language plpgsql;
2.视图
2.1.作用
2.2.创建视图
CREATE OR REPLACE VIEW public.view_name
AS SELECT t1.id,
t1.name,
t2.comment_info
FROM table_name1 t1
left JOIN table_name2 t2 on t1.id = t2.id
ORDER BY r.id;
2.3.删除视图
drop view if exists public.view_name;
2.4.查询视图
select * from public.view_name where name = 'fracong'
3.触发器
3.1.定义
在数据库执行每个操作之前或者之后, 触发该触发器,从而执行某个函数
3.2.触发器函数
所谓触发器函数, 其实和function(存储过程)是一样的, 唯一的限制是没有参数并且函数的类型是trigger类型.
create function public.function_name()
returns trigger
as $function$
begin
--sql代码
end;
$function$
language plpgsql;
3.3.触发器
create trigger trigger_name
before|after --执行某个操作之前或者之后触发
insert|update|delete --某个操作
on public.table_name
for each row execute function
public.function_name(); -- 触发器函数
3.4.删除触发器
drop trigger trigger_name on table_name;
4.事务管理
4.1.ACID
- 原子性Atomic
- 一致性Consistent
- 隔离性Isolated
- 持久性Durable
4.2.常用SQL
4.3.实例
begin;
insert into table_name values(1, 'fracong1', 'test1');
insert into table_name values(2, 'fracong2', 'test2');
insert into table_name values(3, 'fracong3', 'test3');
rollback transaction;
commit;
5.并发锁机
5.1.并发问题
5.1.1.脏读
第一个事务未提交, 第二个事务读取之后, 第一个事务发生回滚, 这时第二个事务读取的数据为脏读.
5.1.2.幻读
第一个事务执行修改或删除操作后未提交, 第二个事务读取的时候并插入数据, 导致第一个事务在查看数据之后,发现部分数据并未被修改.
5.1.3.不可重复性读取
第一个事务和第二个事务同时读取数据, 而第一个事务读取之前修改了数据, 这个时候第二个事务的数据读取为老数据, 而第一个事务读取的是新数据.
5.2.隔离级别
| 隔离级别 | 脏读 | 幻读 | 不可重复性读取 |
|---|---|---|---|
| 读未提交 | ✓ | ✓ | ✓ |
| 读已提交 | × | ✓ | ✓ |
| 可重复读 | × | ✓ | × |
| 可重复读 | × | × | × |
PostgreSQL只提供两种隔离级别
1.读已提交, PostgreSQL的默认隔离级别.
2.可串行化, 提供最严格的事务隔离.
6.锁机
6.1.锁的类型
6.1.1.自旋锁SpinLock
使用互斥信号, 与操作系统和硬件环境比较密切.主要特点是封锁的时间较短,没有等待队列和死锁检测机制. 事务结束时不能字段释放该锁.
6.1.2.轻量级锁LWLock
提供对共享存储器的数据的互斥访问.特点有等待队列和无死锁检测. 事务结束时, 自动释放该锁.存在排他模式和共享模式.
排他模式: 用于数据的修改, 确保不会同时对同一资源进行多次更新.
共享模式: 用于读取数据. 当事务在读取资源的时候, 允许多个事务进行读取该资源, 但不允许其他事务对该资源进行修改操作.
6.1.3.常规锁RegularLock
一般数据库事务管理中的锁, 有等待队列, 死锁检测和字段释放锁.
1.访问共享锁
2.行共享锁
3.行排他锁
4.共享更新排他锁
5.共享锁
6.共享行排他锁
7.排他锁
8.访问排他锁
6.2.死锁
当两个或多个任务中, 如果每个任务锁定其他任务试图所带的资源, 就会造成这些任务永久堵塞,从而发生死锁, 使得系统处于死锁状态.
6.2.1.原因
如果两个事务锁定当前使用的资源, 同时在申请访问对方锁定的资源, 导致两个资源的锁都不被释放, 形成死锁状态.
6.2.2.减少死锁
1.事务中批处理尽量缩短时间
2.有次序使用资源
3.死锁超时时间设置
4.使用低的隔离级别,减少持有共享锁时间
6.2.3.LOCK命令
1.共享锁
BEGIN;
LOCK TABLE table_name1,table_name2 in SHARE MODE;
SELECT id FROM table_name1 where name = 'name1';
INSERT INTO table_name2 values(_id_, 'name2');
COMMIT;
2.共享行排他锁
BEGIN;
LOCK TABLE table_name1 in SHARE ROW EXCLUSIVE MODE;
DELETE FROM table_name2 WHERE id in (
SELECT id FROM table_name1 where name ilike '%name%'
);
DELETE FROM table_name1 where name ilike '%name%';
COMMIT;
