postgresql表中间加列_PostgreSQL中的八级锁__锁是实现数据库并发控制必不可少的功能，PostgreSQL数据库通过其特有的多版本属性实现了MVCC，实现了读不阻塞写，写不阻塞读。P...

最新推荐文章于 2023-06-16 15:54:12 发布
最新推荐文章于 2023-06-16 15:54:12 发布
阅读量151 收藏
点赞数
文章标签： postgresql表中间加列
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29231725/article/details/112807718
版权

### **本文转载自公众号《数据库架构之美》**

#### 锁是实现数据库并发控制必不可少的功能，PostgreSQL数据库通过其特有的多版本属性实现了MVCC，实现了读不阻塞写，写不阻塞读。PostgreSQL中表锁有八个级别，不同的锁对应了不同的排他级别。值得注意的是同一时刻两个事务不能再同一个表上获取相互冲突的锁，但是一个事务是永远不会与自己冲突的，一个事务里连续获取两个有冲突的锁类型是没有问题的。

### **表级锁**

#### 先用一张图总结一下八种锁的冲突关系

![CENTER_PostgreSQL_Community]( /images/news/2020/20200708_1444_图片1--2020.7_.8_.png)

#### 下面分别介绍一下这八种锁的场景：

#### 1.AccessShare

#### 在某个表上发出SELECT命令只读取表而不去修改它的查询都会获取该锁类型。

#### 冲突级别：8

#### 2.RowShare

#### SELECT FOR UPDATE/FOR SHARE命令会在目标表上取得一个这种模式的锁。

#### 冲突级别：7,8

#### 3.RowExclusive

#### 在表上发出UPDATE、DELETE和INSERT要修改表中数据时会取得这种锁模式。

#### 冲突级别：5,6,7,8

#### 4.ShareUpdateExclusive

#### 一些在线维护类操作所获得的锁，例如VACUUM(不带FULL)、ANALYZE、CREATE INDEX CONCURRENTLY、CREATE STATISTICS、ALTER TABLE VALIDATE等，该锁类型是自排他的。

#### 冲突级别：4,5,6,7,8

#### 5.Share

#### 发出CREATE INDEX命令(不带CONCURRENTLY)取得该锁，注意该锁不是自排他的。

#### 冲突级别：3,4,6,7,8

#### 6.ShareRowExclusive

#### 在以前老版本的官方文档中该锁不能通过发出某条数据库命令获得，而11以后的版本介绍该锁由CREATE COLLATION、CREATE TRIGGER和某些 ALTER TABLE命令获得。

#### 冲突级别：3,4,5,6,7,8

#### 7.Exclusive

#### 这种锁模式只允许并发的AccessShare锁，持有该锁只允许该表的只读操作。在以前老版本的官方文档中该锁不能通过发出某条数据库命令获得，而11以后的版本介绍该锁由REFRESH MATERIALIZED VIEW CONCURRENTLY获得。

#### 冲突级别：2,3,4,5,6,7,8

#### 8.AccessExclusive

#### 最高级别的锁，与所有模式的锁冲突，该锁保证持有者是访问该表的唯一事务。由DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW(不带CONCURRENTLY)命令获取。ALTER TABLE的某些命令也在会获得这种锁。同时，显式发出LOCK TABLE命令的默认锁模式也是该八级锁。

#### 冲突级别：所有

#### 值得注意的是savepoint之后获得的锁，在回退到保存点之前后该锁也会被事务释放。

### **实验**

#### 下面做几个小实验验证一下锁冲突。

#### 1.加列和查询冲突

#### 会话1：

```

postgres=# begin ;

BEGIN

postgres=# select * from test;

id

----

1

(1 rows)

```

#### 会话2：

```

postgres=# begin;

BEGIN

postgres=# alter table test add column a int;

```

​

#### 查询锁状态：

```

postgres=# select l.locktype,l.relation,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p where l.locktype='relation' and l.pid=p.pid and query not like '%pg_stat_activity%';

locktype | relation | pid | mode | granted | query_start | query | state

----------+----------+--------+---------------------+---------+-------------------------------+------------------------------------+---------------------

relation | 16782 | 500821 | AccessShareLock | t | 2020-06-20 09:42:21.338529+08 | select * from test; | idle in transaction

relation | 16782 | 502255 | AccessExclusiveLock | f | 2020-06-20 09:43:08.922259+08 | alter table test add column a int; | active

(2 rows)

```

#### 2.读写互不阻塞

#### 会话1：

```

postgres=# begin;

BEGIN

postgres=# update test set id=2;

UPDATE 1

会话2：

postgres=# select * from test;

id

----

1

(1 row)

```

#### 查询锁状态：

```

postgres=# select l.locktype,l.relation,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p where l.locktype='relation' and l.pid=p.pid and query not like '%pg_stat_activity%';

locktype | relation | pid | mode | granted | query_start | query | state

----------+----------+--------+------------------+---------+-------------------------------+-----------------------+---------------------

relation | 16782 | 429476 | RowExclusiveLock | t | 2020-06-20 12:35:15.523242+08 | update test set id=2; | idle in transaction

relation | 16782 | 429965 | AccessShareLock | t | 2020-06-20 12:35:26.266669+08 | select * from test; | idle in transaction

(2 rows)

```

