PostgreSQL BEGIN attention. serializable case
背景
PostgreSQL BEGIN事务的时候，干了些啥？
来看看下面的例子可能会有启发：
场景，实现ORACLE的Serializable隔离级别。
描述 :
ORACLE :
The SERIALIZABLE setting specifies serializable transaction isolation mode as defined in the SQL92 standard.
If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated
in a transaction uncommitted at the start of the serializable transaction, then the DML statement fails.
我们用PostgreSQL 9.0 来测试，实际上9.0和9.1测试的结果是一样的。其他的版本我没测试，大家可以看看HISTORY，什么时候实现的repeatable read应该测试结果都一样(想一想PG的MVCC吧)。
测试一、
Session A :
digoal=> create table isolate_test(id int,info text);
CREATE TABLE
digoal=> insert into isolate_test values (1,'digoal');
INSERT 0 1
digoal=> begin;
BEGIN
digoal=> update isolate_test set id=2 where id=1;
UPDATE 1
Session B :
digoal=> begin isolation level serializable; （替换成REPEATABLE READ，结果一样）
BEGIN
digoal=> select 1;
?column?
----------
1
(1 row)
Session A :
digoal=> commit;
COMMIT
Session B :
digoal=> update isolate_test set id=3 where id=1;
ERROR: could not serialize access due to concurrent update
digoal=> end;
ROLLBACK
显然，实现了ORACLE描述的serializable隔离级别。
测试二、
Session A :
digoal=> create table isolate_test(id int,info text);
CREATE TABLE
digoal=> insert into isolate_test values (1,'digoal');
INSERT 0 1
digoal=> begin;
BEGIN
digoal=> update isolate_test set id=2 where id=1;
UPDATE 1
Session B :
digoal=> begin isolation level serializable; （替换成REPEATABLE READ，结果一样）
BEGIN
Session A :
digoal=> commit;
COMMIT
Session B :
digoal=> update isolate_test set id=3 where id=1;
UPDATE 0
digoal=> select * from isolate_test ;
id | info
----+--------
2 | digoal
(1 row)
digoal=> update isolate_test set id=3 where id=2;
UPDATE 1
digoal=> end;
COMMIT
结果和测试一截然不同，原因只是SESSION B在BEGIN后没有执行任何SQL，SESSION A提交了。
从PG的解释中可以看出点啥呢? the first query or data-modification statement was executed in this transaction . 显然，例二没有按照PG说的来，当然是和前面的结果截然不同的。
来看看PostgreSQL对BEGIN的解释吧:
BEGIN -- start a transaction block
再看看ISOLATION LEVEL的解释 :
REPEATABLE READ
All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction.
SERIALIZABLE
All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction.
If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial
(one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure SQLSTATE.