SAVEPOINT保存点

SAVEPOINT

保存点仅在当前事物中起作用。
A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.


PG支持保存点,在一个大事物中,可以把操作过程分成几部分,第一个部分成功后,可以建一个保存点,若后面的执行失败,则回滚到这个保存点,而不必把整个事物都回滚掉。

highgo=# begin;
BEGIN
highgo=# select * from test;
 id | name 
----+------
  1 | 
(1 row)


highgo=# insert into test values (1);
INSERT 0 1
highgo=# insert into test values (2);
INSERT 0 1
highgo=# savepoint test_savepoint_01;
SAVEPOINT
highgo=# 
highgo=# insert into test values (3);
INSERT 0 1
highgo=# select * from test;
 id | name 
----+------
  1 | 
  1 | 
  2 | 
  3 | 
(4 rows)


highgo=# rollback to savepoint test_savepoint_01;
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
  1 | 
  1 | 
  2 | 
(3 rows)


highgo=# 


highgo=# insert into test values (3);
INSERT 0 1
highgo=# select * from test;
 id | name 
----+------
  1 | 
  1 | 
  2 | 
  3 | 
(4 rows)




To establish and later destroy a savepoint:


highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# BEGIN;
BEGIN
highgo=#     INSERT INTO test VALUES (3);
INSERT 0 1
highgo=#     SAVEPOINT my_savepoint;
SAVEPOINT
highgo=#     INSERT INTO test VALUES (4);
INSERT 0 1
highgo=#     RELEASE SAVEPOINT my_savepoint;    --销毁savepoint
RELEASE
highgo=# COMMIT;
COMMIT
highgo=# select * from test;
 id | name 
----+------
  3 | 
  4 | 
(2 rows)


****************************************************************************************
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.


highgo=# begin;
BEGIN
highgo=# insert into test values (1);
INSERT 0 1
highgo=# savepoint my_savepoint;
SAVEPOINT
highgo=# insert into test values (2);
INSERT 0 1
highgo=# rollback to savepoint my_savepoint;
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
  1 | 
(1 row)


highgo=# insert into test values (2);
INSERT 0 1
highgo=# insert into test values ('2');
INSERT 0 1
highgo=# insert into test values ('aaa');
错误:  无效的整数类型输入语法: "aaa"
LINE 1: insert into test values ('aaa');
                                 ^
highgo=# select * from test;
错误:  当前事务被终止, 事务块结束之前的查询被忽略
highgo=# rollback to savepoint my_savepoint;                 --在一个事物中,一条语句执行失败,有savepoint的情况下可以rollback到sacepoint;没有saveepoint则回退所有事物
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
  1 | 
(1 row)
************************************************************************************
highgo=# begin;
BEGIN
highgo=# 
highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# insert into test values (1);
INSERT 0 1
highgo=# insert into test values (2);
INSERT 0 1
highgo=# insert into test values ('aaaa');
错误:  无效的整数类型输入语法: "aaaa"
LINE 1: insert into test values ('aaaa');
                                 ^
highgo=# select * from test;
错误:  当前事务被终止, 事务块结束之前的查询被忽略   
highgo=# rollback;
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# 
----------------------------------------------
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.


highgo=# begin;
BEGIN
highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# insert into test values (1);
INSERT 0 1
highgo=# insert into test values (2);
INSERT 0 1
highgo=# insert into test values ('aaaa');
错误:  无效的整数类型输入语法: "aaaa"
LINE 1: insert into test values ('aaaa');
                                 ^
highgo=# select * from test;
错误:  当前事务被终止, 事务块结束之前的查询被忽略
highgo=# 
highgo=# \q
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.


highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值