PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce

12 minute read

背景

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

Flag Counter

digoal’s 大量PostgreSQL文章入口