首页 > 数据库 >PostgreSQL锁，第2部分:重量级锁

PostgreSQL锁，第2部分:重量级锁

时间：2023-11-25 19:45:59浏览次数：52  
标签：lock PostgreSQL 部分 10997 pid UPDATE locktest 重量级 SELECT

应用程序开发人员和dba的PostgreSQL锁可见性在大多数情况下与重量级锁相关。复杂的数据库锁定操作需要使用来自系统目录的视图进行完整的检测。应该清楚哪个对象被特定的数据库“后端”进程锁定。任何锁的另一个名称是“瓶颈”。为了使数据库操作并行，我们应该将单个瓶颈分解为多个特定于操作的任务。

目录

这是与表级锁相关的三篇博客文章的第二部分。上一篇文章是关于行级锁的，随后的一篇文章评论锁存器保护内部数据库结构。

示例环境

CREATE TABLE locktest (c INT);
INSERT INTO locktest VALUES (1), (2);

Helper视图

为了检查这些锁的不同类型，让我们按照[Bruce Momjian在他的演示中]的建议创建一个助手视图)：

CREATE VIEW lockview AS SELECT pid, virtualtransaction AS vxid, locktype AS lock_type,
mode AS lock_mode, granted,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text                                                                                     END AS xid_lock, relname,
page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view’s locks
pid != pg_backend_pid() AND
-- no need to show self-vxid locks                                                                           virtualtransaction IS DISTINCT FROM virtualxid
-- granted is ordered earlier
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

RowShareLock (rowshare)

许多应用程序使用read-modify-write范例。例如，应用程序从表中获取单个对象字段，修改数据，并将更改保存回数据库。在多用户环境中，不同的用户可以在事务处理过程中修改相同的行。我们可以通过简单的选择得到不一致的数据。为了响应用户的需求，几乎所有SQL数据库都有SELECT…FOR SHARE锁。此特性可防止应用程序实体在提交或回滚锁定事务之前进行数据修改。

例如：

  1. 一个用户在accounts表中存储了多个银行帐户，在bank_clients表中存储了total_amount。
  2. 为了更新total_amount字段，我们应该防止修改与特定银行客户端相关的所有行。
  3. 最好使用单个update语句来计算total_amount并从accounts表中选择它。如果更新需要外部数据或用户的一些操作，则需要几个语句
START TRANSACTION;
SELECT * FROM accounts WHERE client_id = 55 FOR SHARE;
SELECT * FROM bank_clients WHERE client_id=55 FOR UPDATE;
UPDATE bank_clients SET total_amount=38984.33, client_status='gold' WHERE client_id=55;
COMMIT;

SELECT FOR SHARE语句在关系locktest上创建一个“RowShareLock”锁。

下面是用SQL语句创建的完全相同的锁: 00

BEGIN;
LOCK TABLE locktest IN ROW SHARE MODE;

不管查询锁定的行数是多少，都需要一个重量级的RowShareLock。

在下面的示例中，用一个未完成的事务来说明这一点。启动未完成的事务，在第二个数据库连接中选择lockview：

BEGIN;
SELECT * FROM locktest FOR SHARE;
-- In second connection:
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |   lock_mode   | granted | xid_lock | relname  
-------+------+---------------+---------------+---------+----------+----------
 21144 | 3/13 | transactionid | ExclusiveLock | t       | 586      | 
 21144 | 3/13 | relation      | RowShareLock  | t       |          | locktest

RowExclusiveLock (ROW EXCLUSIVE)

修改行的实际查询还需要对表进行重量级锁，每个表一个锁。

下一个示例使用DELETE查询，UPDATE将具有相同的效果。

所有修改表中数据的命令都获得ROW EXCLUSIVE锁。

BEGIN;
DELETE FROM locktest;
-- second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |    lock_mode     | granted | xid_lock | relname  
-------+------+---------------+------------------+---------+----------+----------
 10997 | 3/6  | transactionid | ExclusiveLock    | t       | 589      | 
 10997 | 3/6  | relation      | RowExclusiveLock | t       |          | locktest

这个新锁与前面的FOR SHARE示例不兼容。SELECT * FROM locktest FOR SHARE等待删除事务完成或终止：

postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname,page,tuple FROM lockview;
  pid  | vxid |   lock_type   |    lock_mode     | granted | xid_lock | relname  | page | tuple 
