1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL9.2Beta: Add a deadlock counter to the pg_stat_database system view

• 数据库

pg_stat_database 系统视图记录每个数据库的运行信息，包括 blks_read,blks_hit，tup_fetched，tup_inserted 等信息，在 9.2 版本增加了 deadlocks 信息，用于统计数据库的死锁次数。

环境准备

1.1 一张测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
francs=> select * from test_1 limit 10;  
id | name  
----+------  
 2 | a  
 3 | a  
 4 | a  
 5 | a  
 6 | a  
 7 | a  
 8 | a  
 9 | a  
10 | a  
11 | a  
(10 rows)

francs=> /d test_1  
 Table "francs.test_1"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |  
Indexes:  
 "idx_test_1_id" UNIQUE, btree (id)  
 "idx_test_1_name" btree (name)

1.2 实验前查询 pg_stat_database 视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
francs=# select * from pg_stat_database where datname='francs';  
-[ RECORD 1 ]--+-----------------------------  
datid | 16386  
datname | francs  
numbackends | 1  
xact_commit | 4871  
xact_rollback | 118  
blks_read | 280156  
blks_hit | 9684659  
tup_returned | 30715579  
tup_fetched | 59701  
tup_inserted | 27185774  
tup_updated | 168  
tup_deleted | 12800  
conflicts | 0  
temp_files | 2  
temp_bytes | 72048640  
deadlocks | 0  
blk_read_time | 0  
blk_write_time | 0  
stats_reset | 2012-05-17 14:42:46.51038+08

备注: 此时的 pg_stat_database.deadlocks 值为 0。

死锁实验

2.1 session A

1
2
3
4
5
6
7
8
9
 
francs=> begin;  
BEGIN  
francs=> select * from test_1 where id=2;  
id | name  
----+------  
 2 | a  
(1 row)
francs=> update test_1 set name='aaa' where id=2;  
UPDATE 1

2.2 session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
francs=> begin;  
BEGIN  
francs=> select * from test_1 where id=2;  
id | name  
----+------  
 2 | a  
(1 row)
francs=> select * from test_1 where id=3;  
id | name  
----+------  
 3 | a  
(1 row)
francs=> update test_1 set name='aaa3' where id=3;  
UPDATE 1
francs=> update test_1 set name='aaaa' where id=2;  
< hang >

2.3 再次回到 session A

1
2
3
4
5
 
francs=> update test_1 set name='aaa3333' where id=3;  
ERROR: deadlock detected  
DETAIL: Process 25454 waits for ShareLock on transaction 1995; blocked by process 25508.  
Process 25508 waits for ShareLock on transaction 1994; blocked by process 25454.  
HINT: See server log for query details.

备注：死锁发生。

2.4 再次查询 pg_stat_database 视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
francs=# select * from pg_stat_database where datname='francs';  
-[ RECORD 1 ]--+-----------------------------  
datid | 16386  
datname | francs  
numbackends | 4  
xact_commit | 4907  
xact_rollback | 119  
blks_read | 280159  
blks_hit | 9687460  
tup_returned | 30725377  
tup_fetched | 61093  
tup_inserted | 27185774  
tup_updated | 168  
tup_deleted | 12800  
conflicts | 0  
temp_files | 2  
temp_bytes | 72048640  
deadlocks | 1  
blk_read_time | 0  
blk_write_time | 0  
stats_reset | 2012-05-17 14:42:46.51038+08

备注：发现 deadlocks 值为1。

附：pg_stat_database 说明

Table 27-4. pg_stat_database view

PostgreSQL9.2Beta: Add a deadlock counter to the pg_stat_database system view

参考

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/237860.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论