事务的操作有 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