云贝教育 |【PostgreSQL PGCE-091题目解析14】PostgreSQL中使用CONCURRENTLY选项创建索引时，允许增删改数据表。

考试科目：PGCE-E-091

考试题量：44 道单项选择题、29 道多项选择题（每题 2 分）

通过分数：60%

考试时间：60min

本文为云贝教育刘峰（微信：yunbee_DBA）原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

PostgreSQL中使用CONCURRENTLY选项创建索引时，允许增删改数据表。

A. 正确

B. 错误

参考答案：A

解析：

我们知道，PG是有行级琐的，在创建索引的时候，会在行上加琐

实验如下：

1、准备目标数据

drop table if exists t1;
create table t1 ( a int, b varchar(50));
insert into t1
select a.*, md5(a::varchar) from generate_series(1,5000000) a;

2、在表上创建索引，不加CONCURRENTLY

testdb=# create index idx_t1_a3 on t1(a);
CREATE INDEX

3、修改表数据

testdb=# begin;
BEGIN
testdb=*# select pg_backend_pid();
pg_backend_pid
----------------
32600
(1 row)

testdb=*# select txid_current();
txid_current
--------------
759
(1 row)
testdb=*# update t1 set a=55 where a=6;
。。。等待中

发现修改不了

4、查看此时的锁

testdb=# select locktype,database,relation::regclass,transactionid,pid,mode from pg_locks order by pid;
      locktype | database | relation | transactionid |  pid  |   mode
---------------+----------+----------+---------------+-------+---------------------
relation       | 16389    | pg_locks |               | 30854 | AccessShareLock
virtualxid     |          |          |               | 30854 | ExclusiveLock
virtualxid     |          |          |               | 32410 | ExclusiveLock
relation       | 16389    | 16411    |               | 32410 | AccessExclusiveLock
transactionid  |          |          |           760 | 32410 | ExclusiveLock
relation       | 16389    | t1       |               | 32410 | ShareLock
virtualxid     |          |          |               | 32600 | ExclusiveLock
relation       | 16389    | t1       |               | 32600 | RowExclusiveLock
transactionid  |          |          |           759 | 32600 | ExclusiveLock
(9 rows)

5、以CONCURRENTLY模式创建索引

testdb=# create index concurrently idx_t1_a41 on t1(a);
CREATE INDEX

6、修改表数据

testdb=# update t1 set a=55 where a=6;
UPDATE 2

发现很顺利修改成功

7、查看此时的锁情况

testdb=# select locktype,database,relation::regclass,transactionid,pid,mode from pg_locks order by pid;
      locktype | database |  relation  | transactionid |  pid  |  mode
---------------+----------+------------+---------------+-------+--------------------------
virtualxid     |          |            |               | 30854 | ExclusiveLock
relation       |    16389 | pg_locks   |               | 30854 | AccessShareLock
relation       |    16389 | idx_t1_a5  |               | 35304 | RowExclusiveLock
relation       |    16389 | idx_t1_a4  |               | 35304 | RowExclusiveLock
relation       |    16389 | idx_t1_a3  |               | 35304 | RowExclusiveLock
relation       |    16389 | idx_t1_a2  |               | 35304 | RowExclusiveLock
relation       |    16389 | idx_t1_a1  |               | 35304 | RowExclusiveLock
relation       |    16389 | idx_t1_a   |               | 35304 | RowExclusiveLock
relation       |    16389 | t1         |               | 35304 | RowExclusiveLock
relation       |    16389 | idx_t1_a41 |               | 35304 | RowExclusiveLock
transactionid  |          |            |           773 | 35304 | ExclusiveLock
virtualxid     |          |            |               | 35304 | ExclusiveLock
virtualxid     |          |            |               | 35470 | ShareLock
relation       |    16389 | t1         |               | 35470 | ShareUpdateExclusiveLock
virtualxid     |          |            |               | 35470 | ExclusiveLock 

结论：PG在创建索引使用concurrently参数时，不影响DML操作，反而是DML操作会影响concurrently索引创建。