PostgreSQL锁案例

1.前置说明

下面的案例是通过显式事务来进行展示的；下面案例中所指session是相对于不同的数据库连接，也就是不同的数据库服务进程而言的。数据库并发操作数据，在这里可以理解为是数据库连接并发操作事务。在postgresql.conf文件中可以设置max_connections字段值，表示PostgreSQL数据库支持最大的连接数。在自己研发环境中window7 四核处理器中，通过测试，发现最少需要配置4个连接数，否则PostgreSQL服务都起不来。

案例中使用的是Navicat Premium工具操作PostgreSQL数据库的，大家也可以使用其他方式和工具，比如直接进入dos环境下操作，也可以使用pgAdmin III工具。

2.物理存储结构术语

Relation：表示表或索引。

Tuple：表示表中的数据行。

Page：表示在磁盘中的数据块。数据块默认是8KB，最大32KB，一个数据块可以存放多行数据。

Buffer：表示在内存中的数据块。

3.常用系统表

首先，介绍一下后面案例会用到的PostgreSQL系统表。

(1)pg_class

该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有字段对所有对象类型都有意义。

关注下面几个字段：

oid：该表中的行标识符（隐藏属性;必须明确选择）。

relname：标识表，索引，视图等的名称。

relkind：标识该行记录的关系类型，r =普通表，i =索引，S =序列，v =视图，m =物化视图， c =复合类型，t = TOAST表，f =外部表。

(2)pg_stat_activity

官方解释：每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前回话的状态和查询等。

    关注下面几个字段：

pid：后端的进程id。

usename：登录到此后端的用户的名称。

wait_event_type：后端正在等待的事件的类型（如果有的话）;否则为NULL。可能的值如下LWLock、Lock、BufferPin、Activity、Extension、Client、IPC、Timeout、IO、backend_type。

wait_event：如果后端正在等待，则为等待事件名称，否则为空。

state：后端当前状态active、idle、idle in transaction、idle in transaction (aborted)、fastpath  function call(后端正在执行快速路径功能)、disabled。

query：这个后端的最新查询的文本。 如果state处于活动状态，则此字段显示当前正在执行的查询在所有其他状态下，它显示最后执行的查询。

(3)pg_locks

pg_locks提供了数据库服务器上打开事务中保持的锁的信息。pg_locks中对每一个活动可锁对象、请求锁模式和相关事务的组合都有一行。因此，如果多个事务持有或者正在等待一个可锁对象上的锁，同一个可锁对象可能出现很多次。但是，一个当前没有被锁的对象根本不会出现。

       在下面的案例中，主要是搜集该表中锁的信息，尤其关注以下字段：

名称	描述
locktype	可锁对象的类型：relation, extend, page, tuple, transactionid, virtualxid, object, userlock, or advisory。
database	锁目标存在的数据库的OID，如果目标是一个共享对象则为0，如果目标是一个事务ID则为空。
relation	作为锁目标的关系的OID，如果目标不是一个关系或者只是关系的一部分则此列为空。
page	作为锁目标的页在关系中的页号，如果目标不是一个关系页或元组则此列为空。
tuple	作为锁目标的元组在页中的元组号(也可以说行号)，如果目标不是一个元组则此列为空。
virtualxid	作为锁目标的事务虚拟ID，如果目标不是一个虚拟事务ID则此列为空
transactionid	作为锁目标的事务ID，如果目标不是一个事务ID则此列为空ID
virtualtransaction	保持这个锁或者正在等待这个锁的事务的虚拟ID。
pid	保持这个锁或者正在等待这个锁的服务器进程的PID，如果此锁被一个预备事务所持有则此列为空。
mode	此进程已持有或者希望持有的锁模式。
granted	如果锁已授予则为真，如果锁被等待则为假。
fastpath	如果锁通过快速路径获得则为真，通过主锁表获得则为假。

