解决postgresql数据库锁表问题
 
–pgsql锁表查询
 select a.pid,b.oid,b.relname,c.state_change,query from pg_locks a
 join pg_class b on a.relation = b.oid
 join pg_stat_activity c on a.pid = c.pid
 where a.mode like ‘%ExclusiveLock%’ ORDER BY c.state_change;
 
– pgsql释放锁
 select pg_terminate_backend(a.pid) from pg_locks a
 join pg_class b on a.relation = b.oid
 join pg_stat_activity c on a.pid = c.pid
 where a.mode like ‘%ExclusiveLock%’;
 
–pid释放锁
 select pg_terminate_backend(‘144362’);

 
Mysql锁表现象
 
1、目前线下业务中心N个，每一个线下业务中心，拥有独立的内网服务器，用于线下业务，部分中心务偶尔性的出现业保存时卡住无法进行的情况一旦卡住则无法使用，时间不确定、周期不确定，部分中心周期短的时候1周一次，时间长的可能一两个月一次，而实际业务量基本保持稳定增长，没有大变化 。
 
2、同样的代码仅在部分中心出现该问题。
 
3、表引擎为innodb
 
4、 mysql版本为5.6.x
 
5、 服务器基本为windows server 2008R2、windows server 2012。
 
原因分析查找
 
