postgresql查询锁表以及解除锁表操作

2024-07-30 00:09:44

1.-- 查询ACTIVITY的状态等信息

select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT,
  T.QUERY_START
 from PG_STAT_ACTIVITY T
 where T.DATNAME = '数据库用户名';

上面查询结果中：pid就是ACTIVITY的唯一标识，state就是活动状态，query就是正在执行的sql语句，query——start就是开始执行的时间。

2.-- 查询死锁的ACTIVITY

select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT,
  T.QUERY_START
 from PG_STAT_ACTIVITY T
 where T.DATNAME = '数据库用户名'
  and T.WAIT_EVENT_TYPE = 'Lock';

3.将第二条查询语句的pid字段的数字值记录下来，执行下面的查询语句可以解锁：

-- 通过pid解锁对应的ACTIVITY

select PG_CANCEL_BACKEND('6984');

上面的查询语句，执行了pg_cancel_backend()函数，该函数是取消后台操作，回滚未提交事物的用途。

补充：PostgreSQL 之锁机制

当要增删改查表中的数据时，首先是要获得表上的锁，然后再获得行上的锁

postgresql中有8种表锁

最普通的是共享锁 share 和排他锁 exclusive

因为多版本的原因，修改一条语句的同时，允许了读数据，为了处理这种情况，又增加了两种锁”access share”和”acess excusive”，锁中的关键字 access 是与多版本相关的

为了处理表锁和行锁之间的关系，有了意向锁的概念，这时又加了两种锁，即意向共享锁和意向排他锁，由于意向锁之间不会产生冲突，而且意向排它锁相互之间也不会产生冲突，于是又需要更严格一些的锁，这样就产生了“share update exclusive” 和 ”share row exclusive”

表级锁模式

表级锁模式	解释
ACCESS SHARE	只与“ACCESS EXCLUSIVE” 锁模式冲突;
	查询命令（Select command）将会在它查询的表上获取”Access Shared” 锁,一般地，任何一个对表上的只读查询操作都将获取这种类型的锁。
ROW SHARE	与”Exclusive'和”Access Exclusive”锁模式冲突;
	”Select for update”和”Select for share”命令将获得这种类型锁，并且所有被引用但没有 FOR UPDATE 的表上会加上”Access shared locks”锁。
ROW EXCLUSIVE	与 “Share,Shared roexclusive,Exclusive,Access exclusive”模式冲突;
	“Update,Delete,Insert”命令会在目标表上获得这种类型的锁，并且在其它被引用的表上加上”Access shared”锁,一般地，更改表数据的命令都将在这张表上获得”Row exclusive”锁。
SHARE UPDATE EXCLUSIVE	”Share update exclusive,Share,Share row ,exclusive,exclusive,Access exclusive”模式冲突，这种模式保护一张表不被并发的模式更改和VACUUM;
	“Vacuum(without full), Analyze ”和 “Create index concurrently”命令会获得这种类型锁。
SHARE	与“Row exclusive,Shared update exclusive,Share row exclusive ,Exclusive,Access exclusive”锁模式冲突,这种模式保护一张表数据不被并发的更改;
	“Create index”命令会获得这种锁模式。
SHARE ROW EXCLUSIVE	与“Row exclusive,Share update exclusive,Shared,Shared row exclusive,Exclusive,Access Exclusive”锁模式冲突;
	任何Postgresql 命令不会自动获得这种锁。
EXCLUSIVE	与” ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE”模式冲突,这种索模式仅能与Access Share 模式并发,换句话说，只有读操作可以和持有”EXCLUSIVE”锁的事务并行；
	任何Postgresql 命令不会自动获得这种类型的锁；
ACCESS EXCLUSIVE	与所有模式锁冲突(ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE),这种模式保证了当前只有一个事务访问这张表;“ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL” 命令会获得这种类型锁，在Lock table 命令中，如果没有申明其它模式，它也是缺省模式。

表锁的冲突关系

Requested Lock Mode	Current Lock Mode
Requested Lock Mode	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE							X	X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

表锁类型对应的数据库操作

锁类型	对应的数据库操作
ACCESS SHARE	select
ROW SHARE	select for update, select for share
ROW EXCLUSIVE	update,delete,insert
SHARE UPDATE EXCLUSIVE	vacuum(without full),analyze,create index concurrently
SHARE	create index
SHARE ROW EXCLUSIVE	任何Postgresql命令不会自动获得这种锁
EXCLUSIVE	任何Postgresql命令不会自动获得这种类型的锁
ACCESS EXCLUSIVE	alter table,drop table,truncate,reindex,cluster,vacuum full

表级锁命令（显式在表上加锁的命令）

testdb=# \h lock
Command:  LOCK
Description: lock a table

Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

where lockmode is one of:
 ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
 | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

注：

name：要锁定的现有表的锁名称(可选模式限定)。如果在表名之前指定了ONLY，则仅该表被锁定如果未指定ONLY，则表及其所有后代表(如果有)被锁定。

lock_mode：锁模式指定此锁与之冲突的锁。如果未指定锁定模式，则使用最严格的访问模式ACCESS EXCLUSIVE。

nowait

当事务要更新表中的数据时，应该申请“ROW EXCLUSIVER”

行级锁模式

只有两种，共享锁和排他锁，或者可以说是“读锁” 或 “写锁“

由于多版本的实现，实际读取行数据时，并不会在行上执行任何锁

行级锁命令（显式加行锁）

SELECT …… FOR { UPDATE | SHARE } [OF table_name[,……]] [ NOWAIT]

备注：

1）指定 OF table_name，则只有被指定的表会被锁定

2）例外情况，主查询中引用了WITH查询时，WITH查询中的表不被锁定

