PgSQL 事务 并发 锁

什么是ACID

在日常操作中，对于一组相关操作，通常需要其全部成功或全部失败。在关系型数据库中，将这组相关操作称为“事务”。

在一个事务中，多个插入、修改、删除操作要么全部成功，要么全部失败，这称为“原子性”，实际上一个事务还需要有其他3个特性，即“一致性”“隔离性”和“持久性”，英文简称为“ACID”，下面分别说明这4种特性。

原子性（Atomicity）：事务必须以一个整体单元的形式进行工作，对于其数据的修改，要么全都执行，要么全都不执行。如果只执行事务中多个操作的前半部分就出现错误，那么必须回滚所有的操作，让数据在逻辑上回滚到原先的状态。
一致性（Consistency）：在事务完成时，必须使所有的数据都保持在一致状态。
隔离性（Isolation）：事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务是不会查看中间状态的数据的。
持久性（Durability）：事务完成之后，它对于系统的影响是永久性的。即使今后出现致命的系统故障（如机器重启、断电），数据也将一直保持。

数据库的ACID性质让应用开发人员的开发工作得到了最大限度的简化，使开发人员不必考虑过于复杂的并发问题，有利于保证程序在并发状态下的正确性。

DDL事务

在PostgreSQL中，与其他数据库最大的不同是，大多数DDL也是可以包含在一个事务中的，而且也是可以回滚的。该功能非常适合把PostgreSQL作为Sharding的分布式数据系统的底层数据库。比如，在Sharding中，常常需要在多个节点中建相同的表，此时可以考虑把建表语句放在同一个事务中，这样就可以在各个节点上先启动一个事务，然后再执行建表语句，如果某个节点执行失败，也可以回滚前面已执行建表成功的操作，自然就不会出现部分节点建表成功，部分节点建表失败的情况。

事务的使用方法

在psql的默认配置下，自动提交功能“AUTOCOMMIT”是打开的，也就是说，每执行一条SQL语句都会自动提交。可以通过设置psql中的内置变量“AUTOCOMMIT”来关闭自动提交功能：

\set AUTOCOMMIT off

测试一下

insert into testtab01 values(1);
insert into testtab01 values(2);

select * from testtab01;
 id 
----
  1
  2
(2 rows)

rollback;

select * from testtab01;
 id 
----
(0 rows)

还可以通过使用BEGIN语句来启动一个事务，这也相当于关闭了自动提交功能：

begin;

insert into testtab01 values(1);
insert into testtab01 values(2);

select * from testtab01;
 id 
----
  1
  2
(2 rows)

rollback;

select * from testtab01;
 id 
----
(0 rows)

SAVEPOINT

PostgreSQL支持保存点（SAVEPOINT）的功能，在一个大的事务中，可以把操作过程分成几个部分，第一个部分执行成功后可以建一个保存点，若后面的部分执行失败，则回滚到此保存点，而不必回滚整个事务。

先使用“begin”命令启动一个事务：

begin;

然后向第一个表中插入两条记录：

insert into testtab01 values(1);
insert into testtab01 values(2);

再使用“savepoint”命令建一个保存点“my_savepoint01”：

savepoint my_savepoint01;

之后向第二个表中插入数据：

insert into testtab02 values(1);

回滚到上一个保存点

rollback to SAVEPOINT my_savepoint01;

在testtab01中插入的数据并没有被回滚掉：

select * from testtab01;
  id 
----
  1
  2
(2 rows)

最后在提交

commit;

事务隔离级别

数据库的事务隔离级别有以下4种。

READ UNCOMMITTED：读未提交。
READ COMMITTED：读已提交。
REPEATABLE READ：重复读。
SERIALIZABLE：串行化。

对于并发事务，我们不希望发生不一致的情况，这类情况的级别从高到低排序如下。

脏读：一个事务读取了另一个未提交事务写入的数据。这是我们最不希望发生的，因为如果发生了脏读，则在并发控制上，应用程序会变得很复杂。
不可重复读：指一个事务重新读取前面读取过的数据时，发现该数据已经被另一个已提交事务修改了。在大多数情况下，这还是可以接受的，只是在少数情况下会出现问题。
幻读：一个事务开始后，需要根据数据库中现有的数据做一些更新，于是重新执行一个查询，返回一套符合查询条件的行，这时发现这些行因为其他最近提交的事务而发生了改变，此时现有的事务如果再进行下去的话就可能会导致数据在逻辑上的一些错误。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
重复读	不可能	不可能	可能
可串行化	不可能	不可能	不可能
读已提交是PostgreSQL中的默认隔离级别。

SELECT查询（没有FOR UPDATE/SHARE子句）只能看到查询开始之前已提交的数据，而无法看到未提交的数据或在查询执行期间其他事务已提交的数据。不过，SELECT查询看得见其自身所在事务中前面尚未提交的更新结果。请注意，在同一个事务中两个相邻的SELECT命令可能看到不同的快照，因为可能有其他事务会在第一个SELECT查询执行期间被提交。

两阶段提交

