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

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

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE parent SET b1=10;

SELECT * FROM active_locks;

ROLLBACK;

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

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

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/22               | 12028 | RowExclusiveLock | t

parent      | relation |    12401 |    16389 |      |       | 2/22               | 12028 | RowExclusiveLock | t

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

 

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

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE parent SET b1=10 WHERE pid=2;  //“pid=2”指明要使用索引，但“b1=10”表明要读取表数据

SELECT * FROM active_locks;

ROLLBACK;

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

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

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/28               | 12028 | RowExclusiveLock | t

parent      | relation |    12401 |    16389 |      |       | 2/28               | 12028 | RowExclusiveLock | t

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

 

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

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

SELECT * FROM active_locks;

ROLLBACK;

    查询active_locks视图，可以得到类似如下的结果，表明在表上增加了一个表级锁“RowShareLock”和“RowExclusiveLock”，在索引parent_pkey上增加了三个锁包括谓词锁“SIReadLock”；另外，在子表child和子表的索引上，增加了一个谓词锁“SIReadLock”和“RowShareLock”锁且在子表的索引页上增加了“AccessShareLock”锁：

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

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

child_pkey  | relation |    12401 |    16397 |      |       | 2/29               | 12028 | AccessShareLock  | t

child       | relation |    12401 |    16394 |      |       | 2/29               | 12028 | RowShareLock     | t

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/29               | 12028 | RowExclusiveLock | t

parent      | relation |    12401 |    16389 |      |       | 2/29               | 12028 | RowShareLock     | t

parent      | relation |    12401 |    16389 |      |       | 2/29               | 12028 | RowExclusiveLock | t

child       | relation |    12401 |    16394 |      |       | 2/29               | 12028 | SIReadLock       | t

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

 

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

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE parent SET b1=10;

SELECT * FROM active_locks;

ROLLBACK;

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

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

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/30               | 12028 | RowExclusiveLock | t

parent      | relation |    12401 |    16389 |      |       | 2/30               | 12028 | RowExclusiveLock | t

   

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

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE parent SET b1=10 WHERE pid=2;

SELECT * FROM active_locks;

ROLLBACK;

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

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

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/31               | 12028 | RowExclusiveLock | t

parent      | relation |    12401 |    16389 |      |       | 2/31               | 12028 | RowExclusiveLock | t

 

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

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE parent SET pid=10 WHERE pid=2;

SELECT * FROM active_locks;

ROLLBACK;

    查询active_locks视图，可以得到类似如下的结果，表明在表上增加了表级锁“RowShareLock”和“RowExclusiveLock”，在索引parent_pkey上增加了“AccessShareLock”和“RowExclusiveLock”锁；在子表child和子表的索引上分别增加了“RowShareLock”和“AccessShareLock”锁：

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

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

child_pkey  | relation |    12401 |    16397 |      |       | 2/32               | 12028 | AccessShareLock  | t

child       | relation |    12401 |    16394 |      |       | 2/32               | 12028 | RowShareLock     | t

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

parent_pkey | relation |    12401 |    16392 |      |       | 2/32               | 12028 | RowExclusiveLock | t

parent      | relation |    12401 |    16389 |      |       | 2/32               | 12028 | RowShareLock     | t

parent      | relation |    12401 |    16389 |      |       | 2/32               | 12028 | RowExclusiveLock | t