在lightdb/pg中，事务是自动提交的，也就是除非明确通过BEGIN;开启事务，否则任何语句都是独立事务。在ltsql客户端，可以通过\echo :AUTOCOMMIT查看自动提交模式，如下：
[zjh@hs-10-20-30-193 ~]$ ltsql -p9999
ltsql (13.3-22.2)
Type "help" for help.
zjh@postgres=# \echo :AUTOCOMMIT
on
zjh@postgres=# \set AUTOCOMMIT off
zjh@postgres=# \echo :AUTOCOMMIT
off
zjh@postgres=#
因为ltsql是客户端工具，不是libpq/ltjdbc实现，所以它不具典型意义。
强制自动提交，无参数控制有个缺点，对于一些oracle/mysql不会提交的场景也会自动提交，而我们认为它不会提交。如explain analyze，如下：
zjh@postgres=*# select count(1) from scott.departments where dept_id<10000;
count
-------
64
(1 row)
zjh@postgres=*# explain analyze update scott.departments set dept_id=dept_id+10000 where dept_id<10000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Update on departments (cost=0.00..54236.67 rows=396542 width=236) (actual time=688.890..688.892 rows=0 loops=1)
-> Seq Scan on departments (cost=0.00..54236.67 rows=396542 width=236) (actual time=0.015..688.719 rows=64 loops=1)
Filter: (dept_id < 10000)
Rows Removed by Filter: 6024736
Planning Time: 0.089 ms
Execution Time: 689.190 ms
(6 rows)
zjh@postgres=*# select count(1) from scott.departments where dept_id<10000;
count
-------
0
(1 row)
explain analyze实际上被提交了，而如果数据被修改后和原来的数据不唯一，就可能无法恢复。这是非常麻烦的。
要正确执行更新，需要先BEGIN，然后rollback，如下：
zjh@postgres=# begin;
BEGIN
zjh@postgres=*# select count(1) from scott.departments where dept_id<20000;
count
-------
64
(1 row)
zjh@postgres=*# explain analyze update scott.departments set dept_id=dept_id+10000 where dept_id<20000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Update on departments (cost=0.00..54236.67 rows=396542 width=236) (actual time=694.907..694.908 rows=0 loops=1)
-> Seq Scan on departments (cost=0.00..54236.67 rows=396542 width=236) (actual time=694.636..694.654 rows=64 loops=1)
Filter: (dept_id < 20000)
Rows Removed by Filter: 6024736
Planning Time: 0.064 ms
Execution Time: 695.004 ms
(6 rows)
zjh@postgres=*# rollback;
ROLLBACK
zjh@postgres=# select count(1) from scott.departments where dept_id<20000;
count
-------
64
(1 row)
因为这很难控制，因此lightdb在内核中基于隐藏字段实现自动更新时间戳，能够记录行最后更新时间，这样能够做到恢复。如下：
zjh@postgres=# create table test_lut(id int) WITH UPDATE CURRENT_TIMESTAMP;
WARNING: LightDB DDL check warn! keyword not allowed with [column name]:id
WARNING: LightDB DDL check warn! no primary key!
WARNING: LightDB DDL check warn! no gmt_create or no gmt_modified!
CREATE TABLE
zjh@postgres=# insert into test_lut values(1);
INSERT 0 1
zjh@postgres=# select * from test_lut ;
id
----
1
(1 row)
zjh@postgres=# \dS+ test_lut
Table "public.test_lut"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+-------------------+---------+--------------+-------------
id | integer | | | | plain | |
ltaut | timestamp without time zone | | | CURRENT_TIMESTAMP | plain | |
Access method: heap
zjh@postgres=# select id,ltaut from test_lut ;
id | ltaut
----+----------------------------
1 | 2022-06-11 20:53:47.295945
(1 row)
zjh@postgres=# update test_lut set id=id+1;
UPDATE 1
zjh@postgres=# select id,ltaut from test_lut ;
id | ltaut
----+----------------------------
2 | 2022-06-11 20:54:33.041471
(1 row)