virtualxid：虚拟事务id，每次生成一个事务，会在pg_clog下的commit log文件中占用2bit空间，因为有些事务中没有实际的操作数据的语句，如select语句，所以这种分配事务id有些浪费空间了，于是提出了虚拟事务的概念，主要是为了节省空间。

4.常用系统函数

也介绍几个常用的系统函数，后面可能会用到。

操作	作用
select version();	查询数据库服务的版本
Select current_database();	查询当前数据库名称
Select current_user;	查询当前连接的用户
Select pg_backend_pid();	查看与当前会话相关联的服务器进程ID
select txid_current();	查询当前进程正在执行的事务id
select ctid from table_name;	查询表上页行信息(page,tuple)，table_name是自定义表
date_trunc('second', now())	截取当前系统时间，精确到秒

5.案例环境

在本地安装PostgreSQL服务，并配置了10个最大连接数；

使用Navicat Premium工具连上本地PostgreSQL服务；

随意启动一个会话，创建一下student1和student2表，执行语句除了名称不一样，其他都一样，参考下面的语句：

CREATE TABLE student1 (

         id serial PRIMARY KEY,

         name VARCHAR (64) NOT NULL,

         age INTEGER NOT NULL,

         updatetime TIMESTAMP WITHOUT TIME ZONE

);

再在两个表中分别插入10条记录，可以参考下面：

INSERT INTO student1 ( name, age, updatetime ) VALUES ( 'Tom', '18', date_trunc('second', now()));

6.【案例一】

开启一个事务，不执行任何操作，也不提交事务。查看锁信息。

案例中session1、session2的进程pid分别为11920、15312。

Session1中开启一个事务：

BEGIN;

Session2中查看session1相关的锁信息：

SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

结果:

通过上面的介绍，我们知道virtualxid：虚拟事务id，每次生成一个事务，总会有虚拟事务的概念发生，locktype指明了写锁的对象是虚拟事务。

7.【案例二】

开启一个事务，并执行显示加表锁操作。

案例中session1、session2的进程pid分别为11920、15312。

Session1中开启一个事务，并对student1表显示加表级锁：

BEGIN;

Lock table student1；

Session2中查看session1相关的锁信息：

SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

 

 

       可以看到，locktype是表关系类型，指明student1表被加上最严格的表级锁，因为显示加表级锁语句中没有指明锁模式，就是用了缺省型。

       从图中也可以看出，若执行一个锁表命令后，并没有进行实际的修改操作，这时transactionid为空，也就说明了事务id是在实际需要的时候才产生的。

       注意，执行这一个加锁命令后，有时在系统表上会产生一些附加的锁。

8.【案例三】

多个会话并发对同一个表显示加锁，后请求加锁的会话可能会被阻塞。

案例中session1、session2、session3的进程pid分别为11920、15312、12596。

Session1中开启一个事务，并对student1表显示加表级锁：

BEGIN;

Lock table student1；

Session2中也开启一个事务，并请求student1表显示加表级锁：

BEGIN;

Lock table student1；

首先，出现的现象是工具窗口，运行按钮置灰，一致没有返回执行结果。

再者，

Session3中查看session1、session2相关的锁信息：

SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

这里关注一下最后一行，granted字段值为f，表明15312进程pid的session2没有授权持有student1表Access Exclusive Lock，从而被阻塞了。

从这里可以看出，想要查看被阻塞的进程，只要查询pg_locks系统表中granted字段值为false的进程就OK了。

Session3中查看一下数据库进程状态：

SELECT datname, usename, pid, query_start, state_change, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE pid in( SELECT pid FROM pg_locks WHERE pid <> pg_backend_pid());

可以看到session2在等待relation类型锁，session1正在执行事务。

现在，可以使session1放开锁，再查看锁的状态。

Session1 执行事务提交：

COMMIT;

执行完成后，首先可以看到session2工具窗口的状态恢复了正常，并且执行完毕。

Session3中查看一下锁的状态：

SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

可以看到session2进程持有了表student2的Access Exclusive Lock。

9.【案例四】

