PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce
背景
Serializable 隔离级别
目标是模拟serializable的隔离级别事务的提交顺序转换为串行的执行顺序.
例如 :
Start session a serializable
Start session b serializable
Session a SQL …
Session b SQL …
Session a|b SQL …
…. Session a|b SQL …
Commit b
Commit a
这个场景模拟成 :
Start session b
Sql …
Commit b
Start session a
Sql … 如果会话a读过的数据在B中被变更, 那么a会话将提交失败.
Commit a
PostgreSQL 串行事务隔离级别的实现, 通过对扫描过的数据加载预锁来实现(内存中的一种弱冲突锁, 只在事务结束时判断是否有数据依赖性的冲突)
因为涉及到扫描的数据, 所以这种锁和执行计划有关.
例如
Select * from tbl where a=1;
如果没有索引, 那么是全表扫描, 需要扫描所有的数据块.
加载的预锁是表级别的预锁. (那么期间如果其他串行事务对这个表有任何变更, 包括插入,删除,更新等. 并且先提交的话.)
这个会话结束的时候会发现预加锁的数据被其他串行事务变更了, 所以会提交失败.
如果a上有索引的话, 执行计划走索引的情况下, 扫描的数据包括行和索引页.
那么加载的预锁包含行和索引页.
这种情况仅当其他串行事务在此期间变更了相对应的行或者是索引页才会在结束时发生冲突.
例子1
全表扫描, 表级预锁
会话A :
digoal=# select pg_backend_pid();
-[ RECORD 1 ]--+------
pg_backend_pid | 12186
会话B :
digoal=# select pg_backend_pid();
-[ RECORD 1 ]--+------
pg_backend_pid | 12222
会话A :
digoal=# truncate iso_test ;
TRUNCATE TABLE
digoal=# insert into iso_test select generate_series(1,100000);
INSERT 0 100000
digoal=# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=100;
-[ RECORD 1 ]
sum | 100
会话 C :
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtrans
action | pid | mode | granted | fastpath
----------+------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-------------
-------+-------+-----------------+---------+----------
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | AccessShareLock | t | t
| virtualxid | | | | | 1/157993 | | | | | 1/157993
| 12186 | ExclusiveLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | SIReadLock | t | f
(3 rows)
会话 B :
digoal=# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=10;
-[ RECORD 1 ]
sum | 10
会话 C :
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtrans
action | pid | mode | granted | fastpath
----------+------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-------------
-------+-------+-----------------+---------+----------
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | AccessShareLock | t | t
| virtualxid | | | | | 1/157993 | | | | | 1/157993
| 12186 | ExclusiveLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | AccessShareLock | t | t
| virtualxid | | | | | 2/6433312 | | | | | 2/6433312
| 12222 | ExclusiveLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | SIReadLock | t | f
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | SIReadLock | t | f
(6 rows)
会话 A :
digoal=# insert into iso_test values (1,'test');
INSERT 0 1
会话 B :
digoal=# insert into iso_test values (2,'test');
INSERT 0 1
会话 C :
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtr
ansaction | pid | mode | granted | fastpath
----------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+----------
----------+-------+------------------+---------+----------
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | RowExclusiveLock | t | t
| virtualxid | | | | | 1/157993 | | | | | 1/157993
| 12186 | ExclusiveLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | RowExclusiveLock | t | t
| virtualxid | | | | | 2/6433312 | | | | | 2/6433312
| 12222 | ExclusiveLock | t | t
| transactionid | | | | | | 316732564 | | | | 1/157993
| 12186 | ExclusiveLock | t | f
| transactionid | | | | | | 316732565 | | | | 2/6433312
| 12222 | ExclusiveLock | t | f
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | SIReadLock | t | f
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | SIReadLock | t | f
(10 rows)
会话 A :
digoal=# commit;
COMMIT
会话 C :
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtr
ansaction | pid | mode | granted | fastpath
----------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+----------
----------+-------+------------------+---------+----------
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | RowExclusiveLock | t | t
| virtualxid | | | | | 2/6433312 | | | | | 2/6433312
| 12222 | ExclusiveLock | t | t
| transactionid | | | | | | 316732565 | | | | 2/6433312
| 12222 | ExclusiveLock | t | f
iso_test | relation | 16384 | 92992 | | | | | | | | 1/157993
| 12186 | SIReadLock | t | f
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433312
| 12222 | SIReadLock | t | f
(6 rows)
会话 B :
digoal=# 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.
会话 C :
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransac
tion | pid | mode | granted | fastpath
----------+----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+---------------
-----+-----+------+---------+----------
(0 rows)
例子2
行锁和索引页锁, 并且索引页为同一页的情况. 同样发生冲突.
同样的场景, 加索引测试 :
digoal=# create index idx_iso_test_1 on iso_test (id);
CREATE INDEX
digoal=# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=100;
-[ RECORD 1 ]
sum | 100
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtua
ltransaction | pid | mode | granted | fastpath
----------------+------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-------
-------------+-------+-----------------+---------+----------
idx_iso_test_1 | relation | 16384 | 93017 | | | | | | | | 1/1579
96 | 12186 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/1579
96 | 12186 | AccessShareLock | t | t
| virtualxid | | | | | 1/157996 | | | | | 1/1579
96 | 12186 | ExclusiveLock | t | t
iso_test | tuple | 16384 | 92992 | 0 | 100 | | | | | | 1/1579
96 | 12186 | SIReadLock | t | f
idx_iso_test_1 | page | 16384 | 93017 | 1 | | | | | | | 1/1579
96 | 12186 | SIReadLock | t | f
(5 rows)
digoal=# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=10;
-[ RECORD 1 ]
sum | 10
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtua
ltransaction | pid | mode | granted | fastpath
----------------+------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-------
-------------+-------+-----------------+---------+----------
idx_iso_test_1 | relation | 16384 | 93017 | | | | | | | | 1/1579
96 | 12186 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/1579
96 | 12186 | AccessShareLock | t | t
| virtualxid | | | | | 1/157996 | | | | | 1/1579
96 | 12186 | ExclusiveLock | t | t
idx_iso_test_1 | relation | 16384 | 93017 | | | | | | | | 2/6433
314 | 12222 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6433
314 | 12222 | AccessShareLock | t | t
| virtualxid | | | | | 2/6433314 | | | | | 2/6433
314 | 12222 | ExclusiveLock | t | t
iso_test | tuple | 16384 | 92992 | 0 | 100 | | | | | | 1/1579
96 | 12186 | SIReadLock | t | f
iso_test | tuple | 16384 | 92992 | 0 | 10 | | | | | | 2/6433
314 | 12222 | SIReadLock | t | f
idx_iso_test_1 | page | 16384 | 93017 | 1 | | | | | | | 1/1579
96 | 12186 | SIReadLock | t | f
idx_iso_test_1 | page | 16384 | 93017 | 1 | | | | | | | 2/6433
314 | 12222 | SIReadLock | t | f
(10 rows)
digoal=# insert into iso_test values (1,'test');
INSERT 0 1
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | vir
tualtransaction | pid | mode | granted | fastpath
----------------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+----
----------------+-------+------------------+---------+----------
idx_iso_test_1 | relation | 16384 | 93017 | | | | | | | | 1/1
57996 | 12186 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/1
57996 | 12186 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/1
57996 | 12186 | RowExclusiveLock | t | t
| virtualxid | | | | | 1/157996 | | | | | 1/1
57996 | 12186 | ExclusiveLock | t | t
idx_iso_test_1 | relation | 16384 | 93017 | | | | | | | | 2/6
433314 | 12222 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6
433314 | 12222 | AccessShareLock | t | t
| virtualxid | | | | | 2/6433314 | | | | | 2/6
433314 | 12222 | ExclusiveLock | t | t
| transactionid | | | | | | 316732567 | | | | 1/1
57996 | 12186 | ExclusiveLock | t | f
iso_test | tuple | 16384 | 92992 | 0 | 100 | | | | | | 1/1
57996 | 12186 | SIReadLock | t | f
iso_test | tuple | 16384 | 92992 | 0 | 10 | | | | | | 2/6
433314 | 12222 | SIReadLock | t | f
idx_iso_test_1 | page | 16384 | 93017 | 1 | | | | | | | 1/1
57996 | 12186 | SIReadLock | t | f
idx_iso_test_1 | page | 16384 | 93017 | 1 | | | | | | | 2/6
433314 | 12222 | SIReadLock | t | f
(12 rows)
digoal=# insert into iso_test values (2,'test');
INSERT 0 1
digoal=# select relation::regclass,* from pg_locks where pid in (12186,12222);
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | vir
tualtransaction | pid | mode | granted | fastpath
----------------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+----
----------------+-------+------------------+---------+----------
idx_iso_test_1 | relation | 16384 | 93017 | | | | | | | | 1/1
57996 | 12186 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/1
57996 | 12186 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 1/1
57996 | 12186 | RowExclusiveLock | t | t
| virtualxid | | | | | 1/157996 | | | | | 1/1
57996 | 12186 | ExclusiveLock | t | t
idx_iso_test_1 | relation | 16384 | 93017 | | | | | | | | 2/6
433314 | 12222 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6
433314 | 12222 | AccessShareLock | t | t
iso_test | relation | 16384 | 92992 | | | | | | | | 2/6
433314 | 12222 | RowExclusiveLock | t | t
| virtualxid | | | | | 2/6433314 | | | | | 2/6
433314 | 12222 | ExclusiveLock | t | t
| transactionid | | | | | | 316732567 | | | | 1/1
57996 | 12186 | ExclusiveLock | t | f
| transactionid | | | | | | 316732568 | | | | 2/6
433314 | 12222 | ExclusiveLock | t | f
iso_test | tuple | 16384 | 92992 | 0 | 100 | | | | | | 1/1
57996 | 12186 | SIReadLock | t | f
iso_test | tuple | 16384 | 92992 | 0 | 10 | | | | | | 2/6
433314 | 12222 | SIReadLock | t | f
idx_iso_test_1 | page | 16384 | 93017 | 1 | | | | | | | 1/1
57996 | 12186 | SIReadLock | t | f
idx_iso_test_1 | page | 16384 | 93017 | 1 | | | | | | | 2/6
433314 | 12222 | SIReadLock | t | f
(14 rows)
digoal=# commit;
COMMIT
digoal=# 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.
索引页用了同一个, 并且被插入语句更新了. 所以发生了冲突
例子3
行数和索引页锁, 但是索引页为不同的页面
如果其中一个插入的值不在1号索引页则没有问题, 例如
digoal=# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=100;
-[ RECORD 1 ]
sum | 100
digoal=# insert into iso_test values (1,'test');
INSERT 0 1
digoal=# commit;
COMMIT
digoal=# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=10;
-[ RECORD 1 ]
sum | 10
digoal=# insert into iso_test values (200000,'test');
INSERT 0 1
digoal=# commit;
COMMIT
(200000,'test') 这个索引页不在1号
idx_iso_test_1 | page | 16384 | 93017 | 275 | | | | | | | 2/6433
316 | 12222 | SIReadLock | t | f
参考
http://www.postgresql.org/docs/9.3/static/transaction-iso.html
http://www.postgresql.org/docs/9.3/static/runtime-config-developer.html