PostgresSQL中的死锁和锁等待_pgsql 能死锁

前言

关于数据库锁等待和死锁我相信所有DBA都已经了解其中的原理和发生的场景；今天在这咱再唠叨唠叨锁等待和死锁概念，首先探讨一下死锁和锁等待这两个事件的异同。

1.死锁和锁等待这两个事件的异同

相同点：
死锁和锁等待两者都是当前事物在试图请求被其他事物已经占用的锁，从而造成当前事物无法执行的现象。

不同点：
死锁是相关session双方或者多方中必然要牺牲（回滚）至少一个事务，否则双方（或者多方）都无法执行；
锁等待则不然，对于暂时无法申请到的锁，尝试持续地“等待一段时间”，这个等待的时间就是“锁等待”参数决定（lock_timeout默认值0），超出之后就不等了。 

2.Postgresql的死锁检测机制(deadlock_timeout)

死锁概念：
死锁是指两个或是两个以上的事务执行过程中因等待或是争抢资源而造成得互相等待得现象

锁等待有可能是发展成死锁，也有可能不是死锁，可能继续等待一段时间之后就可以正常申请到所需要的锁了。
发生死锁的情况下，一定会产生锁等待，因为此时的锁等待继续下去没有任何意义，所以必须（立刻）牺牲其中一个事务，大多关系型数据库都有类似的处理机制。
没有死锁等待时间一说，因为一旦死锁的条件生成，则没有任何缓冲的余地，必须至少牺牲（回滚）其中一个事物，释放其占用的锁，来打断这个死锁的闭环；在Postgresql中，有一个死锁等待事件的参数（deadlock_timeout），默认是1s中，也就是是说Postgresql后台进程会以1s的频率来检测是否存在死锁。
思考问题：
为什么postgresql中对于死锁要以类似于定时轮训的方式来实现死锁检测而不是实时监测？
官方给出解释：

This is the amount of time to wait on a lock before checking to see if there is a deadlock condition. The check for deadlock is relatively expensive, so the server doesn't run it every time it waits for a lock. We optimistically assume that deadlocks are not common in production applications and just wait on the lock for a while before checking for a deadlock. Increasing this value reduces the amount of time wasted in needless deadlock checks, but slows down reporting of real deadlock errors. If this value is specified without units, it is taken as milliseconds. The default is one second (1s), which is probably about the smallest value you would want in practice. On a heavily loaded server you might want to raise it. Ideally the setting should exceed your typical transaction time, so as to improve the odds that a lock will be released before the waiter decides to check for deadlock. Only superusers and users with the appropriate SET privilege can change this setting.

When log_lock_waits is set, this parameter also determines the amount of time to wait before a log message is issued about the lock wait. If you are trying to investigate locking delays you might want to set a shorter than normal deadlock_timeout.

大概意思是说在Postgresql中，deadlock_timeout是进行死锁检测之前在一个锁上等待的总时间（以毫秒计）。Postgresql的理念中认为死锁检测代价是比较高的，因此服务器不会在每次等待锁时都判断有没有形成死锁。我们乐观地假设在生产应用中死锁是不常出现的，并且只在开始检测死锁之前等待一会儿。增加这个值就减少了浪费在无用的死锁检测上的时间（频率），但是降低了报告真正死锁错误的速度。默认是 1 秒（1s），这可能是实际中你想要的最小值。在一个高负载的服务器上，你可能需要增大它。这个值的理想设置应该超过你通常的事务时间，这样就可以减少在锁释放之前就开始死锁检查的机会。同理，对于高并发小事务处理系统上，默认的1秒已经足够了。只有超级用户可以更改这个设置。

3.Postgresql锁超时(lock_timeout)

Postgresql中同样可以设置所等待的超时时间，意味着当前事务在请求一个锁的时候，一旦等待时长超出指定的时间，当前语句被中止；该参数的默认值为0，意味着发生锁等待的时候永远不超时，一直等待下去，与statement_timeout不同，这个超时只在等待锁时发生。注意如果statement_timeout为非零，设置lock_timeout为相同或更大的值没有意义，因为事务超时将总是先与锁超时触发。

参考网址：PostgreSQL 文档: lock_timeout 参数

关于lock_timeout官方给出的解释：

Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt. The limit applies both to explicit locking requests (such as LOCK TABLE, or SELECT FOR UPDATE without NOWAIT) and to implicitly-acquired locks. If this value is specified without units, it is taken as milliseconds. A value of zero (the default) disables the timeout.

Unlike statement_timeout, this timeout can only occur while waiting for locks. Note that if statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value, since the statement timeout would always trigger first. If log_min_error_statement is set to ERROR or lower, the statement that timed out will be logged.

Setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions.

官网说不推荐在postgresql.conf中设置lock_timeout，因为它会影响所有会话。
实际这个建议值得商榷，如果不设置lock_timeout，一个Session的锁等待将无限拉长（如果statement_timeout不限制的话），一旦大量的连接涌入进来等待一个短时间内无法释放的不兼容锁，那么数据库的连接数可能在短时间内被打爆，影响一些正常的连接。这里认为应该根据系统中事务轻重程度，设置成超过deadlock_timeout且小于statement_timeout的一个值，强制一些超锁等待超时的Session自动终止，不要无限期等待而占用数据库连接，引发系统级的异常。

4.Postgresql中的锁超时后事务的处理

默认情况下，Postgresql在当前Session锁超时之后，会回滚整个事务，而不是当前语句。

对于Postgresql中的deadlock_timeout参数设置成不同的值，一方面取决于业务，另外一方面，对系统整体的性能影响，该如何科学地衡量？

5.Postgresql设置数据库执行超时时间（statement_timeout）

statement_timeout是一个PostgreSQL服务器参数，用于设置单个SQL语句的执行超时时间。当一个查询执行的时间超过了设定的超时时间，PostgreSQL将终止该查询并返回一个错误信息。这个参数可以帮助我们防止长时间运行的查询对数据库性能造成影响，同时也有助于保护数据库免受恶意攻击。

应用场景：

1.防止长时间运行的查询：有时，由于数据量过大或其他原因，查询可能会运行很长时间。通过设置合适的statement_timeout值，我们可以确保查询不会无限制地运行，从而避免对数据库性能造成影响。

2.保护数据库免受恶意攻击：恶意用户可能会尝试执行大量计算密集型操作，以消耗数据库资源并使其无法正常工作。通过设置statement_timeout，我们可以限制单个查询的执行时间，从而降低这种攻击的风险。

3.优化查询性能：在某些情况下，我们可能需要优化查询性能。通过观察查询的执行时间，我们可以确定是否需要调整statement_timeout值以提高整体性能。

最佳实践
1.在使用statement_timeout时，我们需要遵循一些最佳实践以确保其有效性和安全性，不会对业务造成中断。

2.合理设置超时时间：设置过短的超时时间可能会导致频繁中断查询，而设置过长的超时时间则可能无法达到预期的保护效果。因此，我们需要根据实际业务需求和数据库性能来合理设置超时时间。

3.监控查询执行时间：定期监控查询的执行时间可以帮助我们了解数据库的性能状况，并及时发现潜在的问题。我们可以使用各种工具（如pgBadger、pgAdmin等）来分析查询执行时间和性能瓶颈。

4.重要查询设置优先级：对于重要的查询，我们可以通过设置较低的statement_timeout值来确保它们能够顺利完成。这样可以避免因超时而导致的重要查询失败。

5.考虑使用事务：在某些情况下，我们可以考虑将多个查询封装在一个事务中。这样，即使某个查询超时，其他查询仍然可以继续执行。但请注意，这可能会增加事务提交的复杂性。