Postgresql学习笔记之——逻辑结构管理之事务、锁

最新推荐文章于 2023-06-29 20:57:08 发布
最新推荐文章于 2023-06-29 20:57:08 发布
阅读量573 收藏 1
点赞数
文章标签： postgresql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_32838955/article/details/104894349
版权
事务
一、事务特性ACID

关系型数据库中，相关数据操作被称之为事务，事务有四种特性简称为 “ACID“ ，分别为：

1、A (Atomicity) 原子性

原子性很容易理解，也就是说事务里的所有操作要么全部做完，要么都不做，事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。

比如银行转账，从A账户转100元至B账户，分为两个步骤：1）从A账户取100元；2）存入100元至B账户。这两步要么一起完成，要么一起不完成，如果只完成第一步，第二步失败，钱会莫名其妙少了100元。

2、C (Consistency) 一致性

一致性也比较容易理解，也就是说数据库要一直处于一致的状态，事务的运行不会改变数据库原本的一致性约束。

例如现有完整性约束a+b=10，如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a+b=10，否则事务失败。

3、I (Isolation) 隔离性

所谓的独立性是指并发的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。

比如现在有个交易是从A账户转100元至B账户，在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的100元的。

4、D (Durability) 持久性

持久性是指一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。

在Postgresql中，可使用多版本并发控制（MVCC）来维护数据的以执行，相比于锁定模型，多版本并发控制的主要有点在于MVCC里对检索（读）数据的锁请求与写数据的锁请求不冲突，读不会阻塞写，写也不会阻塞读。

二、DDL事务

Postgresql与其他数据库最大的区别是，大多是DDL可以包含在一个事务中，而且也是可以回滚的。由于这个功能，所以Postgresql非常适合作为Sharding的分布式数据系统的底层数据库，比如在SHarding中，需要在多个节点中创建相同的表，这时可以将建表语句放到同一个事务中，这样就可以在各个节点上先启动一个事务，然后执行建表语句了，如果某个节点执行失败，也可以把已经创建表成功的操作进行回滚，这样就不会出现部分节点成功，部分节点失败的情况了。

三、事务的使用

在psql的默认配置下，自动提交 ”AUTOCOMMIT“ 项是打开的，每执行一条SQL语句，都会自动提交，可以手动设置psql中的内置变量 ”AUTOCOMMIT“ 来关闭自动提交：

postgres=# \set AUTOCOMMIT off
postgres-# \echo :AUTOCOMMIT 
off
postgres=# insert into score values (1,98,87,now());
INSERT 0 1
postgres=# insert into score values (2,89,77,now());
INSERT 0 1
postgres=# insert into score values (3,91,80,now());
INSERT 0 1
postgres=# select * from score ;
 student_no | chinese_score | math_score | test_date  
------------+---------------+------------+------------
          1 |            98 |         87 | 2020-03-17
          2 |            89 |         77 | 2020-03-17
          3 |            91 |         80 | 2020-03-17
(3 rows)

postgres=# rollback ;
ROLLBACK
postgres=# select * from score ;
 student_no | chinese_score | math_score | test_date 
------------+---------------+------------+-----------
(0 rows)

另一种方式是通过begin关键词来开启一个事务，然后通过end结束事务，两者之间执行的SQL语句都可以进行回滚。

四、savepoint

Postgresql支持保存点（savepoint）功能，在一个大的事务中，可以把操作过程分成几个部分，第一个部分完成后可以创建一个保存点，若后面的操作执行失败，则可以回滚到这个保存点，而不是整个事务，不影响已经完成的操作。

五、事务隔离级别

数据库的事务隔离级别有以下四种：

READ UNCOMMITED：读未提交
READ COMMITED：读已提交
REPEATABLE READ：重复读
SERIALIZABLE：串行化

对于并发事务，不希望发生不一致的情况，这类情况的级别从高到底的排列如下：

1.脏读：
一个事务读取了另一个未提交事务写入的数据，。这种会导致在并发控制上程序的复杂性。

2.不可重复读：
指一个事务重新读取前面读取过的数据时，发现该数据已经被另一个已经提交的事务修改了。

3.幻读：一个事务开始后，需要根据数据库中现有的数据做一些更新，以时重新执行了一个查询，返回符合查询条件的行，这时发现这些行因为其他最近提交的事务而发生了变化，导致现有的事务如果再进行下去就可能在逻辑上出现错误。

在Postgresql中虽然可以使用命令设置事务的隔离级别，但是实际上只有两种独立的隔离级别分别对应读已提交和可串行化，

读已提交时Postgresql默认的隔离级别，当一个事务运行在整个隔离级别时，select查询只能查询到开始之前已经提交的数据，无法看到未提交的数据或查询执行期间其他事务已提交的数据。不过select可以看到自己事务里面尚未提交的更新结果。

