TDSQL PostgreSQL版 事务控制

一、事务隔离级别

TDSQL PG 支持四种事务隔离级别，这些级别定义了一个事务可能会看到其他并发事务所做更改的程度。这些级别从低到高分别是：

·Read Uncommitted（读未提交）：这是最低的隔离级别，一个事务可能会看到其他事务未提交的更改。

·Read Committed（读已提交）：这是 TDSQL PG 默认隔离级别。在这个级别，一个事务能看到在该事务开始之前已经提交事务所做的更改，后续的查询也会看到其他事务所提交新的更改。

·Repeatable Read（可重复读）：在这个级别，一个事务在其整个过程中看到的是一个一致的快照。也就是说，它只能看到在该事务开始时已经提交的事务所做的更改，而不能看到在该事务进行期间其他事务所提交的更改。

·Serializable（可串行化）：这是最高的隔离级别。它提供了最严格的隔离级别，确保事务序列化执行，即使在并发执行时也能得到相同的结果。这个级别可以防止所有类型的并发问题。

二、锁

TDSQL PG 还提供了表级和行级锁定，为应用提供显式管理互斥场景的能力；TDSQL PG 支持咨询锁，提供了跨事务的锁机制；整体上为应用提供了更高的灵活度。

三、表级锁

TDSQL PG 提供了多种锁模式来控制对表中数据的并发访问。在多版本并发控制（MVCC）无法提供所需行为的情况下，这些模式可以用于应用程序显式执行锁定动作。注意，TDSQL PG 支持的命令会自动获取合适的锁，确保在命令执行期间操作的表不会被删除等情况发生（例如 TRUNCATE 不能与其他命令同时操作一张表，所以 TRUNCATE 需要拿到 ACCESS EXCLUSIVE 模式锁）。

Conflicting Lock Modes

四、行级锁

TDSQL PG 的行级锁主要用于控制对单数据行的并发访问。行级锁通常在事务中使用，以确保在事务执行期间，其他事务不能修改被锁定的行。

TDSQL PG 提供了以下几种行级锁模式：

1. FOR UPDATE：这种锁定模式在读取数据行时获取一个排他锁。一旦一个事务获取了这种锁，其他事务就不能获取同一行的 FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE 或 FOR KEY SHARE 锁，直到第一个事务完成。

2. FOR NO KEY UPDATE：这种锁定模式类似于 FOR UPDATE，但它允许其他事务获取 FOR KEY SHARE 锁。这对于需要防止其他事务修改数据行，但允许它们并发读取数据行的情况很有用。

3. FOR SHARE：这种锁定模式在读取数据行时获取一个共享锁。这意味着其他事务可以获取同一行的 FOR SHARE 或 FOR KEY SHARE 锁，但不能获取 FOR UPDATE 或 FOR NO KEY UPDATE 锁。

4. FOR KEY SHARE：这是最轻量级的行级锁定模式。它允许其他事务获取 FOR NO KEY UPDATE、FOR SHARE 或 FOR KEY SHARE 锁。

五、咨询锁

咨询锁（Advisory Locks）是 TDSQL PG 提供的一种特殊类型的锁，它们不由系统自动管理，完全提供给应用程序使用。这种锁的主要用途是在应用程序级别实现复杂的业务规则或协调多个事务的行为。

Advisory Lock 有两种级别：会话级别和事务级别。

1. 会话级别的 Advisory Lock：这种锁在当前会话结束时自动释放，或者可以通过调用 unlock 函数手动释放。如果在一个会话中获取了一个 Advisory Lock，那么只有这个会话可以看到这个锁，其他会话无法看到。

2. 事务级别的 Advisory Lock：这种锁在当前事务结束时自动释放，无论事务是提交还是回滚。这种锁只在当前事务中可见。

六、开始一个事务

postgres=# begin;BEGIN

或者

postgres=# begin TRANSACTION ; BEGIN

也可以定义事务的级别。

postgres=# begin transaction isolation level read committed ;BEGIN

七、提交事务

进程#1访问。

postgres=# begin;BEGINpostgres=# delete from tdsql_pg where id=5;DELETE 1postgres=#postgres=# select * from tdsql_pg order by id; id | nickname —-+————— 1 | hello tdsql_pg 2 | tdsql_pg好 3 | tdsql_pg好 4 | tdsql_pg default

TDSQL PostgreSQL 版完全支持 ACID 特性，没提交前开启另一个连接查询，会看到是5条记录，这是 TDSQL PostgreSQL 版隔离性和多版本视图的实现，如下所示。
进程#2访问。

postgres=# select * from tdsql_pg order by id; id | nickname —-+————— 1 | hello tdsql_pg 2 | tdsql_pg好 3 | tdsql_pg好 4 | tdsql_pg default 5 | tdsql_pg swap(5 rows)

