PostgreSQL V9.6 SELECT操作加锁与隔离级别的示例

SELECT操作触发的锁

    例1，隔离级别为可串行化：

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM parent;

SELECT * FROM active_locks;

COMMIT;

    查询active_locks视图，可以得到类似如下的结果，表明在表上增加了一个表级锁“AccessShareLock”和谓词锁“SIReadLock”，在索引parent_pkey上增加了一个“AccessShareLock”锁：

   relname   | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted

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

 parent_pkey | relation |    12401 |    16392 |      |       | 2/14               | 12028 | AccessShareLock | t

 parent      | relation |    12401 |    16389 |      |       | 2/14               | 12028 | AccessShareLock | t

 parent      | relation |    12401 |    16389 |      |       | 2/14               | 12028 | SIReadLock      | t

 

    例2，隔离级别为可串行化，但查询使用索引并读取表数据：

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM parent WHERE pid=2;  //“pid=2”指明要使用索引，但“*”表明要读取表数据

SELECT * FROM active_locks;

COMMIT;

    查询active_locks视图，可以得到类似如下的结果，表明在表上增加了一个表级锁“AccessShareLock”在元组上增加了谓词锁“SIReadLock”，在索引parent_pkey上增加了一个“AccessShareLock”锁且在索引页上增加了一个谓词锁“SIReadLock”：

  relname   | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/18               | 12028 | AccessShareLock | t

parent      | relation |    12401 |    16389 |      |       | 2/18               | 12028 | AccessShareLock | t

parent      | tuple    |    12401 |    16389 |    0 |     4 | 2/18               | 12028 | SIReadLock      | t

parent_pkey | page     |    12401 |    16392 |    1 |       | 2/18               | 12028 | SIReadLock      | t

 

    例3，隔离级别为可串行化，但查询只在索引上进行：

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT pid FROM parent WHERE pid=2; //“pid=2”指明要使用索引，但“pid”表明不会读取表数据，只需要读取索引即可

SELECT * FROM active_locks;

COMMIT;

    查询active_locks视图，可以得到类似如下的结果，表明在表上增加了一个表级锁“AccessShareLock”在元组上增加了谓词锁“SIReadLock”，在索引parent_pkey上增加了一个“AccessShareLock”锁且在索引页上增加了一个谓词锁“SIReadLock”：

  relname   | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/17               | 12028 | AccessShareLock | t

parent      | relation |    12401 |    16389 |      |       | 2/17               | 12028 | AccessShareLock | t

parent_pkey | page     |    12401 |    16392 |    1 |       | 2/17               | 12028 | SIReadLock      | t

parent      | tuple    |    12401 |    16389 |    0 |     4 | 2/17               | 12028 | SIReadLock      | t

 

    例4，隔离级别为读已提交：

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM parent;

SELECT * FROM active_locks;

COMMIT;

    查询active_locks视图，可以得到类似如下的结果，表明在表上和索引上分别增加了一个“RowExclusiveLock”锁：

   relname   | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted

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

 parent_pkey | relation |    12401 |    16392 |      |       | 2/15               | 12028 | AccessShareLock | t

 parent      | relation |    12401 |    16389 |      |       | 2/15               | 12028 | AccessShareLock | t

   

    例5，隔离级别为可串行化，但查询使用索引并读取表数据：

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM parent WHERE pid=2;  //“pid=2”指明要使用索引，但“*”表明要读取表数据

SELECT * FROM active_locks;

COMMIT;

    查询active_locks视图，可以得到类似如下的结果，表明在表上增加了一个表级锁“AccessShareLock”，在索引parent_pkey上增加了一个“AccessShareLock”锁：

   relname   | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted

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

 parent_pkey | relation |    12401 |    16392 |      |       | 2/20               | 12028 | AccessShareLock | t

 parent      | relation |    12401 |    16389 |      |       | 2/20               | 12028 | AccessShareLock | t

 

    例6，隔离级别为可串行化，但查询只在索引上进行：

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT pid FROM parent WHERE pid=2; //“pid=2”指明要使用索引，但“pid”表明不会读取表数据，只需要读取索引即可

SELECT * FROM active_locks;

COMMIT;

    查询active_locks视图，可以得到类似如下的结果，表明在表上增加了一个表级锁“AccessShareLock”，在索引parent_pkey上增加了一个“AccessShareLock”锁：

  relname   | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/21               | 12028 | AccessShareLock | t

parent      | relation |    12401 |    16389 |      |       | 2/21               | 12028 | AccessShareLock | t