实际上，select查询看到的时在查询开始运行瞬间的一个快照。

六、两阶段提交

Postgresql数据库支持两阶段提交协议。

在分布式系统中，事务往往包含了堕胎数据库上的操作。单台数据库的操作能够保证原子性，但是多台数据库之间的原子性，就需要通过两阶段提交来实现了，两阶段提交时实现分布式事务的关键。

两阶段提交协议有如下五个步骤：

1.应用程序先调用各台数据库做一些操作，但不提交事务，然后通过事务协调器中的提交方法。

2.事务协调器将联络事务中涉及的每台数据库，并通知它们准备提交事务，这是第一阶段的开始。在Postgresql一般调用 ”prepare transaction“ 命令。

3.各台数据库接收到 ”prepare transaction“ 命令后，如果返回成功，数据库必须将自己置于以下状态：确保后续能在被要求提交事务时提交事务，或者在被要求回滚事务时回滚事务。所以PG会将已经准备好提交的信息写入持久存储区中（其实在此时事务信息已经存储，即使重启数据库也不会丢失）。如果数据库无法完成此事务，它会直接返回失败给事务协调器。

4.事务协调器接收到所有数据库的响应。

5.在第二阶段，如果任一数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令（ROLLBACK PREPARED）给各个数据库。如果所有数据库的响应时成功的，则向各台数据库发送 ”COMMIT PREPARED" 命令，通知各台数据库事务成功。

锁机制

在Postgresql中有两类锁：表级锁和行级锁。当要查询、插入、更新、删除表中的数据时，首先要获得表上的锁，然后在获取行上的锁。

一、表级锁模式
锁模式解释
ACCESS SHARE只与ACCESS EXLUSIVE 模式冲突。SELECT命令将在所引用的表上加此类型的锁。任何读取表而不修改表的查询请求都会请求这种锁。
ROW SHARE与EXCLUSIVE 和 ACCESS EXCLUSIVE模式冲突。SELECT FOR UPDATE 和 SELECT FOR SHARE 命令会在目标表上加此类型的锁。
ROW EXCLUSIVE与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE锁模式冲突。UPDATE、DELETE、INSERT 命令会自动在所修改的表上请求加这个锁。通常，修改表中数据的命令都是加此类锁。
SHARE UPDATE EXCLUSIVE与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE锁模式冲突，在模式改变和运行 VACUUM 并发的情况下，这个模式保护一个表。（VACUUM、ANALYZE、CREATE INDEX CONCURRENTLY命令请求此类锁）
SHARE与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 锁模式冲突，这个模式可避免表的并发数据修改。CREATE INDEX（不带CONCURRENTLY选项）语句要求这样的锁模式。
SHARE ROW EXCLUSIVE与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 锁模式冲突。任何Postgresql命令都不会自动请求这个锁模式。
EXCLUSIVE与ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 锁模式冲突，这个模式只允许并发ACCESS SHARE锁。就是说，只有对表的都动作可以和持有这个锁的事务并发执行。任何Postgresql命令都不会在用户表上自动请求这个锁模式，不过在执行某些操作时，会在某些系统上请求这个锁。
ACCESS EXCLUSIVE与所有模式锁冲突，这个模式保证只能有一个人访问此表。ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL命令要求这样的锁。在LOCK TABLE命令中没有明确声明需要的锁模式时，它是默认模式。

表级锁只有 “SHARE" 和 ”EXCLUSIVE“ 这两种锁。这两种锁基本就是读、写锁的意思。

加了 ”SHARE“ 锁后相当于加了读锁，表的内容就不能变化了。可为多个事务加上此锁，只要任意一个人不是放这个读锁，其他人就不能修改这个表。

加了 ”EXCLUSIVE“ 后，则相当于加了写锁。这时别的基础南横既不能写也不能读这个条数据，但是后来数据库加了多版本控制功能。如果某一行的数据被修改，实际上没有修改原来那行数据，而是另外复制了一个新行，修改都在新行上，事务不提交，别人看不到这个新数据。由于旧的那行数据没有变化，在修改过程中，读数据的人仍然可以读取到旧的数据，这样就没必要让别人不能读数据。

若是在多版本功能下，除了以前的 ”SHARE“ 和 ”EXCLUSIVE“ 两种锁外，还需要增加两个锁，一个是 ”ACCESS SHARE“ ，表明加上这个锁，即使正在修改数据的情况下也允许读取数据。另一个锁叫做 ”ACCESS EXCLUSIVE“ ，意思是即使在多版本功能，也不允许访问数据。

