PostgreSQL (五) 存储过程、视图、触发器、事物管理和并发锁机

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

  • START TRANSACTION开始新的事务块
  • BEGIN 初始化事务块
  • COMMIT 提交事务
  • ROLLBACK 事务失败时执行回滚操作
  • SET TRANSACTION 设置事务的特性对后面事务没有影响

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;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值