过程大致意思:
验证:func 函数中依次调用 funca,funcb函数,三个函数中均有update操作,看看三个函数的select 是否可查看未提交的update
表:
CREATE TABLE public.audit
(
id text COLLATE pg_catalog."default",
name text COLLATE pg_catalog."default"
)
任意插入三行数据
定义三个函数如下:
-- FUNCTION: public.func()
-- DROP FUNCTION public.func();
CREATE OR REPLACE FUNCTION public.func(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare newid text = '10';
declare newname text='jack';
declare oldid text;
declare oldname text;
declare rowcursor refcursor;
begin
update audit set id = newid,name = newname where 1=1;
execute funca();
execute funcb();
--子事务update 在主事务中是否可以看到
open rowcursor for select * from audit;
loop
fetch rowcursor into oldid,oldname;
if found then
raise notice 'func:id,name: %,%',oldid,oldname;
else
exit;
end if;
end loop;
close rowcursor;
end;
$BODY$;
-- FUNCTION: public.funca()
-- DROP FUNCTION public.funca();
CREATE OR REPLACE FUNCTION public.funca(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare oldid text;
declare oldname text;
declare rowcursor refcursor;
begin
--看看主事务的update 在子事务中是否勀查看
open rowcursor for select * from audit;
loop
fetch rowcursor into oldid,oldname;
if found then
raise notice 'funca:id,name,state: %,%',oldid,oldname;
else
exit;
end if;
end loop;
close rowcursor;
--子事务更新,看看另一个子事务和主事务是否能看到
update audit set id = '11' where id is not null;
end;
$BODY$;
-- FUNCTION: public.funcb()
-- DROP FUNCTION public.funcb();
CREATE OR REPLACE FUNCTION public.funcb(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare oldid text;
declare oldname text;
declare rowcursor refcursor;
begin
--子事务a的update 在子事务b中是否查看
open rowcursor for select * from audit;
loop
fetch rowcursor into oldid,oldname;
if found then
raise notice 'funcb:id,name,state: %,%',oldid,oldname;
else
exit;
end if;
end loop;
close rowcursor;
end;
$BODY$;
ALTER FUNCTION public.funcb()
OWNER TO postgres;
执行结果,与你初始化的数据有关(事先插入了三行数据)
NOTICE: funca:id,name,state: 10,jack
NOTICE: funca:id,name,state: 10,jack
NOTICE: funca:id,name,state: 10,jack
NOTICE: funcb:id,name,state: 11,jack
NOTICE: funcb:id,name,state: 11,jack
NOTICE: funcb:id,name,state: 11,jack
NOTICE: func:id,name: 11,jack
NOTICE: func:id,name: 11,jack
NOTICE: func:id,name: 11,jack
结论:在read commited 与repeatable read 隔离级别下,均可相互看到未提交的update。异常回滚另验证