-------+------+---------------+------------------+---------+----------+----------+------+-------
 10997 | 3/6  | transactionid | ExclusiveLock    | t       | 589      |          |      |      
 10997 | 3/6  | relation      | RowExclusiveLock | t       |          | locktest |      |      
 11495 | 5/9  | relation      | RowShareLock     | t       |          | locktest |      |      
 11495 | 5/9  | tuple         | RowShareLock     | t       |          | locktest |    0 |     1
 11495 | 5/9  | transactionid | ShareLock        | f       | 589      |          |      |

修改表内容的查询还会锁定所有索引，即使索引不包含已修改的字段。

-- preparation
CREATE INDEX c_idx2 ON locktest (c);
ALTER TABLE locktest ADD COLUMN c2 INT;
CREATE INDEX c2_idx ON locktest(c2);

-- unfinished example transaction
BEGIN;
UPDATE locktest SET c=3 WHERE c=1;
-- second connection
postgres=# SELECT * FROM lockview;
 pid  |  vxid  | lock_type  |    lock_mode     | granted | xid_lock | relname  | page | tuple | classid | objid | objsubid 
------+--------+------------+------------------+---------+----------+----------+------+-------+---------+-------+----------
 3998 | 3/7844 | virtualxid | ExclusiveLock    | t       | 3/7844   |          |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | c2_idx   |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | c_idx    |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | c_idx2   |      |       |         |       |         
 3998 | 3/7844 | relation   | RowExclusiveLock | t       |          | locktest |      |       |         |       |

ShareLock (SHARE)

CREATE INDEX的非并发版本可以防止使用ShareLock进行表更新，例如DROP table或INSERT或DELETE。

BEGIN;
CREATE INDEX c_idx ON locktest (c);
-- second connection
postgres=# SELECT * FROM lockview;
 pid  |  vxid  |   lock_type   |      lock_mode      | granted | xid_lock | relname  | page | tuple | classid | objid | objsubid 
------+--------+---------------+---------------------+---------+----------+----------+------+-------+---------+-------+----------
 3998 | 3/7835 | virtualxid    | ExclusiveLock       | t       | 3/7835   |          |      |       |         |       |         
 3998 | 3/7835 | transactionid | ExclusiveLock       | t       | 564      |          |      |       |         |       |         
 3998 | 3/7835 | relation      | AccessExclusiveLock | t       |          |          |      |       |         |       |         
 3998 | 3/7835 | relation      | ShareLock           | t       |          | locktest |      |       |         |       |

您可以并行执行多个CREATE INDEX查询，除非索引名称完全相同。等待发生在pg_class表中的行锁(具有" transactionid "类型的ShareLock)上。

注意，还有类型为“relation”的AccessExclusiveLock锁，但它不是表级别的锁。

ShareUpdateExclusiveLock (SHARE UPDATE EXCLUSIVE)

这些数据库维护操作需要带ShareUpdateExclusiveLock：

  • ANALYZE table
  • VACUUM (without full) runs.
  • CREATE INDEX CONCURRENTLY

ANALYZE表名，语句更新表统计信息。只有统计信息是最新的，查询计划器/优化器才能为查询执行提供最佳计划。

BEGIN;
ANALYZE locktest;
-- in second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |        lock_mode         | granted | xid_lock | relname  
-------+------+---------------+--------------------------+---------+----------+----------
 10997 | 3/7  | transactionid | ExclusiveLock            | t       | 591      | 
 10997 | 3/7  | relation      | ShareUpdateExclusiveLock | t       |          | locktest

RowExclusiveLock和ShareUpdateExclusiveLock不冲突。UPDATE/DELETE/INSERT仍然可以在ANALYZE期间修改行。

VACUUM和CREATE INDEX concurrent只能在事务外部执行。要在lockview中查看这些语句的效果，首先执行一个冲突事务，例如在事务中运行ANALYZE，或者对一个大表运行VACUUM。

CREATE INDEX concurrent锁定可能会令人困惑。SHARE UPDATE EXCLUSIVE锁与用于DELETE、INSERT和UPDATE的ROW EXCLUSIVE锁不冲突。不幸的是，由于全表扫描，CREATE INDEX concurrent会等待活动事务完成两次:

