GreenPlum锁表经历_greenplum锁表原理-CSDN博客

本文链接：https://blog.csdn.net/MyySophia/article/details/106520856

本文详述了在GreenPlum中遇到的锁表问题，深入探讨了不同类型的锁，如Access Share与Access Exclusive锁的冲突，以及如何引发死锁。通过实验展示了更新和删除在同一表上的并发性，指出在GP4上并发update不被支持，而在GP6上已得到改进。文章以实际案例解析了死锁的产生条件和解决方法，并强调了在进行vacuum、analyze操作时避免死锁的重要性。

摘要由CSDN通过智能技术生成

4.1 实验：Access exclusive 锁与Access share锁相互block 。

4.2 Share 锁与 Row Exclusive 锁冲突

4.3 update和delete在同一张表上是否可以并发？

4.4 基于postgresql 9.4 模拟dead lock

4.5 什么时候会获取exclusive lock

五、总结

一、问题发现

晚上接到报警信息，GP有慢查询，如图：

慢查询的监控设定的是300s，truncate执行300s这个太不应该了，但是首先想到的是数据库是不是出问题了。查看了GP集群并未发现异常。于是继续追查，从pg_log中看到有一个sql执行了9000+s。时间点也很吻合。

回忆了一下在下班前有分析数据库的数据倾斜，使用的是官方手册中的这两个view

gp_skew_coefficients
gp_skew_idle_fractions。

此时发现还有一个报警没看到。

从时间顺序上来看，查询系统表的报警先来。

可是只是select view这会block truncate操作吗？

答案是yes，select会申请一个Access share锁，truncate 会申请一个Access excludsive锁

这两个是会相互block。

二、 Lock 类型

No	Lock 类型	说明
1	ACCESS SHARE	ACCESS SHARE”锁模式只与“ACCESS EXCLUSIVE” 锁模式冲突; 查询命令（Select command）将会在它查询的表上获取”Access Shared” 锁,一般地，任何一个对表上的只读查询操作都将获取这种类型的锁. (Oracle 等沒有)
2	ROW SHARE	”Select for update”和”Select for share”命令将获得这种类型锁，并且所有被引用但没有 FOR UPDATE 的表上会加上”Access shared locks”锁
3	ROW EXCLUSIVE	Update, Delete, Insert”命令会在目标表上获得这种类型的锁，并且在其它被引用的表上加上”Access shared”锁,一般地，更改表数据的命令都将在这张表上获得”Row exclusive”锁
4	SHARE UPDATE EXCLUSIVE	Vacuum(without full), Analyze ”和 “Create index concurrently”命令会获得这种类型锁
5	SHARE	Create index”命令会获得这种锁模式
6	SHARE ROW EXCLUSIVE	任何Postgresql 命令不会自动获得这种锁
7	EXCLUSIVE	任何Postgresql 命令不会自动获得这种类型的锁
8	ACCESS EXCLUSIVE	ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL” 命令会获得这种类型锁，在Lock table 命令中，如果没有申明其它模式，它也是默认模式

三、各种锁block关系

一旦获得，通常会持有一个锁，直到transaction结束。但是，如果在建立savepoint后获得了锁，则如果将savepoint回滚到该锁，则会立即释放该锁。这符合以下原则：ROLLBACK取消自savepoint以来的所有命令。PL / pgSQL异常块中获得的锁也是如此：从该块进行的错误转义将释放在其中获取的锁。

Once acquired, a lock is normally held till end of transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.

Requested Lock Mode	Current Lock Mode
Requested Lock Mode	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

四、实验

测试常见的锁类型相互block的情况。

制造数据，可以使用gp自带的generate_series存储过程生成。，语法为 insert into select generate_series...

create table locktest(id int,cname varchar(50),remark text);
insert into locktest select generate_series(1,10000),'wx good boy',md5('wx good boy');

4.1 实验：Access exclusive 锁与Access share锁相互block 。

场景一

begin代表开启一个事务，需要手动提交。

先在locktest上加Access share锁。

qmstst=# begin;
BEGIN
qmstst=# select * from locktest;
  id   |    cname    |              remark              
-------+-------------+----------------------------------
     3 | wx good boy | ac40ed9ead3cc7af7148f17c2c625b0e
     5 | wx good boy | ac40ed9ead3cc7af7148f17c2c625b0e
     7 | wx good boy | ac40ed9ead3cc7af7148f17c2c625b0e
qmstst=# end;
COMMIT
qmstst=#

另外一个窗口对locktest做truncate 操作，上一个操作完成后truncate马上完成

qmstst=# truncate table locktest;
TRUNCATE TABLE
Time: 2776107.982 ms

结论： Access share 会block Access Exculsive锁

场景二

在事务中先进行truncate操作，相当于给table上了一把独占锁，Access Exclusice锁，这个锁会block其他所有操作。

qmstst=# begin;
BEGIN
Time: 0.918 ms
qmstst=# truncate table locktest;
TRUNCATE TABLE
Time: 14.306 ms
qmstst=# end;
COMMIT
Time: 9.241 ms
qmstst=#

可以看到select 确实被block了。

