PostgreSQL 数据库检测到 ShareLock 死锁的处理方法
【作者】泊涯，公司分部测试经理，集团公司技术专家成员之一，目前主要在客户现场做银行系统的性能诊断分析优化和测试管理工作。
PostgreSQL 是一个免费数据库，对于处理分析型+交易型混合型系统来说确实很不错，特别是版本的升级到11.2后性能提升很多，很多运行机制跟Oracle越来越接近，确实很强大，但是开源系统确实存在一些不如意地方，需要长时间项目问题集锦积累才能慢慢的领悟。
而作为从非功能测试转型做技术运维，在运维过程中会从非功能方面（高可用性、高可靠性、可扩展性等）和性能测试优化方面考虑确实可以避免很多生产不必要的故障问题，但是对于开源的技术在版本迭代过程中总会有些不如意的技术故障还是需要我们自己持续性学习、挖掘、积累、提升，才能确保技术能持续满足业务运营发展和市场需求。
如下问题是我们17年上线的系统，经2年的运行，很多业务表达到千万级，导致需要读写分离、分表等来优化，但是问题还是偶尔出现，说明技术还不到位，例如如下：
问题原因：
目前生产环境使用postgres9.5版本，主从配置，但是因为行业业务的特殊性，有些回访表等都是三四百万级别的，而且日常更新频繁度非常高，日常使用频繁比较高的表，一天insert、update都是接近十万，delete三四万以上，导致在对该表的统计信息不准确，而pg默认 autovacuum默认参数导致部分表因本身存量数据大，更新比例小，导致这些日常被用到的大表反而没办法被重新统计分析，最终导致磁盘IO 高，CPU 高问题，而因为在调整过程中调整不当也导致如下，在对表进行批量update 时，而PG就进行 autovacuum_analyze，结果导致出现 ShareLock错误，具体错误如下：
错误内容：
2019-04-14 15:15:47,707 ERROR [thinkgem.jeesite.common.repeat_form_validator.Token] - 2-
2019-04-14 15:15:47,707 ERROR [thinkgem.jeesite.common.repeat_form_validator.Token] - org.apache.shiro.web.servlet.ShiroHttpSession@
461f4ab1
2019-04-14 15:16:15,952 ERROR [thinkgem.jeesite.common.repeat_form_validator.Token] - 2-
2019-04-14 15:16:15,952 ERROR [thinkgem.jeesite.common.repeat_form_validator.Token] - org.apache.shiro.web.servlet.ShiroHttpSession@
285d498f
2019-04-14 15:16:18,138 ERROR [thinkgem.jeesite.common.repeat_form_validator.Token] - 1-61322fb9-7ca7-482b-99ee-913074957a94
2019-04-14 15:16:24,227 ERROR [500.jsp] -
Error updating database. Cause: org.postgresql.util.PSQLException: 错误: 检测到死锁
详细：进程6533等待在事务 36964707上的ShareLock; 由进程10733阻塞.
进程10733等待在事务 36964708上的ShareLock; 由进程6533阻塞.
建议：详细信息请查看服务器日志.
在位置：当更新关系"visit_crd"的元组(11314, 33)时
The error may involve defaultParameterMap
The error occurred while setting parameters
SQL: UPDATE visit_crd SET visit_plan_id = ?, customer_number = ?, call_id = ?, time_start = ?, time_end = ?,
duration = ?, type = ?, route = ?, cpn = ?, cdpn = ?, recording = ?, trunk_number = ?, update_by = ?, updat
e_date = ?, remarks = ?, affiliation = ?, update_ind = ?, execute_ind = ? WHERE id = ?
Cause: org.postgresql.util.PSQLException: 错误: 检测到死锁
详细：进程6533等待在事务 36964707上的ShareLock; 由进程10733阻塞.
进程10733等待在事务 36964708上的ShareLock; 由进程6533阻塞.
建议：详细信息请查看服务器日志.
在位置：当更新关系"visit_crd"的元组(11314, 33)时
; SQL []; 错误: 检测到死锁
详细：进程6533等待在事务 36964707上的ShareLock; 由进程10733阻塞.
进程10733等待在事务 36964708上的ShareLock; 由进程6533阻塞.
建议：详细信息请查看服务器日志.
在位置：当更新关系"visit_crd"的元组(11314, 33)时; nested exception is org.postgresql.util.PSQLException: 错误: 检测到死锁
详细：进程6533等待在事务 36964707上的ShareLock; 由进程10733阻塞.
进程10733等待在事务 36964708上的ShareLock; 由进程6533阻塞.
建议：详细信息请查看服务器日志.
在位置：当更新关系"visit_crd"的元组(11314, 33)时
org.springframework.dao.DeadlockLoserDataAccessException:
问题分析：
PG 默认 autovacuum
1、autovacuum_vacuum_threshold:默认50
2、autovacuum_vacuum_scale_factor默认值为20%。
3、autovacuum_analyze_threshold:默认50。
4、autovacuum_analyze_scale_factor默认10%
第一次优化：
autovacuum_vacuum_scale_factor = 0.001
autovacuum_analyze_scale_factor = 0.001
结果导致如上错误信息：
第二次优化：
autovacuum_vacuum_scale_factor = 0.03
autovacuum_analyze_scale_factor = 0.03
问题得到解决
如有任何问题，可点击文末阅读原文，到社区原文下评论交流 觉得本文有用，请转发或点击“在看”，让更多同行看到
资料/文章推荐：
欢迎关注社区 “PostgreSQL”技术主题 ，将会不断更新优质资料、文章。地址：
http://www.talkwithtrend.com/Topic/112101
下载 twt 社区客户端 APP
长按识别二维码即可下载
或到应用商店搜索“twt”
长按二维码关注公众号
*本公众号所发布内容仅代表作者观点，不代表社区立场