在并发索引构建中，索引实际上在一个事务中被输入到系统目录中，然后在另外两个事务中进行两次表扫描。在每次扫描表之前，索引构建必须等待已修改表的现有事务终止。 PostgreSQL文档

AccessExclusiveLock (ACCESS EXCLUSIVE)

此锁与任何其他锁冲突，并由以下语句使用：

  • CREATE RULE
  • DROP TABLE
  • DROP INDEX
  • TRUNCATE
  • VACUUM FULL
  • LOCK TABLE (default mode)
  • CLUSTER
  • REINDEX
  • REFRESH MATERIALIZED VIEW (without CONCURRENTLY)
BEGIN;
CREATE RULE r_locktest AS ON INSERT TO locktest DO INSTEAD NOTHING;
-- second connection
postgres=# select pid,vxid,lock_type,lock_mode,granted,xid_lock,relname from lockview;
  pid  | vxid |   lock_type   |      lock_mode      | granted | xid_lock | relname  
-------+------+---------------+---------------------+---------+----------+----------
 10997 | 3/19 | transactionid | ExclusiveLock       | t       | 596      | 
 10997 | 3/19 | relation      | AccessExclusiveLock | t       |          | locktest

更重要的是，drop index需要表和索引的访问独占锁：

BEGIN;
DROP INDEX c_idx;
-- second connection
postgres=# SELECT * FROM lockview;
 pid  |  vxid  |   lock_type   |      lock_mode      | granted | xid_lock | relname  | page | tuple | classid | objid | objsubid 
------+--------+---------------+---------------------+---------+----------+----------+------+-------+---------+-------+----------
 3998 | 3/7839 | virtualxid    | ExclusiveLock       | t       | 3/7839   |          |      |       |         |       |         
 3998 | 3/7839 | transactionid | ExclusiveLock       | t       | 569      |          |      |       |         |       |         
 3998 | 3/7839 | relation      | AccessExclusiveLock | t       |          | c_idx    |      |       |         |       |         
 3998 | 3/7839 | relation      | AccessExclusiveLock | t       |          | locktest |      |       |         |       |

注意：这是最危险的一种锁。避免在生产环境中运行需要访问独占锁的查询，或者至少将应用程序置于维护模式。

ExclusiveLock

同时，SQL命令不使用ExclusiveLock，除非使用一般的LOCK TABLE语句。这个锁可以阻止除非锁定选择(即没有FORSHARE/UPDATE)以外的所有请求。

BEGIN;
LOCK TABLE locktest IN EXCLUSIVE MODE;
-- second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid | lock_type |   lock_mode   | granted | xid_lock | relname  
-------+------+-----------+---------------+---------+----------+----------
 10997 | 3/21 | relation  | ExclusiveLock | t       |          | locktest

Savepoints

Savepoint生成一个transactionid类型的额外ExclusiveLock，并带有新的xid值。

BEGIN;
SELECT * FROM locktest FOR SHARE;
SAVEPOINT s1;
SELECT * FROM locktest FOR UPDATE;
-- second connection
postgres=# SELECT pid,vxid,lock_type,lock_mode,granted,xid_lock,relname FROM lockview;
  pid  | vxid |   lock_type   |    lock_mode    | granted | xid_lock | relname  
-------+------+---------------+-----------------+---------+----------+----------
 10997 | 3/37 | transactionid | ExclusiveLock   | t       | 602      | 
 10997 | 3/37 | transactionid | ExclusiveLock   | t       | 603      | 
 10997 | 3/37 | relation      | AccessShareLock | t       |          | c_idx
 10997 | 3/37 | relation      | RowShareLock    | t       |          | locktest

pg_advisory_lock

有时应用程序开发人员需要进程之间的同步。在这样的系统中，应用程序频繁地创建和删除锁。使用基于行的锁实现的系统往往会导致表膨胀。

有许多与咨询锁相关的功能：

  • -每个会话或每个事务
  • 如果lock不可用，则等待或立即返回false
  • 独占或共享
  • 64位或两个32位整数资源标识符

假设我们有几个cron作业，并且应用程序应该防止同时运行同一个脚本。接下来，每个脚本可以检查特定整数作业标识符的锁在PostgreSQL中是否可用：

