在并发环境下为了解决并发一致性问题保证事务的隔离性，PostgreSQL采用了锁的机制。当一个事务在进行操作时会对操作的数据进行加锁，从而限制另一个事务的操作。为保证效率和想，加锁的粒度不宜太大。加锁的意义就在于当多个会话同时访问数据库的同一数据时，为所有会话提供高效的数据访问，并同时维护严格的数据一致性，从而实现数据的多版本并发控制。

多版本并发控制的英文名称是Multiversion Concurrency Control，简称MVCC。
它是指每个SQL语句看到的都只是当前事务开始的数据快照，而不用去关心底层数据的当前状态。
这样可以保护语句不会看到可能由其他在相同数据行上执行更新的并发事务造成的不一致数据，
为每一个数据库会话提供事务隔离。MVCC避免了传统的数据库系统的锁定方法，
将通过锁争夺最小化的方法来达到多会话并发访问时的性能最大化目的。

PostgreSQL提供了多种类型的锁模式用于控制对表中数据的并发访问。在这些锁当中最主要的是表级锁与行级锁，此外还有页级锁、咨询锁等等。下面将重点介绍表级锁与行级锁。视频讲解如下：

一、表级锁

表级锁通常会在执行各种命令执行时自动获取，或者通过在事务中使用lock语句显式获取，每种表级锁都有自己的冲突集合。两个事务在同一时刻不能在同一个表上持有属于相互冲突模式的锁，但可以持有不冲突的锁。PostgreSQL的表级锁总共有八种模式，并存储在PostgreSQL的共享内存中。下表详细说明了这八种PostgreSQL表级锁。

这八种表级锁彼此之间存在一定的冲突，下表说明了它们之间的冲突关系。

在PostgreSQL数据库中可以通过pg_locks系统视图查询表上已经获取到的表级锁信息，下面通过一个具体的示例来说明。

（1）在会话一中查看当前数据库中的表，并查看表testtable1的结构。

postgres=# \d
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | testtable1 | table | postgres
(1 row)
postgres=# \d testtable1;
             Table "public.testtable1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 tid    | integer |           | not null | 
 tname  | text    |           |          | 
Indexes:
    "testtable1_pkey" PRIMARY KEY, btree (tid)
Tablespace: "mydemotbs"

（2）查看表testtable1的oid。

postgres=# select oid,relname,relkind,relfilenode from pg_class
postgres-# where relname ='testtable1';
  oid  |  relname   | relkind | relfilenode 
-------+------------+---------+-------------
 16395 | testtable1 | r       |       16395
(1 row)

（3）在会话一中开启一个事务并执行一条update语句。

postgres=# start transaction;
postgres=*# update testtable1 set tname='Tom123' where tid=1;
# 这里执行的事务没有结束。

（4）在会话二中更改表testtable1的结构，如：添加一个新的列。

postgres=# alter table testtable1 add dno int;
# 此时会话二的操作将会被阻塞。

（5）在会话三中查看表testtable1上的锁信息。

postgres=# \x
postgres=# select * from pg_locks where relation = 16395;
# 这里的“\x”表示将输出结果进行竖式显示。
# 输出的结果如下：
-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 13580
relation           | 16395
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 4/401
pid                | 41381
mode               | AccessExclusiveLock
granted            | f
fastpath           | f
-[ RECORD 2 ]------+--------------------
locktype           | relation
database           | 13580
relation           | 16395
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 3/644
pid                | 40920
mode               | RowExclusiveLock
granted            | t
fastpath           | f
# 从输出的结果可以看出，此时表testtable1上有两把表级锁，
# 分别是：AccessExclusiveLock和RowExclusiveLock。
# 但此时并不能观察到会话之间的阻塞。

（6）执行语句检查锁的等待，即：会话之间的阻塞信息。