在分布式系统中，事务中往往包含了多台数据库上的操作，虽然单台数据库的操作能够保证原子性，但多台数据库之间的原子性就需要通过两阶段提交来实现了，两阶段提交是实现分布式事务的关键。

两阶段提交协议有如下5个步骤。

1）应用程序先调用各台数据库做一些操作，但不提交事务。然后应用程序调用事务协调器（该协调器可能也是由应用自己实现的）中的提交方法。

2）事务协调器将联络事务中涉及的每台数据库，并通知它们准备提交事务，这是第一阶段的开始。PostgreSQL中一般是调用PREPARE TRANSACTION命令。

3）各台数据库接收到PREPARE TRANSACTION命令后，如果要返回成功，则数据库必须将自己置于如下状态：确保后续能在被要求提交事务的时候提交事务，或后续能在被要求回滚事务的时候回滚事务。所以PostgreSQL会将已准备好提交的信息写入持久存储区中。如果数据库无法完成此事务，它会直接返回失败给事务协调器。

4）事务协调器接收所有数据库的响应。

5）在第二阶段，如果任何一个数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令“ROLLBACK PREPARED”给各台数据库。如果所有数据库的响应都是成功的，则向各台数据库发送COMMIT PREPARED命令，通知各台数据库事务成功。

下面用实际应用来演示两阶段提交。

在演示前，需要把参数“max_prepared_transactions”设置成一个大于0的数字，以便使用两阶段提交功能，如果没有设置该参数，会显示如下错误：

ERROR:  prepared transactions are disabled
HINT:  Set max_prepared_transactions to a nonzero value.

注意，设置该参数后需要重启数据库，直接设置此参数是不能成功的：

set max_prepared_transactions = 10;

我们修改postgresql.conf文件中的“max_prepared_transactions”为“10”：

max_prepared_transactions = 10

然后建一张表做测试：

create table testtab01(id int primary key);

启动一个事务，插入一条记录：

postgres=# begin;
BEGIN
postgres=# insert into testtab01 values(1);
INSERT 0 1

再使用PREPARE TRANSACTION命令准备好事务提交（第一阶段）：

postgres=# PREPARE TRANSACTION 'osdba_global_trans_0001';
PREPARE TRANSACTION

上述命令中“osdba_global_trans_0001”是两阶段提交中全局事务的ID，由事务协调器生成（事务协调器也可能是由应用来实现的，事务协调器会持久化该全局事务ID。PostgreSQL数据库一旦成功执行这条命令，也会把此事务持久化。此事务持久化的意思就是，即使数据库重启，此事务也不会回滚或丢失）。

至此，先停止然后再启动数据库：

osdba@osdba-laptop:~$ pg_ctl stop -D $PGDATA
waiting for server to shut down.... done
server stopped
osdba@osdba-laptop:~$ pg_ctl start -D $PGDATA
server starting

重启数据库后，可以使用COMMIT PREPARED命令真正提交该事务提交（第二阶段）：

osdba@osdba-laptop:~/pgdata$ psql postgres
psql (9.2.4)
Type "help" for help.
postgres=# COMMIT PREPARED  'osdba_global_trans_0001';
COMMIT PREPARED

之后就可以查看之前插入的数据了：

postgres=# select * from testtab01;
 id 
----
  1
(1 row)

从上面的示例中可以看出，一旦成功执行PREPARE TRANSACTION命令，事务就会被持久化，即使数据库重启，仍然可以提交该事务，事务中的操作不会丢失。

锁机制

PostgreSQL数据库中有两类锁：表级锁和行级锁。当要查询、插入、更新、删除表中的数据时，首先会获得表上的锁，然后再获得行上的锁。

表级锁

ACCESS SHARE LOCK：用于数据查询(SELECT)，与ACCESS EXCLUSIVE锁冲突。
ROW SHARE LOCK：用于SELECT FOR UPDATE或SELECT FOR SHARE，与EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。
ROW EXCLUSIVE LOCK：用于数据的更新、插入和删除操作，与SHARE、SHARE ROW EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。
SHARE UPDATE EXCLUSIVE LOCK：用于VACUUM和某些ALTER TABLE操作，防止并发的schema改变和VACUUM命令。
SHARE LOCK：用于创建索引（CREATE INDEX），防止并发的数据变更。
SHARE ROW EXCLUSIVE LOCK：用于某些不会自排他的操作，比如创建触发器。
EXCLUSIVE LOCK：用于防止并发的数据变更和读取操作，只允许并发的ACCESS SHARE锁。
ACCESS EXCLUSIVE LOCK：用于TRUNCATE、DROP TABLE等DDL操作，与其他所有锁冲突。

行级锁

FOR UPDATE：对整行进行更新，包括删除行，阻止其他事务对行的读取和更新。
FOR NO KEY UPDATE：对除主(唯一)键外的字段更新，对行加锁，但允许其他事务在不锁定键值的情况下进行更新。
FOR SHARE：读该行，不允许对行进行更新，阻止其他事务对行的更新。
FOR KEY SHARE：读该行的键值，但允许对除键外的其他字段更新，主要用于外键检查。

@TODO