qmstst=# select * from locktest;
 id | cname | remark 
----+-------+--------
(0 rows)

Time: 99918.776 ms

在GP4中哪个sql block了哪个sql？在GPCC中是可以看到的。

通过GPCC可以看到是谁block了谁，什么类型的锁？

4.2 Share 锁与 Row Exclusive 锁冲突

在数据库的维护过程中，创建索引也是经常做的工作，别小看创建索引，如果是一个很繁忙的系统，索引不一定能创建得上，可能会发生等侍, 严重时造成系统故障；

Update, Delete, Insert 会获取RowExclusiveLock

qmstst=# select * from locktest;
 id |   cname   |              remark              
----+-----------+----------------------------------
  4 | lock test | 7cf8555c01940f5373c5998954e68bd1
  6 | lock test | 7cf8555c01940f5373c5998954e68bd1
  2 | lock test | 7cf8555c01940f5373c5998954e68bd1
  1 | lock test | 7cf8555c01940f5373c5998954e68bd1
  3 | lock test | 7cf8555c01940f5373c5998954e68bd1
  5 | lock test | 7cf8555c01940f5373c5998954e68bd1
  7 | lock test | 7cf8555c01940f5373c5998954e68bd1
  9 | lock test | 7cf8555c01940f5373c5998954e68bd1
  8 | lock test | 7cf8555c01940f5373c5998954e68bd1
 10 | lock test | 7cf8555c01940f5373c5998954e68bd1
(10 rows)

Time: 6.707 ms
qmstst=# 
qmstst=# begin;
BEGIN
Time: 0.789 ms
qmstst=# insert into locktest(id,cname,remark) values('11','loookte test',md5('loookte test'));
INSERT 0 1
Time: 3.585 ms
qmstst=# end; （先不着急提交）

通过pg_locks查看lock信息

qmstst=# select locktype,database,relation,pid,mode from pg_locks;
   locktype    | database | relation |  pid  |       mode       
---------------+----------+----------+-------+------------------
 relation      |    17149 | 53443799 | 32661 | ShareLock
 transactionid |          |          | 28957 | ExclusiveLock
 relation      |    17149 |    10333 | 28957 | AccessShareLock
 relation      |    17149 | 53443799 | 32570 | RowExclusiveLock

qmstst=# select relname,oid from pg_class where oid = 53443799;
 relname  |   oid    
----------+----------
 locktest | 53443799
(1 row)

你可以自己两个表join查一次就能得到结果。也可以使用pg的内置数据类型查看

qmstst=# select 53443799::regclass;
 regclass 
----------
 locktest
(1 row)

’Create Index’ 命令需要获取Share 锁模式。当不提交insert 事务的时候，alter table一直被block。

qmstst=#  create unique index locktest_id_inx  on locktest (id);
CREATE INDEX
Time: 436904.083 ms

事实上这两个也是相互block，需要注意注意的是，在alter table时候如果某些insert 要求低延迟，一定要注意此问题。

4.3 update和delete在同一张表上是否可以并发？

psql1 上进行delete操作。

qmstst=# begin;
BEGIN
Time: 67.224 ms
qmstst=# 
qmstst=# delete from locktest where id=15;
DELETE 0
Time: 2.786 ms

psql2 上进行update操作，并非同一行数据。

Timing is on.
qmstst=# begin;
BEGIN
Time: 61.623 ms
qmstst=# update locktest set cname = '99999' where id = '13';
UPDATE 0
Time: 222093.301 ms  被阻塞了很久，直到psql1 的事务结束。

结论：GP4 环境下对于同一堆表不支持并发的update 和delete。

在GP4 并不存在全局死锁检查gp_enable_global_deadlock_detector 这个参数，并且每个delete/update 操作都会对表加上一个Exclusive锁。这在GP6有了很大改善。

 ~]$ gpconfig --show gp_enable_global_deadlock_detector 
20200609:10:21:33:018265 gpconfig:P1QMSTST01:gpadmin-[ERROR]:-Failed to retrieve GUC information, guc does not exist: gp_enable_global_deadlock_detector
'NoneType' object is not iterable

GP4上的update 锁类型如下:

分析: 可以观察到master上有三个ExclusiveLock. 这对于的三个update，最老的update事务没有结束，其余两个update事务会一直被block，直到timeout.

GP6上的update 锁类型如下:

分析: 可以观察到master上有一个RowExclusiveLock。 segment上有一个RowExclusiveLock. 这个锁粒度到底有多大？大家可以参考上面给出的锁类型.

在GP6中是支持并发update的，表上的锁信息始终保持两个lock 一个是master的一个是segment上的. 再反观GP4 每增加一个update事务就增加一个ExclusiveLock，因此GP4是不支持并发update的.

4.4 基于postgresql 9.4 模拟dead lock

在模拟的时候一定要注意死锁产生的四个条件，刚开始模拟的时候还不能领会其原理导致不能重现死锁。

导致死锁产生的条件就是如下图:

1、process1 持有了A lock

2、process2 持有了B lock

3、process1同时请求B lock

4、process2同时请求A lock