postgres=# SELECT pg_try_advisory_lock(10);
 pg_try_advisory_lock 
----------------------
 t
-- second connection
postgres=# SELECT * FROM lockview;
 pid  | vxid | lock_type |   lock_mode   | granted | xid_lock | relname | page | tuple | classid | objid | objsubid 
------+------+-----------+---------------+---------+----------+---------+------+-------+---------+-------+----------
 3998 | 3/0  | advisory  | ExclusiveLock | t       |          |         |      |       |       0 |    10 |        1
-- other connections
SELECT pg_try_advisory_lock(10);
 pg_try_advisory_lock 
----------------------
 f

查询生成带有类型咨询的ExclusiveLock。

Deadlocks

当查询永远无法完成时，任何具有多个锁的系统都可能出现死锁情况。解决这类问题的唯一方法是:终止一个阻塞的语句。更重要的是，死锁检测在PostgreSQL中是一个昂贵的过程。只有当事务锁定deadlock_timeout毫秒(默认情况下是一秒后)时才会发生死锁检查。

下面是两个不同连接A和B的死锁情况：

从锁等待开始的任何死锁。

A: BEGIN; SELECT c FROM locktest WHERE c=1 FOR UPDATE;
B: BEGIN; SELECT c FROM locktest WHERE c=2 FOR UPDATE; SELECT c FROM locktest WHERE c=1 FOR UPDATE;

你并不是唯一一个识别死锁的人，因为pg_stat_activity系统视图可以帮助你找到导致锁等待的语句和事务:

postgres=# SELECT pg_stat_activity.pid AS pid,
query, wait_event, vxid, lock_type,
lock_mode, granted, xid_lock
FROM lockview JOIN pg_stat_activity ON (lockview.pid = pg_stat_activity.pid);
  pid  |          query             |  wait_event   | vxid |   lock_type   |      lock_mode      | granted | xid_lock 
-------+----------------------------+---------------+------+---------------+---------------------+---------+----------
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | transactionid | ExclusiveLock       | t       | 605
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | advisory      | ExclusiveLock       | t       | 
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | relation      | AccessShareLock     | t       | 
 10997 | SELECT ... c=1 FOR UPDATE; | ClientRead    | 3/43 | relation      | RowShareLock        | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | transactionid | ExclusiveLock       | t       | 606
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | advisory      | ExclusiveLock       | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | relation      | AccessShareLock     | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | relation      | RowShareLock        | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | tuple         | AccessExclusiveLock | t       | 
 11495 | SELECT ... c=1 FOR UPDATE; | transactionid | 5/29 | transactionid | ShareLock           | f       | 605

对c=2行的SELECT FOR UPDATE会导致死锁：

SELECT c FROM locktest WHERE c=2 FOR UPDATE;

之后，PostgreSQL在服务器日志中报告：

2018-08-02 08:46:07.793 UTC [10997] ERROR:  deadlock detected
2018-08-02 08:46:07.793 UTC [10997] DETAIL:  Process 10997 waits for ShareLock on transaction 606; blocked by process 11495.
Process 11495 waits for ShareLock on transaction 605; blocked by process 10997.
Process 10997: select c from locktest where c=2 for update;
Process 11495: select c from locktest where c=1 for update;
2018-08-02 08:46:07.793 UTC [10997] HINT:  See server log for query details.
2018-08-02 08:46:07.793 UTC [10997] CONTEXT:  while locking tuple (0,3) in relation "locktest"
2018-08-02 08:46:07.793 UTC [10997] STATEMENT:  SELECT c FROM locktest WHERE c=2 FOR UPDATE;
ERROR:  deadlock detected
DETAIL:  Process 10997 waits for ShareLock on transaction 606; blocked by process 11495.
Process 11495 waits for ShareLock on transaction 605; blocked by process 10997.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,3) in relation "locktest"

如您所见，数据库服务器会自动中止一个阻塞的事务。

Multi-way deadlocks

通常只有两个事务会产生死锁。但是，在复杂的情况下，应用程序可能会因为多个事务形成一个依赖关系圈而导致死锁。

A：锁第一行，B：锁第2行，C：锁第3行

下一步：

A：试图得到第3行，B：试图得到第1行，C：试图得到第2行