表级锁加锁的对象是表，这使得加锁的范围太大，导致并发不高，于是就有了行级锁的概念。但是行级锁与表级锁之间会产生冲突，这时就需要有一种机制来描述行级锁与表级锁之间的关系。MySQL中使用 “意向锁“ 的概念来解决这个问题，方式就是当我们要修改表中的某一行数据时，需要现在表上加一种锁，表示即将在表的部分行上加共享锁或者排他锁。Postgresql也是这样实现的，如 ”ROW SHARE" 和 ”ROW EXCLUSIVE“ 着两个锁，实际就对应MySQL中的共享意向锁（IS）和排它意向锁（IX）。从 ”意向锁“ 的概念出发，可以得到意向锁的下面两个特点：

1.意向锁之间总是不会发生冲突的，即使是 ”ROW EXCLUSIVE“ 之间也不会发生冲突，因为它们都只是 ”有意“ 要做什么，没有真正去做，所以是可以兼容的。

2.意向锁与其他非意向锁之间的关系和普通锁与普通锁之间的关系是相同的。

如果把共享锁 ”SHARE“ 简写为 ”S“ ，排他锁 ”EXCLUSIVE“ 简写为 ”X“ ，把 ”ROW SHARE" 简写为 “IS”，把 “ROW EXCLUSIVE" 简写为 ”IX“，这四种锁之间的关系如下：

意向锁与非以像之间的冲突矩阵

XSIXIS
XNNNN
SNYNY
IXNNYY
ISNYYY

意向排他锁 ”IX“ 它们自己互相之间是不会冲突的，这时可能就需要一种稍严格的锁，就是这种锁自己之间也会冲突，至于和其他锁冲突的情况则与 ”IX“ 相同，这种锁在Postgresql中就叫 ”SHARE UPDATE EXCLUSIVE“ ，不带 FULL 选项的 VACUUM、CREATE INDEX CONCURRENTY命令都会请求这样的锁。

Postgresql还有一种锁，称为 ”SHARE ROW EXCLUSIVE“ ，这种锁可以看成是同时加了 S 锁和 IX 锁的结果。postgresql命令不会自动请求这个锁模式。

总结一下，Postgresql中有8中锁，最普通的是共享锁 ”SHARE“和排他锁”EXCLUSIVE“，因为多版本的原因，修改一条记录同时，允许读取数据，所以为了处理这种情况，又加了两种锁：”ACCESS SHARE" 和 “ACCESS EXCLUSIVE"。此外，为了处理表级锁与行级锁之间的关系有了”意向锁“的概念，这时又加了两种锁：意向共享锁和意向排他锁。由于意向锁之间不会产生冲突，而且意向排他锁之间也不会产生冲突，于是又需要更严格一些的锁，这就产生了 ”SHARE UPDATE EXCLUSIVE" 和 “SHARE ROW EXCLUSIVE" 这两种锁，一共8种锁。

二、行级锁模式

行级锁比较简单，只有两种，即共享锁和排他锁。或者说读锁和写锁。

三、死锁及防范

死锁是指两个或两个以上的事务在执行过程中相互持有对方请求的锁，导致一直持续相互等待对方执行完毕。Postgresql能够自动侦测死锁，然后会推出其中一个事务，从而允许其它事务完成。只是具体哪个事务会推出时无法确定的。

死锁发生具备的必要条件：

1.互斥条件：指事务对所分配到的资源加了排他锁，即在一段时间内只能由一个事务加锁占用，如果此时还有其他进程请求排他锁，则请求者只能等待，直到持有排他锁的事务释放为止。

2.请求和保持条件：指事务已经至少持有了一把排他锁，但又提出了新的排他锁的请求，而新请求资源上的排他锁已经被其他事务所占用，此时请求阻塞，但同时它自己持有的排他锁又保持不放。

3.不剥夺条件：指事务已经获得的锁，在未使用结束前，不能被其他进程剥夺，只能在使用完后有自己释放。

4.环路等待条件：指在发生死锁时，必然存在一个 ”事务-资源“ 的环形链。

防止死锁的最好条件通常是保证所有使用一个数据库的应用都以相同的顺序在多个对象上请求排他锁。虽然数据库可以自动检测排他锁，应用可以通过捕获死锁异常来处理死锁，但是数据库检测死锁是由代价的，有可能会导致应用程序过久地持有排他锁，导致系统并发处理能力下降。

四、表级锁命令 LOCK TABLE

在Postgresql中显示的在表上加锁的命令为 “LOCK TABLE” 命令。语法：

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

lockmode 有以下参数选择:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

解析：

1.lockmode
表级锁的模式

2.NOWAIT
如果没有使用NOWAIT关键字时，当无法获得锁时会一直等待，如果加了此关键字，无法立即获得锁时，此命令会立即退出并发出一个错误信息。

在Postgresql中，事务自己的锁是不会冲突的，因此一个事务可以在持有 “SHARE” 锁时在请求 “ROW EXCLUSIVE” 锁，因为这样并不会阻塞自己。