#### 3.在线创建索引

```

会话1：

postgres=# begin;

BEGIN

postgres=# select * from test;

id

----

1

(1 row)

```

#### 会话2：

```

postgres=# create index concurrently on test(id);

CREATE INDEX

```

#### 发现直接创建成功了，锁等待视图里面也没有相关信息。

#### 会话1：

```

postgres=# begin;

BEGIN

postgres=# update test set id=2;

UPDATE 1

```

#### 会话2：

```

postgres=# create index concurrently on test(id);

```

​

#### 发现hang了，查看锁视图：

```

postgres=# select l.locktype,l.relation,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p where l.locktype='relation' and l.pid=p.pid and query not like '%pg_stat_activity%';

locktype | relation | pid | mode | granted | query_start | query | state

----------+----------+--------+--------------------------+---------+-------------------------------+----------------------------------------+---------------------

relation | 16782 | 156109 | ShareUpdateExclusiveLock | t | 2020-06-20 13:33:36.050598+08 | create index concurrently on test(id); | active

relation | 16782 | 158346 | RowExclusiveLock | t | 2020-06-20 13:33:31.494708+08 | update test set id=2; | idle in transaction

(2 rows)

```

#### 这里其实原因我上一篇文章专门介绍过，是因为先开启的会话1，造成长事务，引起会话2的创建索引事务等待。如果在一个大表上先直接并发创建索引，再update该表，基本是不会阻塞的(可能阻塞的原因是在创建索引的第二阶段获取快照之前有长事务未结束)。

#### 4.两个字段同时创建索引

#### 会话1：

```

postgres=# begin;

BEGIN

postgres=# create index on test(id);

CREATE INDEX

```

#### 会话2：

```

postgres=# begin;

BEGIN

postgres=# create index on test(a);

CREATE INDEX

```

#### 查询锁状态：

```

postgres=# select l.locktype,l.relation,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p where l.locktype='relation' and l.pid=p.pid and query not like '%pg_stat_activity%' and l.relation=16782;

locktype | relation | pid | mode | granted | query_start | query | state

----------+----------+--------+-----------+---------+-------------------------------+---------------------------+---------------------

relation | 16782 | 156109 | ShareLock | t | 2020-06-20 13:43:10.719273+08 | create index on test(a); | idle in transaction

relation | 16782 | 158346 | ShareLock | t | 2020-06-20 13:42:35.576189+08 | create index on test(id); | idle in transaction

(2 rows)

```

#### 5.在线维护类操作自排他

#### 会话1：

```

postgres=# begin;

BEGIN

postgres=# analyze test;

ANALYZE

```

#### 会话2：

```

postgres=# create index concurrently on test(id);

```

​

#### 查询锁状态：

```

postgres=# select l.locktype,l.relation,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p where l.locktype='relation' and l.pid=p.pid and query not like '%pg_stat_activity%' and l.relation=16782;

locktype | relation | pid | mode | granted | query_start | query | state

----------+----------+--------+--------------------------+---------+-------------------------------+----------------------------------------+---------------------

relation | 16782 | 156109 | ShareUpdateExclusiveLock | f | 2020-06-20 13:56:21.525695+08 | create index concurrently on test(id); | active

relation | 16782 | 158346 | ShareUpdateExclusiveLock | t | 2020-06-20 13:55:24.686202+08 | analyze test; | idle in transaction

(2 rows)

```

![CENTER_PostgreSQL_Community]( /images/news/2020/20200708_1454_图片2--2020.7_.8_.png)

​

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