总结

  • 不要把DDL语句放在长事务中。
  • 对于频繁更新的表，请避免在高负载时打开DDL。
  • CLUSTER命令要求独占访问表及其所有索引。
  • 监控postgresql日志中与死锁相关的消息。

标签：lock,PostgreSQL,部分,10997,pid,UPDATE,locktest,重量级,SELECT
From： https://www.cnblogs.com/jl1771/p/17855949.html

相关文章

  • 为PostgreSQL优化调整Linux内核参数
    为了获得最佳性能，PostgreSQL数据库依赖于正确定义的操作系统参数。配置不当的操作系统内核参数可能导致数据库服务器性能下降。因此，必须根据数据库服务器及其工作负载来配置这些参数。在这篇文章中，我们将讨论一些可能影响数据库服务器性能的重要Linux内核参数，以及如何调优这些参数......
  • PostgreSQL中的复制延迟
    PostgreSQL是一种流行的开源关系数据库管理系统，广泛用于存储和管理数据。PostgreSQL中可能遇到的常见问题之一是复制滞后。在这篇博客中，我们将讨论什么是复制延迟、它发生的原因以及如何在PostgreSQL中缓解它。什么是复制滞后？复制延迟是指数据写入主数据库的时间与复制到备......
  • cryptohack RSA部分
    1.Salty：此题为rsa加密中e=1的情况，由于\(ed(modphi)=1\)，所以d自然是等于1的，不用分解n就解出了dfromCrypto.Util.numberimportlong_to_bytese=1k=1ct=44981230718212183604274785925793145442655465025264554046028251311164494127485n=110581795715958566206600392161360......
  • Ubuntu20.04 安装后部分问题解决方案
    安装搜狗输入法搜狗官方有教程：https://shurufa.sogou.com/linux/guideUbuntu与Windows时间不一致的问题安装ntpdate：sudoapt-getinstallntpdate校准时间：sudontpdatetime.windows.com将时间更新到硬件上：sudohwclock--localtime--systohc单击任务栏图标使窗......
  • postgresql从入门到精通 - 第35讲：中间件PgBouncer部署|PostgreSQL教程
     PostgreSQL从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容，希望对热爱PG、学习PG的同学们有帮助，欢迎持续关注CUUGPG技术大讲堂。 第35讲：中间件PgBouncer部署11月25日(周六)19:30-20:30，往期......
  • 印度程序员指针部分部分代码
    #include"stdio.h"intmain(){ intx=5; int*p=&x; *p=6;//可以不改变x的值来修改输出 int*(*q)=&p;//即p=*q int*(*(*r))=&q;//即r=*p printf("%d\n",*p); printf("%d\n",*q); printf("%d\n",**q);//即*p pr......
  • 直播平台源码，vue图片中划框截取部分图片
    直播平台源码，vue图片中划框截取部分图片 <template> <div>  <el-dialogtitle="请在图片上画出需要新增故障的位置":visible.sync="dialogVisible"width="1270px":before-close="handleClose":close-on-click-modal="false":close......
  • JWT生成的token——中间部分Payload的坑
     JWT进行token认证应该都用过，标准的加密（Base64编码）后的token是这样的三段式的：eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwiaWQiOiIxMjM0NTY3ODkwIiwiZXhwIjoxNjA1MDAxNzQyLCJpYXQiOjE2MDQ5OTQ1NDIsImp0aSI6IjU5YjI2NDEzLTE4MjMtNDVlZS1iZTI1LTA5M2ZjMjlhMmYzOCJ9.FMpVju......
  • eBPF 概述：第 2 部分：机器和字节码
    1.前言我们在第1篇文章中介绍了eBPF虚拟机，包括其有意的设计限制以及如何从用户空间进程中进行交互。如果你还没有读过这篇文章，建议你在继续之前读一下，因为没有适当的介绍，直接开始接触机器和字节码的细节是比较困难的。如果有疑问，请看第1部分开头的流程图。本系列的第2......
  • postgresql中执行select查询语句卡住问题的处理
     1、问题描述 研发环境搭建的分布式的数据库Tbase。Tbase本质上是基于postgresql-xl创建的分布式数据库。昨天，研发的同事反馈，这个数据库中，对某个表执行select查询的时候，都是卡住的。客户端一直没有影响。 2、问题分析 我这边通过pg的客户端进行连接，发现除了这个......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99