多个会话并发对表的同一行显示加锁，后请求加锁的会话可能会被阻塞。

案例中session1、session2、session3的进程pid分别为12624、11012 、11432。

Session1 中查询student1表id = 2的记录：

select * from student1 where id = 2;

Session1开启一个事务，显示加行锁：

BEGIN;

select * from student1 where id = 2 for update;

Session3中查看一下锁的状态：

SELECT relation::regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid();

效果如下：

查询结果中有显示系统表，这里不关注。

       理论上，加行锁的过程，先在表上加上一个表级意向锁，上图可以看出，加行锁不仅会在表上加意向锁，也会在相应的主键上加意向锁。其中student1_pkey就是student1的主键。

       上面的图中并没有发现行锁，原因是pg_locks并不能显示每个行锁的信息，因为行锁信息并不会记录到共享内存中。如果每个行锁都在内存中存在一条记录信息，在对表做全表更新时，表有多少行就在内存中记录多少行锁信息，那么内存会吃不消。所以PostgreSQL被设计成内中并不记录行锁的信息。《PostgreSQL修炼之道 从小工到专家》-唐成 书中是这样解释的，网上的大部分博客中也是说PostgreSQL数据库不保存行锁的信息，这一点我是保持怀疑的，后面具体的例子再说明这一点。

       如果pg_locks中没有行锁信息，如何知道一个进程被另一个进程的行锁阻塞了呢？

Session2同样执行任务加行锁：

BEGIN;

select * from student1 where id = 2 for update;

此时，session2进程被阻塞，窗口运行按钮保持置灰，结果信息窗口一直未返回结果。

Session3查看锁信息：

         SELECT relation::regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid();

可以看到granted字段为false的进程是pid为11012的Session2的进程，说明该进程别阻塞，并且是在申请类型为tansactionalid的锁时被阻塞了，对应tansactionalid=306387的事务，从表上可以看出这个tansactionalid已经被进程pid为12624的session1会话持有了。

从上面的分析可以知道，事件上行锁的阻塞信息时通过tansactionalid类型的锁体现的。从理论上讲，行锁是会在数据行上加自己的tansactionalid的，另一个进程读到这一行时，如果发现上一个操作该行的事务未结束，会把上一个事务的tansactionalid读出来，然后申请在这个tansactionalid上加上ShareLock，等待上一个事务结束，再获得ExclusiveLock。而持有行锁的进程已经在此tansactionalid上加了ExclusiveLock，所以后面要更新这行的进程会被阻塞。

       若要查询因行锁被阻塞的进程信息，只需要查询pg_locks中类型为tansactionalid的锁信息就OK了。

       再者，上面的结果图中最后一行存在一个tuple类型的锁，也就是行锁，是session2进程持有了（page,tuple）= ‘(0,2)’数据行的AccessExclusiveLock，这一点和唐成的书里说明的有一些不同，这里不具体的讨论他的观点是否存在什么误差，只是简单提出来，让大家明白可能存在这样一个问题。

通过上面的结果图，我可以认为，在某些场景下，pg_locks系统表是可以展示行级锁（并且是相关的写锁）信息，这里只强调相关的写锁，因为在尝试了多种情况下对行记录进行加锁，然后查询该行记录，都是可以直接完成的，没有被阻塞。

而且，通过查看结果图中最后一行pg_locks的page和tuple字段可以看到阻塞行的信息，上图中展示的(page,tuple)为（0,2）,其实这两个字段的组合就是表示系统字段ctid的值。

Session3上查看student1表的ctid=’(0,2)’的行信息：

select * from student1 where ctid = '(0,2)';

查询结果就是student1表上id=2的行的信息，说明刚才加行锁的操作，确实阻塞在了ctid=’(0,2)’的行上了。

Session1上提交加行锁的事务：

COMMIT;

执行完成后，session2会话窗口返回查询结果。

Session3查看锁信息：

SELECT relation::regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid();