zibuyu9
关注
postgresql表中间加列_如何数据库表加列
weixin_36397256的博客
01-13 407
X-Engine 如何实现 Fast DDLX-Engine是阿里巴巴自研的存储引擎，作为阿里云 RDS MySQL 的一个可选引擎，除了主打高性能和低成本，还增加了不少惠及用户的新功能。本文将详细介绍 MySQL(X-Engine) 如何近乎瞬时完成传统数据库需要数小时完成的DDL操作。1.数据库DDL操作面临的问题互联网业务发展迅速...文章skin7782020-06-28226浏览量快人一...
postgresql表中间加列_PostgreSQL ALTER TABLE 命令
weixin_28916817的博客
12-30 718
PostgreSQL ALTER TABLE 命令在 PostgreSQL 中，ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。另外你也可以用 ALTER TABLE 命令添加和删除约束。语法用 ALTER TABLE 在一张已存在的表上添加列的语法如下：ALTER TABLE table_name ADD column_name datatype;在一张已存在的表上 DROP...
锁，读写锁；表锁，行级锁（一）
weixin_30265103的博客
03-17 342
（一）什么是读写锁？ ①读锁：也叫共享锁--->互不阻塞。【多线程同步读】 ②写锁：也叫排他锁--->一个写锁会阻塞其他写锁。【单个线程写】 （二）锁粒度 锁策略：锁的开销和数据的安全性之间寻求平衡。 重要的两种锁策略： ①表锁：MySQL中最基本的锁策略，开销最小的策略。 ②行级锁：最大程度上支持并发处理（--->最大的锁开销）。 转载于:https://www....
PostgreSQL（八）锁的相关操作
ACGkaka的博客
06-16 3772
PostgreSQL（八）锁的相关操作
MVCC多版本并发控制（Multi Version Concurrency Control）
qq_36370187的博客
04-08 317
1、快照读和当前读 快照读：读取快照数据，有可能读取到历史版本数据，不加锁的select就是快照读 当前读：加锁的select、
Postgresql - 执行DDL - 锁问题的避免
chuckchen1222的博客
11-18 883
加列，减列很快，几乎没影响 Version 10 alter table test01 add column col11 varchar(100) alter table test01 alter COLUMN col12 set default 'abc'; Version 11 alter table test01 add column col11 varchar(100) default...
pgsql 阻塞问题
zuijiu997的博客
09-21 1363
pgsql 阻塞问题 当pgsql发生阻塞时，极有可能发生锁表了。 可以用一下命令，解决。 查询是否锁表了 select oid from pg_class where relname='可能锁表了的表' select pid from pg_locks where relation='上面查出的oid' 查看当前的进程，query字段显示ddl或dml语句 SELECT * FROM pg_stat_activity where datname=数据库名 查看等待中的进程 select * fro
postgresql表中间加列_好消息！PostgreSQL 13增加新功能！
weixin_39847722的博客
12-22 175
Number one：vacuum速度提高3倍对于PostgreSQL的新手来说，清理是多版本并发系统的一部分，它负责删除每当记录被更改或删除时生成的无效元组或行。autovacuum功能可以清除死掉(已删除)的隐藏行，从而消除表膨胀，类似于Java的垃圾收集器。有两个与vacuum相关的改进：索引的并行清理以及允许对仅追加的数据进行仅索引扫描。首先，有了并行vacuum，Postgres现在可以...
postgresql表中间加列_PostgreSQL unlogged table
weixin_39616003的博客
12-30 295
pg中支持创建unlogged table，顾名思义就是不记录日志的表，这个和oracle创建表时的nologging选项类似。https://www.cndba.cn/foucus/article/3931unlogged table主要有几个特点：1、unlogged table不记录wal日志，写入速度快，备库无数据，只有结构。2、当数据库crash后，数据库重启时自动清空unlogged ...
PostgreSQL日常维护、监控、排错、优化.pdf
03-04
为了提高数据库的性能，优化是必不可少的。PostgreSQL提供了多种优化手段： - **索引优化**：合理使用索引可以极大地提高查询速度。 - **查询优化**：通过优化查询语句结构、使用合适的连接方式等提高查询效率。 - ...
postgresql 日常维护、监控、排错、优化
02-02
### PostgreSQL日常维护、监控、排错、优化 #### 一、SQL审计 ...通过上述知识点的学习和实践，可以有效地提高PostgreSQL数据库系统的稳定性和性能，为企业级应用提供强大的数据支持和服务保障。
postgresql建表带注释_postgresql表和列注释（描述）
weixin_39640543的博客
12-21 1499
PostgreSQL添加表和列注释。本文为测试表test，默认无注释。test=# \d+关联列表架构模式 | 名称 | 类型 | 拥有者 | 大小 | 描述----------+------+--------+----------+---------+------public | test | 数据表 | postgres | 0 bytes |(1 行记录)test=# co...
基于Vue框架的鱿鱼导航前端开发设计源码
10-02
该项目是一款基于Vue框架开发的鱿鱼导航前端设计源码，包含122个文件，涵盖23个JavaScript文件、23个Java文件、10个PNG图片、9个Vue组件、7个CSS样式表、5个XML配置文件、4个JSON数据文件、4个YAML配置文件、4个JPG图片文件和3个Git忽略文件。该项目以HTML和CSS为基础，通过JavaScript和Vue.js实现动态交互和用户界面设计。
安徽大学在江西2020-2024各专业最低录取分数及位次表.pdf
10-02
那些年，与你同分同位次的同学都去了哪里？全国各大学在江西2020-2024年各专业最低录取分数及录取位次数据，高考志愿必备参考数据
2021美赛D题参考翻译（第一版）（PDF文档）
最新发布
10-02
2021美赛D题参考翻译（第一版）（PDF文档） 【音乐影响力的量化模型】
Xshell-8.0.0057p.exe
10-02
Xshell-8.0.0057安装包
CPA 经济法 基础班 第5章 公司法律制度 股份有限公司（8）-PPT-6页.pdf
10-02
CPA 经济法 基础班 第5章 公司法律制度 股份有限公司（8）-PPT-6页.pdf
实例程序原理图加文档基于msp430的智能宿舍
10-02
实例程序原理图加文档基于msp430的智能宿舍
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值