当事务更新表中的数据时，应该申请 “ROW EXCLUSIVE" 锁，而不应该申请 ”SHARE" 锁，因为事务在更新数据时还是会对表加 “ROW EXCLUSIVE”锁。想象一下，当两个并发事务都请求 “SHARE“ 锁后，在开始更新数据前，对表要加 ”ROW EXCLUSIVE“ 锁，但是由于各自先加了 ”SHARE" 锁，所有都要等待对方释放 “SHARE” 锁，从而会出现死锁。所以可以看出，如果涉及多种锁模式，那么事务应该总是最先申请最严格的锁模式，否则很容易出现死锁。

注意：

LOCK TABLE … IN ACCESS SHARE MODE 需要在目标表上又 SELECT 权限。LOCK TABLE … IN ROW EXCLUSIVE MODE 需要又 INSERT, UPDATE, DELETE,或者 TRUNCATE 权限。所有其他形式的锁都需要表级的更新、删除或截断特权。

五、行级锁命令

显式的行级锁命令由SELECT命令后面加如下子句来完成：


SELECT ...... FRO [UPDATE | SHARE ] [OF table_name [, ...] ] [NOWAIT] [...]

如果在 FOR UPDATE 或 FOR SHARE 中使用 OF table_name 明确指定了表的名字，那么只有这些被指定的表会被锁定，其他的SELECT 中使用的表不会锁定。一个其后不带 OF table_name 的 FOR UPDATE 或 FOR SHARE 子句将锁定该命中所有使用的表。如果 FOR UPDATE 或 FOR SHARE 时应用于一个视图或者子查询，那么它会锁定该视图或子查询中使用到的所有的表。但有一种情况例外，就是主查询中引用了 WITH 查询时，WITH查询中的表不会被锁定。如果向锁定 WITH 查询内的表，需要在 WITH 查询内指定 FOR UPDATE 或 FOR SHARE 关键字。

六、锁的查看

在Postgresql中如果想要查看一个事务产生了那些锁，哪个事务被哪个事务阻塞。比如一条SQL语句执行时被阻塞了，想要知道为什么被阻塞，是哪个事务阻塞等等查询要求，这些都可以通过查询数据库系统视图 pg_locks 来查找，其结构如下：

列名称列类型引用描述
locktypetext被锁定的对象类型：relation、extend、page、tuple、transactionid、virtualxid、object、userlock或advisory
databaseoidpg_database.oid锁定对象之数据库的OID，如果对象是一个共享对象，不属于任何一个数据库，此值为0，如果对象是“transaction ID”，此值为空
relationoidpg_class.oid如果对象不是表或者只是表的一部分，此值为空，否则此值是表的OID
pageinteger表中的页号，如果对象不是表行（tuple）或表页（relation page），则此值为null
tuplesmallint页内的行号（tuple），如果对象不是表行（tuple），则此值为null
virtualxidtext是一个虚拟事务ID（virtual ID of a transaction），如果对象不是虚拟事务，则此值为null
transactionidxid事务ID（ID of a transaction），如果对象不是事务ID，此值为null
classidoidpg_class.oid包含对象的系统目录（system catalog）的OID，如果对象不是通常的数据库对象，则此值为null
objidoidany OID column包含对象的系统目录（system catalog）的OID，如果对象不是通常的数据库对象，则此值为null。对于advisory locks，此字段用于区别两类key空间（1表示int8的key，2表示two int4的key）
objsubidsmallint如果对象是表列（table_column），此列的值为列号，这时 classid 和 objid 指向表，在其他数据库类型中，此值为0，如果不是数据库对象则此值为null
virtualtransactiontext持有或等待这把锁的虚拟事务的ID
pidinteger持有或等待这把锁的服务进程的pid，如果此锁是被一个两阶段提交的事务所持有，则此值为null
modetext锁的模式名称，只要有：ACCESS SHARE 、 ROW SHARE 、 ROW EXCLUSIVE 、 SHARE UPDATE EXCLUSIVE 、 SHARE、 SHARE ROW EXCLUSIVE 、EXCLUSIVE 、ACCESS EXCLUSIVE
grantedboolean如果锁已被持有，此值为true，如果等待获取此锁，此值为false

在上面的表格中，描述事务ID的字段有三个：

virtualxid
transaction
virtualtransaction

解析：
首先 “transactionid” 代表事务ID，简写为 “xid”。“virtualxid” 代表虚拟事务ID，简写为“vxid”。每产生一个事务ID，都会在pg_clog下的commit log文件中占用2bit，最早开始Postgresql中是没有虚拟事务的，但后来返现，有些事务根本没有产生任何实质性的变更，例如只读事务或者一个空事务，这种情况也分配事务ID，会比较浪费数据库资源，于是就提出了虚拟事务ID的概念。对于虚拟事务就不需要再pg_clog下的commit log中占用2bit的空间了。

