PostgreSQL 死锁与分析

2020-06-17 00:00:00 语句设置时间等待死锁

任何数据库都有死锁，MYSQL的死锁有相关的工具，或者去日志查找，今天来说说postgresql的死锁又怎么搞。

首先来说postgresql 检测死锁在配置文件中是有相关配置的，在postgresql中有三个和查询有关的超时设置

deadlock_timeout

进行死锁检测之前在一个锁上等待的总时间

lock_timeout

锁等待超时。语句在试图获取表、索引、行或其他数据库对象上的锁时等到超过指定的毫秒数，该语句将被中止。不推荐在postgresql.conf中

statement_timeout

控制语句执行时长，单位是ms。超过设定值，该语句将被中止。

将被中止。

这三个里面的设置，死锁的检测一定是要设置的，因为死锁被发现时，好是尽快的通过系统检测到后，尽快的解除（牺牲一个）。保证系统正常的运行，尤其在OLTP的系统中。所以这里一般可以设置一个较短的值，例如1秒。

lock_timeout 这个属于 A 有 B 的资源，B需要等待A 的资源释放后等待可以忍受的时间，一般来说，如果不是故意例如写完begin 后不操作 commit,任务很快的完成的情况下，一般来说我们不设置 lock_timeout ，当然如果在一个糟糕的系统中，经常发生霸占资源不释放的状态，这样的不设置也可以很快发现问题。（稍后会给出语句）。

statement_timeout 类似于MYSQL 也有类似的设置或者通过PT工具来进行设置，将超过运行设定时间的语句，KILL掉，这里面我们也是一般不进行设置的。

不进行设置默认是一直等待。

OK 我们先来看一下什么是死锁，这里我们稍微的把死锁的鉴定的时间调大一点，好来给执行发现死锁的语句一点时间，我们将deadlock_timeout 设置为 20秒，当然如果是生产系统，你这样做，呵呵你还想干吗？

我们启动两个se

Session 1

1 test=# begin;

BEGIN

2 test=# update test set value = 'tyyu' where id =3;

UPDATE 1

5 test=# update test set value = 'tyyu' where id =2;

Session 2

3 test=# begin;

BEGIN

4 test=# update test set value = 'tyyu' where id =2;

UPDATE 1

6 test=# update test set value = 'tyyu' where id =3;

大家可以看我的执行语句前面的序号

系统会给出死锁的信息以及相关解决的信息，当然如果在死锁期间，通过语句你也是可以发现相关的死锁信息的。

是可以发现相关的死锁信息的。

SELECT blocked_locks.pid AS blocked_pid,

blocked_activity.usename AS blocked_user,

blocking_locks.pid AS blocking_pid,

blocking_activity.usename AS blocking_user,

blocked_activity.query AS blocked_statement,

blocking_activity.query AS current_statement_in_blocking_process

FROM pg_catalog.pg_locks blocked_locks

JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid

JOIN pg_catalog.pg_locks blocking_locks

ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE

AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid

AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid

AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid

AND blocking_locks.pid != blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

WHERE NOT blocked_locks.GRANTED;

反过来我们去查看日志

CST [15798] LOG: duration: 74.150 ms

CST [15788] LOG: process 15788 detected deadlock while waiting for ShareLock on transaction 678 after 20003.487 ms

CST [15788] DETAIL: Process holding the lock: 15786. Wait queue: .

CST [15788] CONTEXT: while updating tuple (0,3) in relation "test"

[15788] STATEMENT: update test set value = 'tyyu' where id =3;

CST [15788] ERROR: deadlock detected

CST [15788] DETAIL: Process 15788 waits for ShareLock on transaction 678; blocked by process 15786.

Process 15786 waits for ShareLock on transaction 679; blocked by process 15788.

Process 15788: update test set value = 'tyyu' where id =3;

Process 15786: update test set value = 'tyyu' where id =2;

CST [15788] HINT: See server log for query details.

CST [15788] CONTEXT: while updating tuple (0,3) in relation "test"

CST [15788] STATEMENT: update test set value = 'tyyu' where id =3;

CST [15786] LOG: duration: 12131.851 ms

查看上面的日志

通过上面的日志给出的信息 process 15788 检测到死锁，在20秒的时候，（20003.487）15788 等待sharelock 锁，而process 15788 被 15786 kill 掉。在后踢掉的过程中， 15788 的语句是 update test set value = 'tyyu' where id =3; 15786 的语句是

update test set value = 'tyyu' where id =2;

在PG 中有行锁和表锁两种，每行记录都有xmax，如果一个事务要处理这一行会在 XMAX 上添加 transaction_ID, 如果这样一行已经有了 transaction_id 则要再次添加事务ID 的事务就需要等待。而当要进行UPDATE 和 DELETE 操作的过程中会检查相关行的 XMAX 的状态。

通过判断XMAX 的状态来，这条记录可以被UPDATE 或者 DELETE 还是不能。这也是POSTGRESQL 和别的数据库比较没有UNDO 这个空间的设置原因之一，因为不需要。

说完简单的分析，下一期我们来讲讲，怎么避免死锁。

相关文章