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