PostgreSQL中的ROW EXCLUSIVE到底是什么? [英] What is ROW EXCLUSIVE in PostgreSQL exactly?

问题描述

我了解PostgreSQL插入使用ROW EXCLUSIVE锁，这是否意味着插入可以是并行的，并且一个插入不会锁定整个表?

I understand that PostgreSQL inserts use ROW EXCLUSIVE locks, does that mean that inserts can be in parallel, and that one insert won't lock up the entire table?

有问题的表具有在数据库外部生成的主键，并且没有其他索引(但是我很好奇，如果不是这种情况，会发生什么情况.)

Table in question has a primary key generated outside of DB and no additional indexes (but I'm curious what would happen if that wasn't the case).

根据文档，ROW EXCLUSIVE与CREATE INDEX获取的SHARE冲突.

Per documentation, ROW EXCLUSIVE conflicts with SHARE which is acquired by CREATE INDEX.

这是否意味着如果表具有索引，insert将锁定整个表?

Does this mean that if the table has index, insert will lock up the entire table?

还是仅在第一次创建索引时才锁定表?

Or will the table be locked only when creating the index first time?

而且，据我所知，主键也是索引，对吧?

Also, as I understand now, primary key is also an index, right?

推荐答案

并发插入不应互相阻塞，除非它们将冲突的键插入唯一索引中，在这种情况下，第二个插入将等待包含第一个插入的事务提交或回滚，然后中止或继续.主键被实现为唯一索引.

Concurrent inserts should not block each other, unless they are inserting conflicting keys into a unique index, in which case the second insert will wait for the transaction containing the first to be committed or rolled back, and then either abort or proceed. A primary key is implemented as a unique index.

非唯一索引不应导致其他锁定冲突.创建索引将阻止对表的插入和更新，尽管您可以将concurrently添加到命令中来避免这种情况，但是会降低速度.

Non-unique indexes should not cause additional lock conflicts. Creating an index will block inserts and updates to the table, although you can just add concurrently to the command to avoid this, for some speed penalty.

这篇关于PostgreSQL中的ROW EXCLUSIVE到底是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！