3）如果需要锁定WITH查询中的表，需在WITH查询内指定FOR UPDATA或FOR SHARE

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

Postgresql 实现查询一个表/所有表的所有列名

假设postgres有数据库testdb 在testdb下执行下列sql语句,得到所有表的信息 select * from information_schema.columns where table_schema='public' and table_name<>'pg_stat_statements'; 在testdb下执行下列sql语句,得到所有表的列名 select column_name from information_schema.columns where table_sche
postgresql数据库根据年月查询出本月的所有数据操作

表里的所有数据: 现在我要根据创建时间的年月查询当月所有的数据比如前台传给我一个年月的字段planTimeMonth,值为2018-06 那么我们需要查询创建时间为6月份的所有数据 sql: java代码中sql拼接: criteria.andCondition("to_char(crt_time, 'yyyy-mm')='"+query.get("planTimeMonth").toString()+"'"); 补充:postgresql
PostgreSQL 查找当前数据库的所有表操作

实现的功能类似MySQL: show tables; 在 PostgreSQL 中需要写: select * from pg_tables where schemaname = 'public'; 返回结果类似如下: schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity ------------+-----------+------------+--
postgresql查询锁表以及解除锁表操作

1.-- 查询ACTIVITY的状态等信息 select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT, T.QUERY_START from PG_STAT_ACTIVITY T where T.DATNAME = '数据库用户名'; 上面查询结果中:pid就是ACTIVITY的唯一标识,state就是活动状态,query就是正在执行的sql语句,query--start就是开始执行的时间. 2.-- 查询死锁的ACTIVIT
pgsql锁表后kill进程的操作

如下: select * from pg_stat_activity 查询系统中的进程如果怀疑哪张表被锁了,可以执行如下sql: select oid from pg_class where relname='table_name' 然后将拿到的oid 放入如下sql执行: select pg_cancel_backend('oid '):: 搞定! 此sql是kill作用补充:PostgreSQL - 如何杀死被锁死的进程前言在一次系统迭代后用户投诉说无法成功登陆系统,经过测试重现和日
MySQL行级锁、表级锁、页级锁详细介绍

页级:引擎 BDB.表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行行级:引擎 INNODB , 单独的一行记录加锁表级,直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作.如果你是写锁,则其它进程则读也不允许行级,,仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作.页级,表级锁速度快,但冲突多,行级冲突少,但速度慢.所以取了折衷的页级,一次锁定相邻的一组记录. MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定,对BDB表进行
MySQL锁(表锁,行锁,共享锁,排它锁,间隙锁)使用详解

锁,在现实生活中是为我们想要隐藏于外界所使用的一种工具.在计算机中,是协调多个进程或县城并发访问某一资源的一种机制.在数据库当中,除了传统的计算资源(CPU.RAM.I/O等等)的争用之外,数据也是一种供许多用户共享访问的资源.如何保证数据并发访问的一致性.有效性,是所有数据库必须解决的一个问题,锁的冲突也是影响数据库并发访问性能的一个重要因素.从这一角度来说,锁对于数据库而言就显得尤为重要. MySQL锁相对于其他的数据库而言,MySQL的锁机制比较简单,最显著的特点就是不同的存储引擎支持不
Mysql锁机制之行锁、表锁、死锁的实现

目录一.Mysql锁是什么?锁有哪些类别? 二.行锁和表锁的区别三.InnoDB死锁概念和死锁案例死锁场景一之selectforupdate: 死锁场景二之两个update 四.程序开发过程中应该如何注意避免死锁一.Mysql锁是什么?锁有哪些类别? 锁定义: 同一时间同一资源只能被一个线程访问在数据库中,除传统的计算资源(如CPU.I/O等)的争用以外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性.有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据
mysql中如何查看表是否被锁问题

目录如何查看是否发生死锁死锁发生情况及原因产生原因发生死锁的几种情况总结如何查看是否发生死锁在使用mysql的时候,如何查看表是否被锁呢? 查看表被锁状态和结束死锁步骤: 1.在mysql命令行执行sql语句 use dbName; // 切换到具体数据库 show engine innodb status; // 查询db是否发生死锁 2.查看数据表被锁状态 show OPEN TABLES where In_use > 0; 该语句可以查询到当前锁表的状态 3.分析锁表的SQL
MySQL中的行级锁、表级锁、页级锁

在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足. 在DBMS中,可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎).表级锁(MYISAM引擎)和页级锁(BDB引擎 ). 一.行级锁行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁.行级锁能大大减少数据库操作的冲突.其加锁粒度最小,但加锁的开销也最大.行级锁分为共享锁和排他锁. 特点开销大,加锁慢:会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发度也
SQL为什么不建议执行超过3表以上的多表关联查询

目录摘要: MySQL多表关联查询效率高点还是多次单表查询效率高? 对关联查询进行分解概述:前段时间在跟其他公司DBA交流时谈到了mysql跟PG之间在多表关联查询上的一些区别,相比之下mysql只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join),而PG是都支持的,而且mysql是往简单化方向去设计的,如果多个表关联查询(超过3张表)效率上是比不上PG的. 摘要: 不超过3层是为了效率.更通用 ,
SQL重复记录查询查询多个字段、多表查询、删除重复记录的方法

SQL重复记录查询 1.查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 例二: select * from testtable where numeber in (select number from people group by numbe
Django实现一对多表模型的跨表查询方法

当有两个表,例如一个学生表,一个班级表,是多对一的关系. 方法1: c = models.Class.object.get(pk=1) #查询到ID为1的班级 stus = models.Student.object.filter(cls=c) # 查询出来所有班级为c的学生 #stus是多个对象 #可以循环出结果 for stu in stus : print stu.id,stu.name 方法2 stus = models.Student.object.filter(cls__name='