postgresql锁住的简单介绍

postgres怎么在线创建索引

我们知道数据库创建索引可能会锁住创建索引的表，并且用该表上的一次扫描来执行整个索引的构建，这样在创建索引时会影响在线业务，非常大的表创建索引可能会需要几个小时，这样阻塞业务是不被允许的。商业数据库一般都提供在线创建索引的能力，PostgreSQL作为开源数据库，也提供了这样的功能。我们在CREATE INDEX命令中新增CONCURRENTLY选项来实现索引的在线创建。     但是concurrently在线创建索引也并不是那么完美，当使用这个选项时，PostgreSQL必须执行该表的两次扫描，此外它必须等待所有现有可能会修改或者使用该索引的事务终止，甚至它可能会等待一个不相干的事务终止。因此这种方法比起普通的索引创建过程来说要做更多工作并且需要更多时间。同时，索引的创建会带来较大的CPU和I/O消耗。甚至在极端情况下，如果数据库存在长事务，我们发现create index命令根本无法结束。     从官方文档中我们可以了解到如下信息，在并发（concurrently）索引构建中，索引实际上是在事务中被构建的，它在两个事务中发生两次表扫描。在每一次表扫描之前，索引构建必须等待对该表做过修改的现有事务终止。在第二次扫描之后，索引构建必须等待任何持有早于第二次扫描的快照的事务终止。然后该索引最终能被标记为可用，CREATE INDEX命令完成。

开启第一个事务，拿到当前快照snapshot

2.等待所有修改过该表的事务结束

3.扫描该表，第一次创建索引

4.结束第一个事务

5.开启第二个事务，拿到当前快照snapshot2

6.等待所有修改过该表的事务结束

7.第二次扫描该表，将两次快照之间变更的记录，合并到索引

8.上一步更新索引结束后，等待snapshot2之前开启的所有事务结束

9.结束索引创建，索引变为可用     那么这里有个疑问，为什么需要两次扫描、两次创建索引？其实想想也很好解释。因为在第一次创建索引的时候不阻塞读写，这段时间内发生的变更需要在第二次扫描的时候合并更新进索引。

相关问答

Q1: postgresql怎么解锁表

--查询是否锁表了

1、select oid from pg_class where relname='可能被锁掉的表的表名'

，会显示一个oid

2、select pid from pg_locks where relation='刚刚查出来的oid'

--如果查询到了结果（pid），表示该表被锁 则需要释放锁定

select pg_cancel_backend(上面查到的pid)

Q2: 为什么使用postgresql skip locked加锁失败

是因为同时更新事物失误。

通常在数据库中最小粒度的锁是行锁，当一个事务正在更新某条记录时，另一个事务如果要更新同一条记录（或者申请这一条记录的锁），则必须等待锁释放。

通常持锁的时间需要保持到事务结束，也就是说，如果一个长事务持有了某条记录的锁，其他会话要持有这条记录的锁，可能要等很久。

Q3: PostgresQL建立索引如何避免写数据锁定

问题源自一个帅哥在建索引发生表锁的问题。先介绍一下Postgresql的建索引语法： Version:9.1 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ] ( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace ] [ WHERE predicate ] 这里不解释语法的诸多参数使用(排序，使用方法，填充因子等)，主要说一下concurrently的使用场景。 正常情况下Postgresql建立普通btree索引时会阻塞DML(insert,update,delete)操作，直到索引完成，期间读操作不受阻塞。当只有一个用户操作这当然没问题，但是在生产环境，并发比较高的情况下，特别是大表建索引就不能这么操作了，不然用户要跳起来骂娘了，点个按钮一天还没反应过来。--使用 Postgresql提供了一个参数，可以在线建立索引的时候避免因写数据而锁表，这个参数叫concurrently。使用很简单，就是用create index concurrently来代替create index即可。--副作用 当然了，使用这个参数是有副作用的，不使用这个参数建索引时DB只扫描一次表，使用这个参数时，会引发DB扫两次表，同时等待所有潜在会读到该索引的事务结束，这么一来，系统的CPU和IO，内存等会受一点影响，所以综合考虑，仍然让用户自行选择，而不是默认。--失败 在使用concurrently参数建索引时，有可能会遇到失败的情况，比如建唯一索引索引发现数据有重复，又或者用户发现建索引时建错字段的，取消建索引操作了。此时该表上会存在一个索引，这是因为带这个参数的建索引命令一经发出，就首先会在系统的日志表里先插一个索引记录进去，又因为这个索引最终建失败了，所以会被标记一个INVALID的状态，如下： postgres=# \d t_kenyon Table public.t_kenyon Column | Type | Modifiers --------+---------+----------- col | integer |Indexes:idx btree (col) INVALID--重建 遇到上述失效的索引重建时两个办法，一个是drop index index_name，然后再执行create index concurrently。还有一个是执行reindex index_name命令，但是后者不支持concurrent参数。--总结