PostgreSQL事务重启 [英] PostgreSQL transaction restart

问题描述

我开始使用PostgreSQL，并注意到sequence从不回滚，即使在失败的INSERT上也是如此.
我已经读到它可以防止在并发事务上出现重复序列，并且发现我的数据库经验很奇怪，只有

I'm starting to play with PostgreSQL and noticed that sequences never rollback, even on failed INSERT.
I've read that it is as expected to prevent duplicated sequences on concurrent transactions and I found that weird as my database experience is only with GTM where transaction restarts are common and used precisely for this.

所以我想测试PGSQL中的重启并将其加载到数据库中

So I wanted to test restarts in PGSQL and loaded this in a database:

CREATE SEQUENCE account_id_seq;

CREATE TABLE account
(
  id integer NOT NULL DEFAULT nextval('account_id_seq'),
  title character varying(40) NOT NULL,
  balance integer NOT NULL DEFAULT 0,
  CONSTRAINT account_pkey PRIMARY KEY (id)
);

INSERT INTO account (title) VALUES ('Test Account');

CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$
DECLARE
    cc integer;
BEGIN
    cc := balance from account where id=1;

    RAISE NOTICE 'Balance: %', cc;
    perform pg_sleep(3);

    update account set balance = cc+10 where id=1 RETURNING balance INTO cc;

    return cc;
END
$$
LANGUAGE plpgsql;

因此，函数mytest()将检索余额，等待3秒钟(让我启动其他过程)，然后根据已保存的变量更新余额.

So, function mytest() will retrieve balance, wait 3 seconds (to let me launch the other process) and then update balance based on the saved variable.

我现在直接从shell启动对该函数的2个调用:

I now launch 2 calls to this function directly from shell:

void$ psql -c "select * from account where id=1"
 id |    title     | balance 
----+--------------+---------
  1 | Test Account |       0
(1 row)

void$ psql -c "select mytest()" & PIDA=$! && psql -c "select mytest()" && wait $PIDA
[1] 3312
NOTICE:  Balance: 0
NOTICE:  Balance: 0
 mytest 
--------
     10
(1 row)

 mytest 
--------
     10
(1 row)

[1]+  Done                    psql -c "select mytest()"
void$ psql -c "select * from account where id=1"
 id |    title     | balance 
----+--------------+---------
  1 | Test Account |      10
(1 row)

我希望余额为20，而不是10，因为随着处理过程中balance from account where id=1的视图"发生更改，上一次要提交的事务应重新启动...

I would expect balance to be 20, not 10, as the last transaction to be committed should restart as the "view" of balance from account where id=1 changed during processing...

我已经阅读了官方文档中的交易隔离在我看来，默认的read committed应该精确地执行此行为..
我还测试了将隔离级别更改为serializable，然后提交的最后一个事务确实引发了异常，但是我想知道是否没有任何事务重启"功能(如我所述)或是否缺少一些东西...

I've read about transaction isolation in official documentation and it sounds to me that the default read committed should enforce this behavior precisely..
I've also tested changing the isolation level to serializable and then the last transaction committed does throw an exception but I would like to know if there isn't any "transaction restart" functionality (as I described) or if I'm missing something...

推荐答案

如果对

You get a "restart" automatically if you use proper queries with row level locks. To be precise, the transaction is not restarted as a whole, it just waits its turn when trying to lock a row in default transaction isolation READ COMMITTED:

CREATE OR REPLACE FUNCTION mytest()
   RETURNS integer AS
$func$
DECLARE
   cc integer;
BEGIN
   SELECT INTO cc balance FROM account WHERE id = 1 FOR UPDATE;

   RAISE NOTICE 'Balance: %', cc;
   PERFORM pg_sleep(3);

   UPDATE account SET balance = cc+10
   WHERE id = 1
   RETURNING balance
   INTO cc;

   RETURN cc;
END
$func$  LANGUAGE plpgsql;

SELECT ... FOR UPDATE进行行级锁定以声明该行将要更新的声明.在另一个事务中尝试相同操作的同一个函数将被阻止，并等到第一个提交或回滚时才进行操作-然后自己获取锁并在更新的行上构建，这样您的实验结果将为20，而不是10.

SELECT ... FOR UPDATE takes a row level lock to announce the claim that this row is going to be updated. The same function trying the same in another transaction will be blocked and wait until the first commits or rolls back - then take the lock itself and build on the updated row so that the result of your experiment will be 20, not 10.

使用简单而又简单的UPDATE查询(使用适当的

You can have the same much more efficiently with a plain and simple UPDATE query that takes the appropriate FOR UPDATE locks automatically:

UPDATE account
SET    balance = balance + 10
WHERE  id = 1
RETURNING  balance;

这些最近的问题似乎也遇到了类似的问题.详细说明和链接:

These recent questions seem to have run into similar problems. Detailed explanation and links:

• 函数永久运行大量记录
• 为SELECT锁定，因此进程不会获取旧数据

• Function taking forever to run for large number of records
• Lock for SELECT so another process doesn't get old data

这篇关于PostgreSQL事务重启的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！