从上图中可以看到，session2已经在自己的tansactionalid=306388上加了ExclusiveLock。

      

这里还可以提出一个疑问：

session1已经请求在student1表id=2的行上加锁，并完成了加锁，体现在自己的tansactionalid= 加上了ExclusiveLock，那么，为什么session2请求在student1表id=2的行上加锁时，pg_locks系统表显示session2是持有tuple类型的AccessExclusiveLock，并且被session1的事务阻塞在id=2的行，从而对session1的tansactionalid请求加ShareLock？

       我认为的是，对于更新行数据的事务，都会先获取tuple类型的写锁，并且会保存行和事务的关联关系，在此基础下，对操作对应行的事务tansactionalid进行加锁，而且tuplel类型锁与tansactionalid类型锁的执行顺序如下：

       上面这样一个模型，完全可以解释多个事务并发操作一行数据时，加锁的顺序，我们后续案例有说明。同时，这样也能体现出，对数据产生实际修改的事务执行操作是放在最靠近数据对象的位置。

10.【案例五】

多个会话并发修改行数据时，后续的会话可能会被阻塞。

案例中session1、session2、session3、session4的进程pid分别为5952、4472 、4456、4920。

Session1开启事务，修改student1表中id=2的数据行：

BEGIN;

update student1 set updatetime = date_trunc('second',now()) where id = 2;

Session3查询锁信息：

SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

可以看出，对于update操作，会对表先加意向锁RowExclusiveLock，参考上面的tuplel类型锁与tansactionalid类型锁加锁模型，tansactionalid = 306446事务已经执行完成，session1窗口也有结果返回，在程序内部已经完成对行级锁的持有和释放，但是tansactionalid = 306446事务还未提交，所以对于id=2的数据行修改虽然完成，但对关联该行的tansactionalid = 306446事务的写锁还未释放，如果现在有其他事请求修改该行数据，应该会被阻塞在tansactionalid = 306446事务的写锁上。

Session2开启事务，修改student1表中id=2的数据行：

BEGIN;

update student1 set updatetime = date_trunc('second',now()) where id = 2;

可以看到session2窗口执行没有返回结果，应该是session1会话被阻塞了。

Session4查询锁信息：

SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

       从图中可以看出，确实session2在请求到ctid=’(0,30)’数据行的tuplel类型锁ExclusiveLock后，发现上一个连接操作该行的tansactionalid = 306446事务还未结束，并且该连接持有tansactionalid = 306446事务的ExclusiveLock，那么session2就执行请求tansactionalid = 306446事务的ShareLocks，等待tansactionalid = 306446事务结束后，可以开始session2自己事务的执行操作，并且session2此刻是保持持有ctid=’(0,30)’数据行的tuplel类型锁ExclusiveLock的。这一场景与上面的tuplel类型锁与tansactionalid类型锁加锁模型是一致的。

Session3修改student1表中id=2的数据行：

update student1 set updatetime = date_trunc('second',now()) where id = 2;

这里不显示开启任务也一样，数据库自己在执行操作时会开启任务的。

Session4查看锁信息：

SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

从图中可以看到，session3被阻塞在请求ctid=’(0,30)’数据行的tuplel类型锁ExclusiveLock，因为这个锁被session2持有着。这一场景与上面的tuplel类型锁与tansactionalid类型锁加锁模型是一致的。

Session4中查看一下数据库进程状态：

SELECT datname, usename, pid, query_start, state_change, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE pid in( SELECT pid FROM pg_locks WHERE pid <> pg_backend_pid());

       可以看到三个进程的状态，session1处于事务执行中，session2在等待tansactionalid类型锁，session3在等待tuplel类型锁。

Session4查看student1表的ctid=’(0,30)’的行信息：

select * from student1 where ctid = '(0,30)';

查询结果就是student1表上id=2的行的信息，说明刚才加行锁的操作，确实阻塞在了ctid=’(0,30)’的行上。其实这个状态也可以说明PostgreSQL数据库对于读取行信息时是无阻力的，也就是不执行任何锁操作。