此时死锁就产生了

psql1

qmstst=# begin;
BEGIN
Time: 0.210 ms
qmstst=# update locktest set cname='99999' where id=3;
UPDATE 1
Time: 1.702 ms

psq2

qmstst=# begin;
BEGIN
Time: 0.203 ms
qmstst=# update  locktest set cname = '888888' where id=4;
UPDATE 1
Time: 2.285 ms
qmstst=# update  locktest set cname = '888888' where id=3;
UPDATE 1
Time: 6986.422 ms

然后再回到psql1

qmstst=# update locktest set cname='99999' where id=4;
ERROR:  deadlock detected  (seg0 10.50.10.170:6000 pid=24516)
DETAIL:  Process 24516 waits for ShareLock on transaction 3127071; blocked by process 25819.
Process 25819 waits for ShareLock on transaction 3127066; blocked by process 24516.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "locktest"
Time: 1002.614 ms

这时死锁发生了。两个update事务相互等待对方持有的lock，最后一个update就会报dead lock，需要注意的是仅有最后触发dead lock的事务会失败(年轻的事务失败这也是情理之中，毕竟有比你老的事务在运行着)，在psql2中更新id=3 的那个事务并不会影响。对应上图的第四步。

在GP4中尽管update是表锁，一般不会出现dead lock。但是在vacuum/analyze操作的时候发生dead lock的机会还是很大的。

下面是在正式环境发现的一段dead lock报错

ERROR:  deadlock detected
DETAIL:  Process 46199 waits for ShareUpdateExclusiveLock on relation 465909 of database 17149; blocked by process 7768.
Process 7768 waits for ExclusiveLock on relation 1296434658 of database 17149; blocked by process 46199.

查看到两个relation 分别是

qmstst=# select 465909 ::regclass;
      regclass       
---------------------
 wpp_adefect_panel_f
(1 row)

Time: 1.165 ms
qmstst=# select 1296434658::regclass;
             regclass              
-----------------------------------
 wpp_adefect_panel_f_1_prt_p202005
(1 row)

Time: 1.152 ms

ShareUpdateExclusiveLock 的获取显而易见，但是分区表上的Exclusive无法确定其来源。(在GP6的版本上官方文档给出的说明是: GP6获取该lock的几率变得很小.官方文档描述见4.5节)

后来查询log发现每周六做vacuum、analyze的时候总会发生deadlock的情况。看起来对整个表做vacuum、analyze还是有风险的，上上策为对单独分区做analyze、vacuum full。这样不好吗？

通过以下sql查询deadLock的结果。

select logtime,logsessiontime, loguser,loghost,logmessage,logdebug,logfile,logstack from log_alert_history where
1=1 and logmessage  like '%deadlock%' order by logtime desc

再注意看 04/21 02:20:11 那两个insert into。两个RowExclusiveLock 也会发生dead lock，通过这个再次加深一下对dead lock的理解。哈哈dead lock真是防不胜防啊。

Process 774 waits for RowExclusiveLock on relation 691206 of database 17149; blocked by process 25233.
Process 25233 waits for RowExclusiveLock on relation 691235 of database 17149; blocked by process 774.
Process 774 waits for RowExclusiveLock on relation 691206 of database 17149; blocked by process 17913.
Process 17913 waits for RowExclusiveLock on relation 691235 of database 17149; blocked by process 774.

4.5 什么时候会获取exclusive lock

postgresql 8.4官方是这么说的：

Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

This lock mode is not automatically acquired on user tables by any PostgreSQL command. However it is acquired on certain system catalogs in some operations.

postgresql 9.4官方是这么说的：

Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.

可以看到9.4获得该锁的情况变得明确了。事实上，在8.4 我发现DML甚至会莫名其妙获取这个Exclusive锁，这是我们不希望看到的，这一点在9.4 有了很大提升。

五、总结

事出必有因，那回到最初的问题，为什么查这两个view会block truncate呢？

gp_skew_coefficients
gp_skew_idle_fractions。

因为gp_skew_coefficients是嵌套了好几层还有一些存储过程，具体调用关系没有细看，我猜是因为该view的某个过程对需要truncate的表加了Access share锁，因此到了truncate一直被阻塞，最终大量报警，cancel 那个block的sql问题就解决了。

排查数据倾斜任重道远。

drop、truncate 原理：

对于PostgreSQL数据库来说每个表都是由一个或几个文件组成的，文件由一个数据组成，如名称为：123468，文件大小不能超过一个设定值，目前是1G，如果表的内容超过了1G,1g内容写到下一个加了”.数字"的文件中，如123468.1，当123468.1写满了，再放到123468.2文件中，依此类推。当greenplum在truncate table或drop table时，会把表对应的文件删除，删除的方式是，先删除123468这个文件，然后遍历这个目录下的所有文件，看这个目录下的每一个文件名前面是否是123468.n的格式（n为1,2,3...)，如果是，再把这个文件删除。随着greenplum数据库的变大，数据目录下有几十万个文件甚至到达百万个文件，于是遍历目录就会很慢。

参考：https://www.postgresql.org/docs/8.4/explicit-locking.html