进程#1提交数据。

postgres=# commit;COMMITpostgres=#

进程#2再查询数据，这时已经能看到提交的数据，这个级别叫“读已提交”。

postgres=# select * from tdsql_pg order by id; id | nickname —-+————— 1 | hello tdsql_pg 2 | tdsql_pg好 3 | tdsql_pg好 4 | tdsql_pg default(4 rows)

八、回滚事务

postgres=# begin;BEGINpostgres=# delete from tdsql_pg where id in (3,4);DELETE 2postgres=# select * from tdsql_pg; id | nickname —-+————- 1 | hello tdsql_pg 2 | tdsql_pg好(2 rows)postgres=# rollback;ROLLBACK

Rollback 后数据又回来了。

postgres=# select * from tdsql_pg; id | nickname —-+————— 1 | hello tdsql_pg 2 | tdsql_pg好 3 | tdsql_pg好 4 | tdsql_pg default(4 rows)

九、事务读一致性 REPEATABLE READ

这种事务级别表示事务自始至终读取的数据都是一致的，如下所示。
#session1

postgres=# create table t_repeatable_read (id int,mc text); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE postgres=# insert into t_repeatable_read values(1,'tdsql_pg'); INSERT 0 1 postgres=# begin isolation level repeatable read ; BEGIN postgres=# select * from t_repeatable_read ; id | mc —-+——- 1 | tdsql_pg (1 row)

#session2

postgres=# insert into t_repeatable_read values(1,'pgxz'); INSERT 0 1postgres=# select * from t_repeatable_read; id | mc —-+——- 1 | tdsql_pg 1 | pgxz(2 rows)

#session1

postgres=# select * from t_repeatable_read ; id | mc —-+——- 1 | tdsql_pg(1 row)postgres=#

十、环境准备

postgres=# create table t_row_lock(id int,mc text,primary key (id));NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# postgres=# insert into t_row_lock values(1,'tdsql_pg'),(2,'pgxz'); INSERT 0 2postgres=# select * from t_row_lock; id | mc —-+——- 1 | tdsql_pg 2 | pgxz(2 rows)

十一、直接 update 获取

#session1

postgres=# begin;BEGINpostgres=# set lock_timeout to 1;SETpostgres=# update t_row_lock set mc='postgres' where mc='pgxz';UPDATE 1postgres=#

#session2

postgres=# begin; BEGIN postgres=# set lock_timeout to 1; SET postgres=# update t_row_lock set mc='postgresql' where mc='tdsql_pg'; UPDATE 1 postgres=#

上面 session1 与 session2 分别持有 mc=pgxz 行和 mc=tdsql_pg 的行锁。

十二、select…for update 获取

#session1

postgres=#BEGINpostgres=# set lock_timeout to 1; SETpostgres=# select * from t_row_lock where mc='pgxz' for update; id | mc —-+—— 2 | pgxz(1 row)

#session2

postgres=# begin;BEGINpostgres=# set lock_timeout to 1; SETpostgres=# select * from t_row_lock where mc='pgxz' for update; id | mc —-+—— 2 | pgxz(1 row)

上面 session1 与 session2 分别持有 mc=pgxz 行和 mc=tdsql_pg 的行锁。

十三、与 MySQL 获取行级锁的区别

mysql> select version();+———–+| version() |+———–+| 5.6.36 |+———–+1 row in set (0.00 sec)

#session1

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_row_lock where mc='pgxz' for update;+—-+——+| id | mc |+—-+——+| 2 | pgxz |+—-+——+1 row in set (0.00 sec)

#session2

mysql> select * from t_row_lock where mc='tdsql_pg' for update;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>

这是因为 MySQL 要使用行级锁需要有索引来配合使用，如下所示，使用 ID 主键来获取行锁。
#session1

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_row_lock where id=1 for update; +—-+——-+| id | mc |+—-+——-+| 1 | tdsql_pg |+—-+——-+1 row in set (0.00 sec)

#session2

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_row_lock where id=2 for update;+—-+——+| id | mc |+—-+——+| 2 | pgxz |+—-+——+1 row in set (0.00 sec)

十四、自治事务

自治事务是由另一个事务（主事务）启动的独立事务。自治事务执行 SQL 操作并提交或回滚，而无需提交或回滚主事务。支持匿名块或者存储过程中使用子事务。

示例：

create table t3(f1 int); do $$ begin insert into t3 values(1); insert into t3 values(2); commit; insert into t3 values(3); rollback;end; $$ ;create or replace procedure demo11(a_varchar in varchar) as$$ begin insert into t3 values(1); insert into t3 values(2); commit; insert into t3 values(3); rollback;end;$$LANGUAGE plpgsql ; call demo11(null); select * from t3;