理解虚拟事务ID 和 事务ID后，再解释以下 “virtualxid” 和 “virtualtransaction” 。字面上都是虚拟事务ID，但是实际上在pg_locks表中，列 “virtualtransaction” 将其分为了两部分：

virtualtransaction字段之前的字段（不包括 virtualtransaction字段）称为第一部分。

virtualtransaction字段之后的字段（包括 virtualtransaction字段）称为第二部分。

第一部分字段用于描述锁定对象（Locked Object）的信息，第二部分描述的是持有锁或者等待锁session的信息。

表级锁示例：

1.开启第一个psql窗口：

[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
            1421 
(1 row)

查询到此窗口链接的服务进程为 615

2.开启第二个psql窗口：

[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
            605
(1 row)

查询到第二个窗口链接服务进程为605

3.此时，在第一个psql窗口1中锁定一张表：

postgres=# begin;
BEGIN
postgres=# lock table employee ;
LOCK TABLE

4.开启第三个psql窗口，运行如下SQL语句查询数据库中锁的情况，如下：

postgres=# select locktype,
relation::regclass as rel,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks where pid=615;
   locktype    |                rel                |  vxid   | xid  |  vxid2  | pid |        mode         | granted 
---------------+-----------------------------------+---------+------+---------+-----+---------------------+---------
 relation      | pg_class_tblspc_relfilenode_index |         |      | 5/11300 | 615 | AccessShareLock     | t
 relation      | pg_class_relname_nsp_index        |         |      | 5/11300 | 615 | AccessShareLock     | t
 relation      | pg_class_oid_index                |         |      | 5/11300 | 615 | AccessShareLock     | t
 relation      | pg_namespace_oid_index            |         |      | 5/11300 | 615 | AccessShareLock     | t
 relation      | pg_namespace_nspname_index        |         |      | 5/11300 | 615 | AccessShareLock     | t
 relation      | pg_namespace                      |         |      | 5/11300 | 615 | AccessShareLock     | t
 relation      | pg_class                          |         |      | 5/11300 | 615 | AccessShareLock     | t
 virtualxid    |                                   | 5/11300 |      | 5/11300 | 615 | ExclusiveLock       | t
 relation      | employee                          |         |      | 5/11300 | 615 | AccessExclusiveLock | t
 transactionid |                                   |         | 1094 | 5/11300 | 615 | ExclusiveLock       | t
(10 rows)

从上面可以看出，执行了一次加锁的命令后，有时在系统表上会产生一些附加的锁。这些系统表上的锁是在系统第一次启动时会看到，第二次运行后就不会看到的。如果从第一个窗口退出，燃火重新进去，重新执行 “LOCK TABLE” 命令，这时就会看到如下的界面了：
窗口1退出后在进入执行锁表操作：

postgres=# \q
[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# begin;
BEGIN
postgres=# lock table employee ;
LOCK TABLE
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           1421
(1 row)

（此时重新进去的进程为1421）

窗口3再次执行查询数据库锁操作：

postgres=# select locktype,
relation::regclass as rel,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks where pid=1421;
   locktype    |   rel    |  vxid   | xid  |  vxid2  | pid  |        mode         | granted 
---------------+----------+---------+------+---------+------+---------------------+---------
 virtualxid    |          | 5/11302 |      | 5/11302 | 1421 | ExclusiveLock       | t
 transactionid |          |         | 1095 | 5/11302 | 1421 | ExclusiveLock       | t
 relation      | employee |         |      | 5/11302 | 1421 | AccessExclusiveLock | t
(3 rows)

（此时查询就不会看到上次系统表加 SHARE 锁情况了！）

倒数第二行显示的事务在自己的 “virtualxid" 上加了 ”ExclusiveLock“ 锁，这是必定需要加上的。

最后一行才是实际在表上加的锁 ”AccessExclusiveLock“。

5.若在窗口2也执行锁表操作，这时窗口2的操作会阻塞：

postgres=# begin;
BEGIN
postgres=# lock table employee ;

6.然后再在窗口3上查询数据库锁情况：

postgres=# select locktype,
relation::regclass as rel,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks where pid in (1421,605);
   locktype    |                rel                |  vxid   | xid  |  vxid2  | pid  |        mode         | granted 
---------------+-----------------------------------+---------+------+---------+------+---------------------+---------
 virtualxid    |                                   | 5/11309 |      | 5/11309 | 1421 | ExclusiveLock       | t
 virtualxid    |                                   | 3/10540 |      | 3/10540 | 605| ExclusiveLock       | t
 relation      | employee                          |         |      | 5/11309 | 1421 | AccessExclusiveLock | t
 transactionid |                                   |         | 1100 | 5/11309 | 1421 | ExclusiveLock       | t
 transactionid |                                   |         | 1101 | 3/10540 | 605| ExclusiveLock       | t
 relation      | employee                          |         |      | 3/10540 | 605| AccessExclusiveLock | f
(20 rows)

从以上可以看出，窗口1（进程1421）和窗口2（605）都对表employee加了锁，窗口1对应的 granted 字段值是 ”t“ ，表明他获得了这个锁，而窗口2的 ”granted“ 字段值为 ”f“ ，表明窗口2进程没有获得锁，被阻塞了。

行级锁示例：

1.窗口1打开psql窗口执行如下操作：

[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           2415
(1 row)

postgres=# select * from employee ;
 id | name  | sex | age | emp_no 
----+-------+-----+-----+--------
  1 | zhang | t   |  23 |    210
  2 | Lee   | f   |  33 |    211
  3 | Smith | t   |  33 |    212
(3 rows)

postgres=# begin;
BEGIN
postgres=# select * from employee where id=1 for update;
 id | name  | sex | age | emp_no 
----+-------+-----+-----+--------
  1 | zhang | t   |  23 |    210
(1 row)

2.打开窗口2查看数据库锁的情况：

postgres=# select locktype,
relation::regclass as rel,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks where pid = 2415;
   locktype    |   rel    |  vxid   | xid  |  vxid2  | pid  |     mode      | granted 
---------------+----------+---------+------+---------+------+---------------+---------
 relation      | employee |         |      | 3/10548 | 2415 | RowShareLock  | t
 virtualxid    |          | 3/10548 |      | 3/10548 | 2415 | ExclusiveLock | t
 transactionid |          |         | 1103 | 3/10548 | 2415 | ExclusiveLock | t
(3 rows)

从前面理论看出，加行锁的过程，会先在表上加一个表级意向锁 ”RowShareLock“，但是在此查询结果中并没有看到行锁，实际上Postgresql中不会记录行锁信息，方式大量行更新时占用太多资源。

想查看行锁信息，需要在视图 pg_locks 中另一种方式来表示这种行锁之间的阻塞关系，例如：
（1）打开窗口3执行窗口2加行锁的操作：

[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           2561
(1 row)

postgres=# begin;
BEGIN
postgres=# select * from employee where id=1 for update;

（但是此处语句执行会被阻塞！）

（2）然后在窗口2中再次查询数据库锁情况：

postgres=# select locktype,
relation::regclass as rel,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks where pid in (2415,2561);
   locktype    |                rel                |  vxid   | xid  |  vxid2  | pid  |        mode         | granted 
---------------+-----------------------------------+---------+------+---------+------+---------------------+---------
 relation      | employee                          |         |      | 3/10548 | 2415 | RowShareLock        | t
 virtualxid    |                                   | 3/10548 |      | 3/10548 | 2415 | ExclusiveLock       | t
 relation      | employee                          |         |      | 5/11344 | 2561 | RowShareLock        | t
 virtualxid    |                                   | 5/11344 |      | 5/11344 | 2561 | ExclusiveLock       | t
 transactionid |                                   |         | 1103 | 3/10548 | 2415 | ExclusiveLock       | t
 transactionid |                                   |         | 1103 | 5/11344 | 2561 | ShareLock           | f
 tuple         | employee                          |         |      | 5/11344 | 2561 | AccessExclusiveLock | t
(14 rows)

前面说过，如果想看哪个进程被阻塞了，只需要查看字段 ”granted“ 字段值为 false 的PID 即可，在上面结果中可以看到倒数第2行，说明窗口3（进程2561）申请了一个 ”transactionid “ 的锁时被阻塞了，这个 ”transactionid “对应的 xid 是 1103，但是 xid 是1103的锁已经被进程 2415，也就是窗口 1持有了，所以窗口3在等待窗口1释放。

因此，行锁的阻塞信息是通过 ”transactionid “ 类型的锁体现出来的，从原理上来说，行锁是会在数据行上加上自己的 xid 的，另一个进程（窗口3）读到这一行时，如果发现行锁，会把行上的另一个事务（窗口1）的xid读出来，然后要申请在这个xid上加 ”Share“ 的锁，而持有行锁的进程（窗口1）已经在xid上加上 ”ExclusiveLock“ 锁。所以后面要更新这行的进程（窗口3）会被阻塞。

若要查询因行锁被阻塞的进程信息，只需查询视图 pg_locks 中类型为 ”transactionid“ 的锁信息就可以了。

查看被锁的行

可以通过查看pg_locks的 ”page“ 和 ”tuple“ 字段来查看。

1.窗口1下执行更新语句：

postgres=# begin;
BEGIN
postgres=# update employee set age = 28 where id=3;
UPDATE 1

2.在窗口3执行同样的更新语句：

postgres=# begin;
BEGIN
postgres=# update  employee set age = 28 where id=3;

（窗口3的操作会被阻塞！）

3.在窗口3查看锁信息（锁查询语句加上字段 page、tuple）：

postgres=# select locktype,
relation::regclass as rel,page,tuple,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks where pid in (2415,2561);
   locktype    |   rel    | page | tuple |  vxid   | xid  |  vxid2  | pid  |       mode       | granted 
---------------+----------+------+-------+---------+------+---------+------+------------------+---------
 relation      | employee |      |       |         |      | 3/10555 | 2415 | RowExclusiveLock | t
 virtualxid    |          |      |       | 3/10555 |      | 3/10555 | 2415 | ExclusiveLock    | t
 relation      | employee |      |       |         |      | 5/11345 | 2561 | RowExclusiveLock | t
 virtualxid    |          |      |       | 5/11345 |      | 5/11345 | 2561 | ExclusiveLock    | t
 transactionid |          |      |       |         | 1105 | 3/10555 | 2415 | ExclusiveLock    | t
 transactionid |          |      |       |         | 1105 | 5/11345 | 2561 | ShareLock        | f
 transactionid |          |      |       |         | 1106 | 5/11345 | 2561 | ExclusiveLock    | t
 tuple         | employee |    0 |     9 |         |      | 5/11345 | 2561 | ExclusiveLock    | t
(8 rows)

事务阻塞后，查看行级锁信息，看到等待锁的进程时2561（窗口3），她所对应的字段 ”page“ 和 ”tuple“ 值分别为0和9。想要查询表中的哪一行，使用如下SQL：

postgres=# select * from employee where ctid = '(0,9)';
 id | name  | sex | age | emp_no 
----+-------+-----+-----+--------
  3 | Smith | t   |  28 |    212
(1 row)

因此得出事务阻塞的行是表employee上id=-3的记录。

Major_ZYH
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
PostgresQL 锁
fanbin
10-14 347
postgresql的锁机制主要有三种，表级锁、行级锁、建议性锁 表级锁和行级锁有显示的和隐式的，建议性锁都是显示的。显示的需要用户显示的查询来获取，隐式的则是由标准sql获取。 页级共享/排除锁，用于控制对共享缓存池里表页的访问，在一行数据被读取或更新后会立即被释放 表级锁 大多数表级锁都可以通过sql命令来获取，也可以通过锁命令获取 access share 访问共享锁，只和access ...
Postgresql学习笔记
01-25
【PostgreSQL学习笔记详解】 PostgreSQL，简称PG，是一款开源的关系型数据库管理系统，以其高度的稳定性和强大的功能受到全球开发者的广泛青睐。本学习笔记旨在为初学者提供一个全面、易懂的PG入门指南，帮助“菜鸟...
PostgreSQL 事务和锁
qq_34409900的博客
03-12 795
1、将数据改变分组到逻辑单元 PostgreSQL 中，这些改变由四个关键语法来控制： BEGIN 开始一个事务 SAVEPOINT savepointname告诉服务器需要记住事务的当前状态。这个语法只能在 BEGIN之后且在 COMMIT 或者 ROLLBACK 之前；也就是说，必须在一个事务中。 COMMIT 表明所有的事务的元素都完成了，现在需要对所有的并行的事务和...
postgresql锁检测及处理(一)---识别事务锁
weixin_43230594的博客
03-18 937
1.模拟deadlock，begin一个事务，update表t1 2.开启另外的session,同样begin一个事务，使用同上的条件update同样的table 这里，发现事务hang住了 3.使用如下sql去检测事务级的deadlock. SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.query AS current_or_recent_statement_in_blocking_process, ka.state AS st
PostgreSQL数据库锁机制——常规锁
肥叔菌的博客
10-21 1048
常规锁也是事务锁，用于协调各种不同事务对相同对象的并发访问。在数据库启动阶段，PG通过InitLocks函数来初始化保存锁对象的共享内存空间，在共享内存中，有两个锁表被用来保存锁对象，分布式主锁表LockMethodLockHash和进程锁表LockMethodProcLockHash。锁表是一个共享内存的哈希表，当一个进程尝试获取一种类型的锁和已经存在的锁冲突时，使用storage/lmgr/proc.c中的函数将该进程置入sleep。 src/backend/storage/lmgr/lock.c *
Oracle vs PostgreSQL，研发注意事项（1）-查询锁表
07-20 203
Oracle数据库，查询语句不会锁表，但PostgreSQL在开启事务后，查询数据表会锁表，在试图DROP/TRUNCATE TABLE时会一直等待。--------------------------...
PostgreSQL学习笔记
10-22
**PostgreSQL学习笔记** PostgreSQL，简称PG，是一款开源的对象关系型数据库管理系统，以其强大的功能、高度的稳定性以及丰富的数据类型而备受开发者喜爱。作为一款成熟的数据库系统，它支持SQL标准，并提供了许多...
Postgresql之体系结构全掌握
08-26
PostgreSQL是一种开源的对象关系型数据库管理系统，其体系结构复杂而精细，旨在提供高效、稳定且功能丰富的数据存储和管理解决方案。以下将详细解析PostgreSQL的主要组成部分及其作用。 1. **核心组件** - **...
PostgreSQL学习笔记中文版
11-20
随着对PostgreSQL的不断学习，对它的了解也在逐步加深，后来发现它的功能还是非常强大的，特别是对GIS空间数据的支持就...想到这里便决定起而行之，花上一段时间好好的学习和研究它，于是就有了这个系列的学习笔记！
PostgreSQL修炼之道从小工到专家高清
07-23
资源名称：PostgreSQL修炼之道 从小工到专家 高清资源截图： 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
PostgreSQL 锁浅析
热门推荐
luojinbai的专栏
05-04 1万+
转载自：http://francs3.blog.163.com/blog/static/40576727201082134343604/ 一、概述　　此文档主要对Postgresql 锁机制进行分析，在讲解的过程中结合实验，理解Postgresql的锁机制。二、表级锁类型　　表级锁类型分为八种，以下对各种表级锁类型进行简单介绍下, 锁的冲突模式可以参考3.1的图一:表级锁冲突模式。2.1 ACCES
PostgreSQL中的锁机制
dsc1245的博客
03-27 984
环境:PG12~15 Centos7.9对于数据库的锁机制, pg12和pg15是完全相同的.随着对PG内部操作的一些了解,很多都会对数据库对象造成锁,但是不同的锁之间有不同的关系,需要系统整理一下.本文依据:PostgreSQL提供了多种lock mode来控制对表中数据的并发访问。在MVCC未提供所需行为的情况下，这些模式可用于应用程序控制的锁定。大多数PostgreSQL命令会自动获取适当模式的锁，以确保在命令执行时不会以不兼容的方式删除或修改引用的表。
Postgresql杂谈 16—Postgresql中的锁机制
Hyman的博客
08-13 6550
今天，我们学习下Postgresql中的锁机制。锁是数据库事务的基础，通过锁才能保证数据库在并发时能够保证数据的安全和一致，才能够达到事务的一致性和隔离性。但是任何事物都有它的两面性，引入锁同样会增加性能开销和可能的死锁问题，不过好在都存在解决方法。Postgresql和其它数据库一样，从锁作用的对象上来说可以分为表锁、行锁两种，从排他性来说可以大体分为共享锁和排他锁，但是由于Postgresql是通过多版本的方式对数据库进行更新，它也引入了Access锁。接下来我们就对这些内容分类进行学习。...
PostgreSQL如何查看事务所占有的锁？
贾欣晓的博客
06-12 8607
PostgreSQL如何查看事务所占有的锁？
大量使用临时表带来的问题，替代方案，以及如何擦屁股
weixin_33974433的博客
06-15 984
以前有使用Greenplum的朋友遇到元表膨胀的问题，总结了原因写了一篇文章如下。 建议先阅读这篇文档，再阅读本文，里面有些原理我在本文就不讲了。 http://blog.163.com/digoal@126/blog/static/1638770402014616113353555 近日，又有使用PostgreSQL的朋友也遇到类似的问题，一些元表...
pgsql与oracle查询死锁和处理死锁
m0_71559454的博客
01-05 1915
今天工作用postgresql数据库，遇到了死锁情况，干脆写个博客记录下查询和处理死锁的语句，以便自己后期工作使用。
PostgreSQL锁详解
最新发布
myneth的博客
06-29 1399
postgresql锁
Hibernate不支持PostgreSQL的for update nowait的解决方法
Free-从心开始
01-10 5275
目前，Hibernate对PostgreSQL的for update nowait语法是不支持的，(Hibernate3.2.4)，由于项目的需要，只好自己来实现。Hibernate在解析SQL文的时候，利用的是Hibernate的Dialect，我们可以通过重载org.hibernate.dialect.PostgreSQLDialect来实现对for update nowait的支持。1.重载
pgsql查看锁表（pg查询锁表并删除）
u013817721的博客
05-05 4123
与” ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE”模式冲突,这种索模式仅能与Access Share 模式并发,换句话说，只有读操作可以和持有”EXCLUSIVE”锁的事务并行；查询命令（Select command）将会在它查询的表上获取”Access Shared” 锁,一般地，任何一个对表上的只读查询操作都将获取这种类型的锁。
postgresql学习笔记
04-28
好的，我可以和你分享一些关于 PostgreSQL 的学习笔记。 1. PostgreSQL 是什么？ PostgreSQL 是一种开源的关系型数据库管理系统，它支持 SQL 语言和 ACID 事务处理，并且具有丰富的特性和扩展性，可用于大型企业...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Major_ZYH

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值