Postgrseql - transaction - 事务操作及隔离级别

事务的操作有 begin(start transaction), savepoint, rollback, commit, release

 

事务开始：

begin/start transaction [ transaction_mode [, ...] ] 设置事务隔离级别，读写/只读， ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY [ NOT ] DEFERRABLE

 

事务保存点，用于事务回滚。

savepoint

 

事务回滚：

rollback;

 

事务提交：

commit

 

在PostgreSQL中，可以请求四个标准事务隔离级别中的任何一个。但在内部，只有三个不同的隔离级别，对应于读提交、可重复读取和可序列化的级别。

当选择未读取的级别时，就真正获得了读提交，并且在可重复读取的PostgreSQL实现中不可能有幻像读取，因此实际隔离级别可能比选择的更严格。

默认为读提交（read commited）.所以更改事务隔离级别只可更改到更严格的情况。

 

通过三个实验来理解可重复读和序列化的级别。

==============================================

可重复读示例

transaction A:

begin transaction isolation level repeatable read;

mytest=# select * from test_1202 ;

id | col1 | c_time

----+------+--------

(0 rows)

 

transaction B:

mytest=# insert into test_1202 values (1, 'a', now());

INSERT 0 1

mytest=# insert into test_1202 values (1, 'a', now());

INSERT 0 1

mytest=# insert into test_1202 values (1, 'a', now());

INSERT 0 1

mytest=# select * from test_1202 ;

id | col1 | c_time

----+------+----------------------------

1 | a | 2018-12-03 04:33:13.483814

1 | a | 2018-12-03 04:33:19.864314

1 | a | 2018-12-03 04:33:46.233437

(3 rows)

 

transaction C:

mytest=# select * from test_1202;

id | col1 | c_time

----+------+----------------------------

1 | a | 2018-12-03 04:33:13.483814

1 | a | 2018-12-03 04:33:19.864314

1 | a | 2018-12-03 04:33:46.233437

(3 rows)

 

transaction A:

mytest=# select * from test_1202 ;

id | col1 | c_time

----+------+--------

(0 rows)

 

mytest=# insert into test_1202 values (2, 'b', now());

INSERT 0 1

mytest=# select * from test_1202 ;

id | col1 | c_time

----+------+----------------------------

2 | b | 2018-12-03 04:29:02.134891

(1 row)

 

 

==============================================

隔离级别为 serializable

transaction A:

begin work isolation level serializable ;

insert into test_1202 values (5, 'e', now());

 

transaction B:

begin work isolation level serializable ;

insert into test_1202 values (3, 'c', now()); or

 

transaction A:

mytest=# commit;

COMMIT

 

transaction B:

mytest=# commit;

ERROR: could not serialize access due to read/write dependencies among transactions

DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.

HINT: The transaction might succeed if retried.

 

==============================================

隔离级别为serializable ，并且设置为 DEFERRABLE

transaction A:

mytest=# begin isolation level serializable ;

BEGIN

mytest=# insert into test_1202 values (1, 'a', now());

INSERT 0 1

mytest=# select * from test_1202 ;

id | col1 | c_time

----+------+----------------------------

1 | a | 2018-12-03 05:22:14.520799

2 | b | 2018-12-03 05:22:26.481592

1 | a | 2018-12-03 05:22:55.58385

(3 rows)

 

transaction B:

mytest=# begin isolation level serializable ;

BEGIN

mytest=# insert into test_1202 values (2, 'b', now());

INSERT 0 1

 

transaction A:

mytest=# commit;

COMMIT

 

transaction B:

mytest=# commit;

COMMIT