Session1提交事务：

COMMIT;

       Session1完成了tansactionalid = 306446事务，Session2窗口恢复正常。

Session4查看数据库进程连接状态：

SELECT datname, usename, pid, query_start, state_change, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE pid in( SELECT pid FROM pg_locks WHERE pid <> pg_backend_pid());

       可以看到，现在session2处于任务执行中，session3在等待session2任务执行结束。

Session2提交事务：

COMMIT;

       Session3窗口恢复正常。

       再次查看数据库进程状态和锁信息，均恢复正常状态。

11.【案例六】

表级锁AccessExclusiveLock与其他任何表级锁冲突。

案例中session1、session2、session3进程pid分别为5952、4472、4920。

Session1开启任务，修改表student1字段name为sname:

BEGIN;

alter table student1 rename column name to sname;

Session3查看锁信息：

         SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

可以看到已经给student1表加上了AccessExclusiveLock。

Session2查询student1表信息：

         select * from student1;

session2窗口处于未返回查询结果。

Session3查看锁信息：

         SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

Select语句会请求加AccessShareLock，session2在请求该锁时与ses1ion1已经持有的AccessExclusiveLock冲突了。

Session3手动结束session2的查询状态，取消后台操作，回滚未提交事物：

         select pg_cancel_backend(4472);

session3窗口返回执行成功，pg_cancel_backend字段为true。 session2窗口会报错误信息，提示用户请求取消语句。

Session3在查看锁信息：

         SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

类似的，还有其他各种表级锁之间的冲突场景，这里不一一例举了。

12.【案例七】

并发请求数据库资源时，由于请求顺序交叉引发的数据库死锁问题。

案例中session1、session2、session3进程pid分别为2740、10268、7088。

Session1开启任务，修改表student1字段name为sname:

BEGIN;

alter table student1 rename column name to sname;

此时，session1持有student1表的AccessExclusiveLock。

Session2开启任务，修改表student2字段name为sname:

BEGIN;

alter table student1 rename column name to sname;

此时，session2持有student2表的AccessExclusiveLock。

Session3查看锁信息;

         SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

Session1继续查询student2表：

select * from student2;

session1窗口为返回结果。

Session3查看锁信息;

         SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

从锁信息可以看出，Session1会话在请求student2表的AccessShareLock时，与session2持有student2表AccessExclusiveLock冲突，Session1会进入等待session2结束对student2表AccessExclusiveLock的持有的状态。那么，如果现在使session2查询student1表，会出现什么情况呢？

Session2，查询student1表：

select * from student1;

session2窗口会报错，错误信息如下：

提示触发了数据库死锁，是在请求执行select * from student1时发生的。

在服务安装路径下PostgreSQL\data\pg_log文件夹下的当天的日志文件中也可以查看到死锁日志记录：

PostgreSQL数据库会自动检测死锁问题，自动结束触发死锁的进程事务，并使事务处于异常状态。

可以修改Postgresql.conf文件中的选项为：

log_lock_waits = on

deadlock_timeout = 10s

当发生死锁被程序检测到后，可以有10s的时间，查看异常状态。

Session3查看锁信息;

         SELECT relation :: regclass AS relname, * FROM pg_locks WHERE pid <> pg_backend_pid ();

       现在看不到session2进程的锁信息了。

Session3查看一下数据库进程状态：

SELECT datname, usename, pid, query_start, state_change, wait_event_type, wait_event, state, query FROM pg_stat_activity where pid in(2740, 10268);

可以看到触发死锁的session2进程，显示执行事务状态异常。

Session2提交事务，并查询student2表：

COMMIT;

select * from student2;

可以看到字段name没有被修改成sname。

这个死锁场景主要是由于两个会话请求资源的顺序有交叉，在并发操作数据时，很有可能出现死锁问题，所以建议不同事务操作同样资源时，要保持操作顺序一致，能尽可能避免这种场景的死锁发生。