TDSQL PostgreSQL版事务控制-开发指南-文档中心-腾讯云

事务概述
事务是一组数据库操作的集合，这些操作作为一个整体被执行，要么全部成功，要么全部失败，数据库会从一个一致性状态转换到下一个一致性状态。即使在系统故障的情况下，也能保证数据的一致性。
如果事务中的某个操作失败，那么整个事务将被回滚，数据库状态将恢复到事务开始之前的状态。
TDSQL PG 事务系统通过使用多版本并发控制（MVCC）和可串行化快照隔离（SSI）等技术，提供了高效的并发控制和严格的事务隔离，确保并发执行的事务不会互相干扰。在 MVCC 中查询数据获取的锁不会与写入数据获取的锁发生冲突，因此读取不会阻塞写入，写入也不会阻塞读取，可以为用户提供高性能并发读写能力。
事务隔离级别
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
Requested Lock/Existing Lock
ACCESS SHARE
ROW SHARE
ROW EXCL.
SHARE UPDATE EXCL.
SHARE
SHARE ROW EXCL.
EXCL.
ACCESS EXCL.
ACCESS SHARE
﻿
﻿
﻿
﻿
﻿
﻿
﻿
X
ROW SHARE
﻿
﻿
﻿
﻿
﻿
﻿
X
X
ROW EXCL.
﻿
﻿
﻿
﻿
X
X
X
X
SHARE UPDATE EXCL.
﻿
﻿
﻿
X
X
X
X
X
SHARE
﻿
﻿
X
X
﻿
X
X
X
SHARE ROW EXCL.
﻿
﻿
X
X
X
X
X
X
EXCL.
﻿
X
X
X
X
X
X
X
ACCESS EXCL.
X
X
X
X
X
X
X
X
行级锁
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;
BEGIN
postgres=# delete from tdsql_pg where id=5;
DELETE 1
postgres=#
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;
COMMIT
postgres=# 
进程#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;
BEGIN
postgres=# delete from tdsql_pg where id in (3,4);
DELETE 2
postgres=# 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 1
postgres=# 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 TABLE
postgres=# 
﻿
postgres=# insert into t_row_lock values(1,'tdsql_pg'),(2,'pgxz');    
INSERT 0 2
postgres=# select * from t_row_lock;
 id |  mc  
----+-------
 1 | tdsql_pg
 2 | pgxz
(2 rows)
直接 update 获取
#session1
postgres=# begin;
BEGIN
postgres=# set lock_timeout to 1;
SET
postgres=# update t_row_lock set mc='postgres' where mc='pgxz';
UPDATE 1
postgres=# 
#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=#
BEGIN
postgres=# set lock_timeout to 1; 
SET
postgres=# select * from t_row_lock where mc='pgxz' for update;
 id |  mc  
----+------
 2 | pgxz
(1 row)
#session2 
postgres=# begin;
BEGIN
postgres=# set lock_timeout to 1; 
SET
postgres=# 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 transaction
mysql> 
这是因为 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;
﻿
Requested Lock/Existing Lock	ACCESS SHARE	ROW SHARE	ROW EXCL.	SHARE UPDATE EXCL.	SHARE	SHARE ROW EXCL.	EXCL.	ACCESS EXCL.
`ACCESS SHARE`								X
`ROW SHARE`							X	X
`ROW EXCL.`					X	X	X	X
`SHARE UPDATE EXCL.`				X	X	X	X	X
`SHARE`			X	X		X	X	X
`SHARE ROW EXCL.`			X	X	X	X	X	X
`EXCL.`		X	X	X	X	X	X	X
`ACCESS EXCL.`	X	X	X	X	X	X	X	X
事务控制

本页目录：

事务概述

事务隔离级别

锁

表级锁

行级锁

咨询锁

开始一个事务

提交事务

回滚事务

事务读一致性 REPEATABLE READ

行锁在事务中的运用

环境准备

直接 update 获取

select...for update 获取

与 MySQL 获取行级锁的区别

自治事务