postgres=# with  
  t_wait as  
  (  
   select 
     a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,
     a.classid,a.granted,a.objid,a.objsubid,a.pid,
     a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
     b.state,b.query,b.xact_start,b.query_start,b.usename,
     b.datname,b.client_addr,b.client_port,b.application_name  
   from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted  
  ),  
  t_run as  
  (  
   select 
     a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,
     a.classid,a.granted,a.objid,a.objsubid,a.pid,
     a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
     b.state,b.query,b.xact_start,b.query_start,b.usename,
     b.datname,b.client_addr,b.client_port,b.application_name  
   from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted  
  ),  
  t_overlap as  
  (  
   select r.* from t_wait w join t_run r on  
   (  
    r.locktype is not distinct from w.locktype and  
    r.database is not distinct from w.database and  
    r.relation is not distinct from w.relation and  
    r.page is not distinct from w.page and  
    r.tuple is not distinct from w.tuple and  
    r.virtualxid is not distinct from w.virtualxid and  
    r.transactionid is not distinct from w.transactionid and  
    r.classid is not distinct from w.classid and  
    r.objid is not distinct from w.objid and  
    r.objsubid is not distinct from w.objsubid and  
    r.pid <> w.pid  
   )  
  ),  
  t_unionall as  
  (  
   select r.* from t_overlap r  
   union all  
   select w.* from t_wait w  
  )  
  select 
  locktype,datname,relation::regclass,page,tuple,
  virtualxid,transactionid::text,classid::regclass,
  objid,objsubid, 
  string_agg(
  'Pid: '
  ||case when pid is null 
       then 'NULL' else pid::text end||chr(10)||'Lock_Granted: '
  ||case when granted is null 
       then 'NULL' else granted::text end||' , Mode: '
  ||case when mode is null 
       then 'NULL' else mode::text end||' , FastPath: '
  ||case when fastpath is null 
       then 'NULL' else fastpath::text end||' , VirtualTransaction: '
  ||case when virtualtransaction is null 
       then 'NULL' else virtualtransaction::text end||' , Session_State: '
  ||case when state is null 
       then 'NULL' else state::text end||chr(10)||'Username: '
  ||case when usename is null 
       then 'NULL' else usename::text end||' , Database: '
  ||case when datname is null 
       then 'NULL' else datname::text end||' , Client_Addr: '
  ||case when client_addr is null 
       then 'NULL' else client_addr::text end||' , Client_Port: '
  ||case when client_port is null 
       then 'NULL' else client_port::text end||' , Application_Name: '
  ||case when application_name is null 
       then 'NULL' else application_name::text end
  ||chr(10)||'Xact_Start: '
  ||case when xact_start is null 
       then 'NULL' else xact_start::text end||' , Query_Start: '
  ||case when query_start is null 
       then 'NULL' else query_start::text end||' , Xact_Elapse: '
  ||case when (now()-xact_start) is null 
       then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '
  ||case when (now()-query_start) is null 
       then 'NULL' else (now()-query_start)::text end
  ||chr(10)||'SQL (Current SQL in Transaction): '||chr(10)
  ||case when query is null then 'NULL' else query::text end,chr(10)
  ||'--------'||chr(10)  
  order by  
   ( case mode  
    when 'INVALID' then 0  
    when 'AccessShareLock' then 1  
    when 'RowShareLock' then 2  
    when 'RowExclusiveLock' then 3  
    when 'ShareUpdateExclusiveLock' then 4  
    when 'ShareLock' then 5  
    when 'ShareRowExclusiveLock' then 6  
    when 'ExclusiveLock' then 7  
    when 'AccessExclusiveLock' then 8  
    else 0  
   end ) desc,  
   (case when granted then 0 else 1 end) 
  ) as lock_conflict 
  from t_unionall  
  group by locktype,datname,relation,page,tuple,virtualxid,
  transactionid::text,classid,objid,objsubid; 
# 输出的结果如下：
-[ RECORD 1 ]-+------------------------------------------------------
locktype      | relation                                             
datname       | postgres                                             
relation      | testtable1                                           
page          |                                                      
tuple         |                                                      
virtualxid    |                                                      
transactionid |                                                      
classid       |                                                      
objid         |                                                      
objsubid      |                                                      
lock_conflict 
| Pid: 41381                                           
              | Lock_Granted: false , Mode: AccessExclusiveLock , ...
          | SQL (Current SQL in Transaction):                    
              | alter table testtable1 add dno int;                  
              | --------                                             
              | Pid: 40920                                           
              | Lock_Granted: true , Mode: RowExclusiveLock , ...    
          | SQL (Current SQL in Transaction):                    
              | update testtable1 set tname='Tom123' where tid=1;    
# 通过这里输出的信息可以看到阻塞的进程ID号，以及发生等待的SQL语句。

（7）在会话一执行结束事务操作。

postgres=*# commit;
# 此时会话二将成功执行。

（8）重新在会话三中查看表testtable1上的锁信息，此时将没有任何锁的信息输出。

postgres=# select * from pg_locks where relation = 16395;

二、行级锁

同一个事务可能会在相同的行上保持冲突的锁。但是除此之外，两个事务永远不可能在相同的行上持有冲突的锁。PostgreSQL行级锁不影响数据查询，它们只阻塞对同一行的写入者和加锁者。行级锁在事务结束时或保存点回滚的时候释放，就像表级锁一样。PostgreSQL行级锁支持以下四种不同的模式，如下表所示。

这四种行级锁彼此之间存在一定的冲突，下表说明了它们之间的冲突关系。

×表示存在相互之间的冲突。

在PostgreSQL中可以通过查询pg_locks的系统视图来获取行级锁的相关信息。下面展示了该系统视图的结构。

postgres=# \d pg_locks;
                   View "pg_catalog.pg_locks"
       Column       |   Type   | Collation | Nullable | Default 
--------------------+----------+-----------+----------+---------
 locktype           | text     |           |          | 
 database           | oid      |           |          | 
 relation           | oid      |           |          | 
 page               | integer  |           |          | 
 tuple              | smallint |           |          | 
 virtualxid         | text     |           |          | 
 transactionid      | xid      |           |          | 
 classid            | oid      |           |          | 
 objid              | oid      |           |          | 
 objsubid           | smallint |           |          | 
 virtualtransaction | text     |           |          | 
 pid                | integer  |           |          | 
 mode               | text     |           |          | 
 granted            | boolean  |           |          | 
 fastpath           | boolean  |           |          |

【赵渝强老师】PostgreSQL锁的类型

一、表级锁

二、行级锁

关系型数据库

热门文章

最新文章

【赵渝强老师】PostgreSQL锁的类型

一、 表级锁

二、 行级锁

关系型数据库

热门文章

最新文章

一、表级锁

二、行级锁