1、show open tables where in_use >=1;  执行时发现某一张表 in_use达到100次之多，直接影响业务的正常进行。(https://dev.mysql.com/doc/refman/5.6/en/show-open-tables.html)
 
2.、查询当前锁定的事务为空、等待锁的事务也为空，没有锁的事务？？但表N多in_use状态
 
3、每次锁的表不固定，随机锁表(难道是系统整体事务有问题？也不可能，有的中心长期运行无任务问题，同样的代码)。
 
解决过程
 
事情比较诡异，当然首先想到的是事务处理有问题，而经过确认研究，事务写法、配置均正确。
 
1、暂时没解决之前，只能先临时重启mysql处理，另行查找原因。
 
2、查找源代码，发现锁表的很多是非常简单的业务比如整个系统中只涉及到一个insert 语句而已，无复杂业务，确认源代码无问题。
 
mysql> show variables like ‘innodb_autoinc_lock_mode’;
 
+————————–+——-+
 
| Variable_name | Value |
 
+————————–+——-+
 
| innodb_autoinc_lock_mode | 1 |
 
+————————–+——-+
 
发现自增表锁模式为：连续模式，每次也是单记录写入。
 
查找mysql错误日志，发现没有死锁日志。
 
最终解决办法
 
怀疑跟操作系统有关，不同批次的服务器供应商安装的操作系统有异，将服务器重装为Linux之后，观察运行情况，重装之后至今(几个月了)但凡装过Linux系统的业务中心均再未出现该现象。 update 2020年4月30日  目前已经运行近4个月100%正常。
 
结论
 
但凡不是自己安装的系统，或者不确定是正版系统的情况下系统出现莫名其妙的问题， 初步排查程序没有问题时，可以优先考虑操作系统的问题。

引言
 
作为开发人员，我们经常会和数据库打交道。
 
当我们对数据库进行修改操作的时候，例如添加字段，更新记录等，没有正确评估该表在这一时刻的使用频率，直接进行修改，致使修改操作长时间无法响应，造成锁表，在 mysql 中，如果出现 alter 操作引发Waiting for table metadata lock 类型的锁表，会导致任何操作不可用，后果是灾难性的。
 
程序设计不合理，频繁对同一张表进行修改操作，同样可能引发锁表。
 
数据库锁表问题一旦出现，往往都是很致命的，锁表时间过久，可能会造成大量请求阻塞异常，影响系统业务正常开展。本文将详细阐述数据库锁表原因，解锁以及如何避免锁表，希望能对你有所帮助。
 
锁表产生场景以及原因
 
场景：
 
锁表通常发生在 DML（ insert 、update 、delete ）语句中，例如： 程序A 对 A表 的 a数据 进行修改，修改过程中产生错误，没有commit也没有rollback，这个时候 程序B 对 A表 的 a数据库进行修改，会产生资源正忙的异常，也就是锁表。
 
DDL也会引发锁表，例如在 MySql 操作一张大表，利用 alter 语句修改或新增字段的时候，恰巧有一个长事务（包括读）在操作此表，会触发修改等待，造成锁表。
 
原因：
 
当多个事务处理对多个资源同时访问时，若双方已锁定一部分资源但也都需要对方已锁定的资源时，无法在有限的时间内完全获得所需的资源，就会处于无限的等待状态，从而造成其对资源需求的死锁，导致锁表。
 
如何解锁
 
锁表一旦产生，需要我们尽快对其解锁，释放资源，不然会一直阻塞，下面主要讲解 MySql 和 Oracle 数据库的解锁方式。
 
MySql 解锁：
 
执行sql：
 
select * from information_schema.processlist where command not in ('Sleep') ORDER BY time desc
 
通过此 sql 可以查询到以下内容：
 
 sql已经按照阻塞时长从大到小排序，找到耗时长的记录 id ，kill 即可：
 
kill 16519789
 
通过此命令也可以查询到 mysql 的慢sql 语句，进行优化，info字段即为具体执行的sql语句。
 
oracle解锁：
 
执行以下sql：
 
SELECT SESS.SID,  
SESS.SERIAL#,  
LO.ORACLE_USERNAME,  
LO.OS_USER_NAME,  
AO.OBJECT_NAME 被锁对象名, 
LO.LOCKED_MODE 锁模式, 
sess.LOGON_TIME 登录数据库时间,
'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ','||SESS.SERIAL#||'''' FREESQL
FROM V$LOCKED_OBJECT LO,  DBA_OBJECTS AO,  V$SESSION SESS 
WHERE AO.OBJECT_ID = LO.OBJECT_ID 
AND LO.SESSION_ID = SESS.SID 
ORDER BY sid, sess.serial#;
 
通过此sql可以查询到以下内容：
 
 然后复制最后一列 FREESQL 的内容，直接执行即可。
 
复制完执行可能会报错：ORA-00031: session marked for kill，这表示ORACLE已经把它标记为一个杀死的进程，但暂时无法将其彻底杀死，这个时候需要我们执行下面的sql，查出它在服务器上的进程id：
 
# sid 为上面sql 查出来的 sid
select spid, osuser, s.program
   from v$session s,v$process p
   where s.paddr=p.addr
   and s.sid='24986' 
 
通过上方 sql 可以得到服务器上的进程 id，登录数据库所在服务器，利用 kill 命令将其杀死即可：
 
kill -9 12009（查出来的spid）
 
oracle查询指定时间内的慢sql：
 
select *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0 and sa.LAST_ACTIVE_TIME >to_date( '2021-11-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;
 
根据查询到的慢 sql 结果可以对相关 sql 进行优化。
 
如何避免锁表
 
通常情况下，数据库锁表大多是因为程序设计不合理导致的，在写代码的时候，我们要对业务场景充分考虑，尽量做到以下两点：
 
尽量减少程序中的 DML（insert，update，delete） 操作所花费的时间，对此类操作做好隔离控制，防止阻塞。
如果事务产生异常，确保事务可以正常回滚。
 
此外，不要轻易对线上数据库进行 DDL 操作，尤其是使用量大且频繁的数据表，强行修改可能会引发灾难性后果，目前修改线上表结构主要有以下几种方式：
 
等到使用量小的时候（例如半夜）修改，但有一定风险
暂时停止服务，修改完毕再上线，但会影响用户体验
复制旧表结构到新表，旧表创建触发器，旧表操作同步执行到新表，然后对新表修改表结构，同步旧数据到新表，锁旧表（只读），最后新表替换旧表，此方案基本对用户体验无影响，但操作较为复杂。
扩展表，如果字段使用频繁会影响性能。
 
在实际生产中，我们需要根据具体场景去选择适合自己的表结构变更方式。
 
结语
 
本文就数据库锁表问题进行了较为详细的探讨，喜欢的朋友可以收藏下，以备不时之需。
 
在真实的生产环境中，一般都会专门部署一套用来监测数据库的应用，一旦发现锁表或慢sql会及时报警通知相关负责人处理，但查询锁表以及解锁的相关sql还是需要我们特别掌握一下，也许在关键时刻就会发挥作用。
 
关注公众号 螺旋编程极客 第一时间解锁精彩内容，同时可获取独家研发的 代码生成器，提升开发效率

1定义
 阻塞：多个线程之间的相互影响，等待临界资源；
 死锁：多个线程之间互相等待，形成等待环；
 
对于死锁，达梦数据库会自动检测，并选择牺牲掉其中的一个事务，保证其它事务正常运行。
 死锁一般是由于应用程序bug导致的，当有修改多表的事务时，应尽量保证每个事务以相同的顺序锁定表。
 可以通过V$DEADLOCK_HISTORY查询死锁历史，其中记录了产生死锁后被牺牲掉的事务的事务ID、会话ID、执行的SQL语句以及死锁发生时间（没有记录造成死锁的其它事务）。
 
对于阻塞，达梦数据库不会自动处理，被阻塞的事务会一直挂起，直到获取到所需的资源。
 阻塞一般是由应用程序bug造成的，在应用编写中，应尽量形成短事务，快速提交。
 
2什么情况下会形成阻塞？
 在达梦数据库中，默认采用读提交的机制，查询永远不会被阻塞。查询一般情况下也不会阻塞增删改操作，SELECT FOR UPDATE的情况除外。
 INSERT语句被阻塞的情况：多个事务同时向有主键或唯一约束的表中插入相同的数据；
 删、改语句被阻塞的情况：所需要操作的数据被其它事务修改过，且一直没有提交或回滚；
 
示例：
 job表结构和数据如图
 
 
先执行：update "DMHR"."JOB" set MIN_SALARY='10000' where JOB_ID='73'
 
 
再执行：select * from "DMHR"."JOB" where JOB_ID='73'
 
 
可以看到， select 操作可以正常进行。但是由于之前的事务没有提交，所以查出来的结果还是旧的值。
 再执行：delete from "DMHR"."JOB" where JOB_ID='73';
 
 
此时，发生阻塞，因为 delete 操作需要加锁，当前事务被挂起。
 
3.阻塞的排查
 当阻塞发生时，我们可以通过 v$lock 视图查到当前数据库中锁的状态
 
select * from v$lock;
 
 
结果中我们可以看到，事务 1459 被阻塞了，阻塞他的事务为 1450，同样我们也可以通过 V$TRXWAIT 视图排查阻塞，
 
select * from v$trxwait;
 
 
得出同样的结果，等待时间是1814645 毫秒。
 接下来，通过 V$SESSIONS 视图查找两个事务对应的会话，可以知道是哪些 SQL 语句产生的阻塞。
 
select sess_id,sql_text,state,trx_id from v$sessions where trx_id in('1459','1450');
 
 
4.阻塞的解决方法
 根据需求，可以有两种解决方案。
 4.1 提交或回滚产生阻塞的事务。
 根据上文，我们可知产生阻塞的事务会话 ID 为 140702994469648。此时，我们只需要在该会话下提交或回滚事务，锁自然会被释放，阻塞解决。
 
4.2 关闭产生阻塞的会话
 同样，我们也可以使用系统过程 SP_CLOSE_SESSION(SESS_ID)来关闭对应的会话
 
 
此时，锁被释放，delete 操作也可以顺利进行下去。
 
 
备注：可以用一条语句查看出被阻塞的操作和事务会话 ID（SESS_ID）
 
select a.*,b.NAME,c.SESS_ID from v$lock a
left join sysobjects b on b.ID=a.TABLE_ID
left join v$sessions c on a.TRX_ID=c.TRX_ID;
 
然后直接执行SP_CLOSE_SESSION(SESS_ID) 来关闭对应的会话
 
参考链接：https://blog.csdn.net/fengxiaozhenjay/article/details/101707104