挖掘PostgreSQL事务的“中间态”----更加严谨的数据一致性？

1.问题

今天在上班途中，中心的妹纸突然找我，非常温柔的找我帮忙看个数据库的报错。当然以我的性格，妹子找我的事情对我来说优先级肯定是最高的，所以立马放下手中的“小事”，转身向妹子走去。具体是一个什么样的问题呢？

可以看到，这是一个postgreSQL的问题，妹子通过python的pscopg2包，通过executemany()的方法，对PostgreSQL数据库进行多条数据的写入操作，但是报了以下错误

psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block 
1

2.问题分析

对这个问题乍看一眼，其实就是当前的事务被中断了，但是具体什么原因，这里并没有告诉我们，所以第一想法，这会不会是触发了什么bug，导致事务突发中断。但是为了解决妹子多条数据插入这个事情，我们还是得确定它问题到底是不是bug。于是，我们还是得先到数据库上看看这个事务的相关日志。

通过对日志的检查，我们看到了以下操作过程：

2023-11-15 15:29:53.682 CST [52096] STATEMENT:  data = [
            (202311,cpu,18%,,),
            (202311,men,50%,test,),
            (202311,storage,3%,,)
        ]
        sql = "insert into aa (a,b,c,d) value (%s,%s,%s,%s)"
        select * from fk ;
2023-11-15 15:33:52.180 CST [54538] ERROR:  syntax error at or near "value" at character 40
2023-11-15 15:33:52.180 CST [54538] STATEMENT:  insert into aa (a,b,c,d) value ('202311','cpu','18%','')
2023-11-15 15:34:06.611 CST [54538] ERROR:  current transaction is aborted, commands ignored until end of transaction block
1
2
3
4
5
6
7
8
9
10

从数据库层查看，可以看出，程序通过data定义一个列表，列表中包含了三组数据，并通过insert的sql插入数据库，但是当我们执行到第一个sql时，由于语法错误(insert into … values …的values写成了value)，导致插入语句失败，然后报出了我们看到的事务中断的错误。
嗨，看到这里，松了口气，其实就是语法写错导致事务中断而已，不是什么bug嘛，既然事务失败了，那么我们重新发起操作即可。可是妹子又跟我说，后续她改正了错误，然后执行，可还是会报这个错误。

这时，我突然想起在很久之前做过的一些关于pg事务的测试，当时突然测试出一个奇怪的现象，就是当我手动启用一个事务的时候，如果这个事务中的sql执行出错，那么我接着改正sql，继续执行还是会报错，只有当我做了rollback或者commit之后，才能够在这个会话中继续执行sql。

而妹子遇见的这个现象好像和这个确实很像，为了进一步验证想法，我做了之前的实验，过程很简单：

begin;
BEGIN
insert into fk value(now(),'1','2','3');
ERROR:  syntax error at or near "value"
LINE 1: insert into fk value(now(),'1','2','3');
                       ^
insert into fk values(now(),'1','2','3');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
insert into fk values(now(),'3','2','3');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
insert into fk values(now(),'3','2','');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
select * from fk;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

很容易的，我们模拟出了这个问题，这个时候我们去看当前的活动会话：

postgres=# select pid,usename,application_name,wait_event_type,wait_event,state,query from pg_stat_activity where wait_event_type='Client';

  pid  | usename  | application_name | wait_event_type | wait_event |             state             |       query       
-------+----------+------------------+-----------------+------------+-------------------------------+-------------------
 19137 | postgres | psql             | Client          | ClientRead | idle in transaction (aborted) | select * from aa;
(1 row)

此时，我们通过rollback，结束这个事务：

rollback;
ROLLBACK

再次查看当前活动会话：

postgres=# select pid,usename,application_name,wait_event_type,wait_event,state,query from pg_stat_activity where wait_event_type='Client';
  pid  | usename  | application_name | wait_event_type | wait_event | state |   query   
-------+----------+------------------+-----------------+------------+-------+-----------
 19137 | postgres | psql             | Client          | ClientRead | idle  | rollback;

--插入数据测试：
insert into aa values(now(),'1','2','3');
INSERT 0 1

--查询表测试：
select * from aa;
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

1

注：我们知道，在postgresql中，通过psql登陆，事务默认是自动提交的，因此我们不需要在dml操作后，执行commit或者rollback操作，想要关闭事务的自动提交，则有两个方法：

1.通过begin指定开始一个事务块；
2.通过设置AUTOCOMMIT的方式关闭自动提交

postgres=# \set AUTOCOMMIT off
postgres=# \echo :AUTOCOMMIT
off
1
2
3
4
5
6

那么上面的现象就很好理解了，我通过begin的方式，开启了一个事务块，在这个事务块中，我执行了一个错误的sql，导致这个事务出现问题中断，而这个事务则进入了一个“中间态”，而会话则是一个idle in transaction (aborted)的状态，此时，只要在这个会话中不论我们执行什么sql，都会报错。而当我做了rollback后，则结束了这个事务，同时会话也成为了idle状态。而我们再次做其他操作时，则是进入了新的事务，并自动提交。

那么，妹子的事情也能解释的通了，通过python等客户端对数据库进行连接操作，默认是开启了一个事务块。妹子在写测试sql的时候，发现了错误并及时修正，但是并未做回滚或提交，结束事务。此时，事务处于“中间态”，会话处于abort。

3.源码证实

虽然妹子的问题解决了，但是实际是不是这样呢？这只能在源码中看看了。在源码中，我找到这么一段代码及描述：

大概意思就是说，当我们在一个事务块中，由于语句造成的中断，虽然我们什么都没做成，但是它还会保持中断状态，直到我们收到一个rollback命令；而当我们从用户客户端收到了rollback命令，那么我们就会讲已经abort的回话清理并恢复到空闲状态。

4.总结

从这个问题中，我们可以看到，PostgreSQL在进行事务操作时候，为了保证事务的一致性，会有一个事务的“中间态”，这里我打个比方，即这个“中间态”保证了PostgreSQL同一个事务中的操作“一荣俱荣，一损俱损”。即在一个事务中，我们可能会存在多个dml操作，而当其中的一个操作失败后，整个事务中的操作都会失败，并且需要手动回滚，而不会存在部分成功部分失败。

对比oracle/mysql，当我们在一个事务中，执行多个dml操作时，正确的操作则会成功(这里会写入到buffer中，旧数据则会在undo中)，错误的操作则会失败，从而形成了部分成功的情况。

这样看来，好像PostgreSQL的事务对于整个业务的一致性要更加严谨。比如A给B转钱，那么A的账户要减掉10000，B的账户要增加10000。我们可以将这个过程分为两个update操作，如果按照Oracle/Mysql数据库层面的处理逻辑，但当某些原因，B账户增加10000的update执行失败，此时如果我们做了commit操作，那么则只有A的账户被被更新，B的账户则不变，此时业务则出现了错误。但是如果以PostgreSQL的“中间态”理解，此时A、B的账户则都会回退，看起来更加合理。

当然，这只是我的一些理解，也欢迎各位大佬可以一起交流，指正错误。