事物默认自动提交AUTOCOMMIT

最新推荐文章于 2024-12-03 23:32:50 发布

lk_db

最新推荐文章于 2024-12-03 23:32:50 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： Highgo DB PostgreSQL

本文链接：https://blog.youkuaiyun.com/lk_db/article/details/77938917

Highgo DB 同时被 2 个专栏收录

171 篇文章

订阅专栏

PostgreSQL

162 篇文章

订阅专栏

本文介绍了两种在数据库中禁用自动提交的方法：一是通过设置AUTOCOMMIT为off，需要注意大小写；二是通过显式使用BEGIN来开启新的事务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

修改为非自动提交的方式有两种：
1、# \set AUTOCOMMIT off --注意此处的AUTOCOMMIT要大写，小写无效
highgo=# \echo :autocommit
:autocommit
highgo=# \echo :AUTOCOMMIT
on
highgo=# \set autocommit off
highgo=# \echo :AUTOCOMMIT
on
highgo=# \set AUTOCOMMIT off
highgo=# \echo :AUTOCOMMIT
off
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=# select * from test;
id | name
----+------
1 |
2 |
(2 rows)

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

2、显示使用BEGIN
highgo=# \echo :AUTOCOMMIT
on
highgo=# begin; ---此处begin大小写都生效
BEGIN
highgo=#
highgo=# select * from test;
id | name
----+------
(0 rows)

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

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \set AUTOCOMMIT off
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \echo :AUTOCOMMIT
off
highgo=# \echo :autocommit
:autocommit

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
show autocommit;和\echo :autocommit的区别
highgo=# \set autocommit on
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \set AUTOCOMMIT OFF
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \SET AUTOCOMMIT off
Invalid command \SET. Try \? for help.
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \SET autocommit off
Invalid command \SET. Try \? for help.
highgo=# \set AUTOCOMMIT off
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \set autocommit off
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \set autocommit off
highgo=# show autocommit;
autocommit
------------
on
(1 row)

highgo=# \echo :AUTOCOMMIT
off
highgo=# show autocommit;
autocommit
------------
on
(1 row)
highgo=# \set autocommit on
highgo=# \echo :autocommit
on
highgo=# show autocommit;
autocommit
------------
on
(1 row)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++