postgreSQL锁

首先应用挂起在DELETE语句部分，先查一下DELETE的表数据量，发现只有3K多行，这么小的表应该很快跑完才对。猜想应该是表被锁住导致操作不能进行下去。于是通过下面的语句查询是否该表上存在锁：

SQL：

select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname

from pg_locks a

join pg_class b on a.relation = b.oid

where upper(b.relname) = 'TABLE_NAME';

 

以上为查询某表上是否存在锁的SQL语句。

查到后发现确实存在锁，如下：

 locktype | database |  pid  |      mode       | relation | relname

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

 relation |   439791 | 26752 | AccessShareLock |  2851428 |table_name

 relation |   439791 | 26752 | ExclusiveLock   |  2851428 |table_name

 

再根据上面查出来的pid去表pg_stat_activity查询一下该锁对应的SQL语句：

select usename,current_query ,query_start,procpid,client_addr from pg_stat_activity where procpid=26675;

如下：

  usename  |  current_query                      |          query_start          | procpid |  client_addr   

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

 gpcluster | DELETE FROM TABLE_NAME WHERE A = 1  | 2011-05-14 09:35:47.721173+08 |   26752 | 192.168.165.18

(1 row)

 

 

通过以上可以发现，就是上面的锁导致该语句一直挂在那里。然后把该锁结束掉后，应用很快跑完。

然后核查应用的代码，发现代码里面两个事务都没有提交操作。后增加提交操作后，重新跑数，很快跑完。

 

总结：

1、查询表是否存在锁postgreSQL的SQL为：

select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname

from pg_locks a

join pg_class b on a.relation = b.oid

where upper(b.relname) = 'TABLE_NAME';

 

2、查询表锁及操作的SQL语句的SQL为：

select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname,c.usename,c.current_query,c.query_start,c.client_addr

from pg_locks a

join pg_class b on a.relation = b.oid

join pg_stat_activity c on a.pid = c.procpid

where upper(b.relname) = 'TABLE_NAME';

 

3、对于PG里面的锁的相关介绍，详细见：http://cs.scu.edu.cn/~zuojie/pgsqldoc-8.2c/explicit-locking.html

不管是做应用开发，还是平时的基础数据库操作的时候，锁都是得必须注意的一个事情。

 

下面附上PG的表级锁简单介绍：

下面的列表显示了可用的锁模式和它们被 PostgreSQL 自动使用的场合。你也可以用 LOCK 命令明确获取这些锁。请注意所有这些锁模式都是表级锁，即使它们的名字包含"row"单词(这些名称是历史遗产)。从某种角度而言，这些名字反应了每种锁 模式的典型用法，但是语意却都是一样的。两种锁模式之间真正的区别是它们有着不同的冲突锁集合。两个事务在同一时刻不能在同一个表上持有相互冲突的锁。不 过，一个事务决不会和自身冲突。比如，它可以在一个表上请求 ACCESS EXCLUSIVE 然后接着请求 ACCESS SHARE 。非冲突锁模式可以被许多事务同时持有。请特别注意有些锁模式是自冲突的(比如，在任意时刻 ACCESS EXCLUSIVE 模式就不能够被多个事务拥有)，但其它锁模式都不是自冲突的(比如，ACCESS SHARE 可以被多个事务持有)。

 

表级锁模式

 

ACCESS SHARE

只与 ACCESS EXCLUSIVE 冲突。

 

SELECT 命令在被引用的表上请求一个这种锁。通常，任何只读取表而不修改它的命令都请求这种锁模式。

 

ROW SHARE

与 EXCLUSIVE 和 ACCESS EXCLUSIVE 冲突。

 

SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在目标表上需要一个这样模式的锁(加上在所有被引用但没有 ACCESS SHARE 的表上的 FOR UPDATE/FOR SHARE 锁)。

 

ROW EXCLUSIVE

与 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 冲突。

 

UPDATE, DELETE, INSERT 命令自动请求这个锁模式(加上所有其它被引用的表上的 ACCESS SHARE 锁)。通常，这种锁将被任何修改表中数据的查询请求。

 

SHARE UPDATE EXCLUSIVE

与 SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 冲突。这个模式保护一个表不被并发模式改变和 VACUUM 。

 

VACUUM(不带 FULL选项), 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 命令没有明确声明需要的锁模式时，它是缺省锁模式。

 

【提示】只有 ACCESS EXCLUSIVE 阻塞 SELECT(不包含 FOR UPDATE/SHARE 语句)。

 

一旦请求已获得某种锁，那么该锁模式将持续到事务结束。但是如果在建立保存点之后才获得锁，那么在回滚到这个保存点的时候将立即释放所有该保存点之后获得的锁。这与 ROLLBACK 取消所有保存点之后对表的影响的原则一致。同样的原则也适用于 PL/pgSQL 异常块中获得的锁：一个跳出块的错误将释放在块中获得的锁。