root/trunk/tools/lock.info/lock.info

Revision 244, 5.3 kB (checked in by depesz, 7 years ago)

Script to show detailed information about locks in database

  • Property svn:executable set to *
Line 
1 #!/bin/bash
2
3 # here are function definitions, for actual code that is executed, search for MAIN PROGRAM comment
4
5 setup() {
6     ssh_remote_user=hlubaczewski
7     ssh_keyfile=/home/hlubaczewski/lock.info/lock.info.key
8     PGUSER=postgres
9     PGDATABASE=gilt_prod
10     PGPORT=5432
11     PGHOST=127.0.0.1
12
13     ssh_options="-n -q -l $ssh_remote_user -i $ssh_keyfile -o StrictHostKeyChecking=no -o PasswordAuthentication=no"
14     ssh="ssh $ssh_options"
15     export PGUSER PGDATABASE PGHOST PGPORT
16 }
17
18 clear_ssh_env() {
19     while read line
20     do
21         unset ${line%%=*}
22     done < <( env | grep ^SSH )
23 }
24
25 show_remote_data() {
26     export use_ip="$1"
27     export use_port="$2"
28     $ssh $use_ip info $use_port
29 }
30
31 # MAIN PROGRAM #
32
33 setup
34 clear_ssh_env
35
36 locked_queries="$( psql -qAtX -F$'\t' -c "
37     select
38         extract(epoch from now() - query_start)::int8,
39         procpid,
40         regexp_replace(current_query, E'\\\\n', ' ', 'g'),
41         query_start::timestamp(0),
42         coalesce(host(client_addr)::text, 'unix socket'),
43         coalesce(client_port::text, 'internal connection'),
44         usename,
45         application_name,
46         xact_start::timestamp(0),
47         backend_start::timestamp(0)
48     from pg_stat_activity
49     where query_start < now() - '5 seconds'::interval and waiting
50     order by query_start
51     " )"
52
53 i=0
54 while IFS=$'\t' read -r blocked_runtime blocked_pid blocked_query blocked_start blocked_ip blocked_port blocked_user blocked_app blocked_xact_start blocked_backend_start
55 do
56     i=$(( i + 1 ))
57     printf "%.2d. Query: %s\n" "$i" "$blocked_query"
58     printf "  - Blocked for %d seconds.\n" "$blocked_runtime"
59     printf "  - Backend / transaction / query start: %s / %s / %s\n" "$blocked_backend_start" "$blocked_xact_start" "$blocked_start"
60     printf "  - Blocked user and application: %s / %s\n" "$blocked_user" "$blocked_app"
61     printf "  - Connection from: %s (remote port %s)\n" "$blocked_ip" "$blocked_port"
62
63     if [[ "$blocked_ip" == "unix socket" ]]
64     then
65         printf "    - Connection from unix socket cannot be traced back to origin.\n"
66     else
67         show_remote_data "$blocked_ip" "$blocked_port" | sed 's/^/    - /'
68     fi
69
70     locking_queries="$( psql -qAtX -P null=NULL -F$'\t' -c "
71         SELECT
72             b.locktype,
73             b.relation::regclass,
74             b.classid,
75             b.objid,
76             b.objsubid,
77             b.virtualxid,
78             b.transactionid,
79             extract(epoch from now() - a.query_start)::int8,
80             a.procpid,
81             regexp_replace(current_query, E'\\\\n', ' ', 'g'),
82             a.query_start::timestamp(0),
83             coalesce(host(a.client_addr)::text, 'unix socket'),
84             coalesce(a.client_port::text, 'internal connection'),
85             a.usename,
86             a.application_name,
87             a.xact_start::timestamp(0),
88             a.backend_start::timestamp(0)
89         FROM pg_database d join pg_locks b on d.oid = b.database JOIN pg_locks l ON b.database = l.database and
90         (
91             ( b.locktype = 'relation' and b.relation = l.relation )
92             OR
93             ( b.locktype = 'advisory' and b.classid = l.classid and b.objid = l.objid and b.objsubid = l.objsubid )
94             OR
95             ( b.locktype = 'virtualxid' and b.virtualxid = l.virtualxid )
96             OR
97             ( b.locktype = 'transactionid' AND b.transactionid = l.transactionid )
98         ) join pg_stat_activity a on l.pid = a.procpid
99         WHERE b.pid = $blocked_pid AND not b.granted AND l.granted and d.datname = current_database()
100         ORDER BY a.xact_start asc
101     " )"
102
103     if [[ -z "$locking_queries" ]]
104     then
105         echo "  SORRY, cannot find why it's locked."
106         echo
107         continue
108     fi
109     echo "  - Locked because of:"
110     while IFS=$'\t' read -r lock_type lock_table lock_class lock_objid lock_objsubid lock_virtualxid lock_transactionid locking_runtime locking_pid locking_query locking_start locking_ip locking_port locking_user locking_app locking_xact_start locking_backend_start
111     do
112         printf "    - Query: %s\n" "$locking_query"
113
114         printf "      - Keep lock of type: %s on " "$lock_type"
115         if [[ "$lock_type" == "relation" ]]
116         then
117             printf "table %s" "$lock_table"
118         elif [[ "$lock_type" == "advisory" ]]
119         then
120             printf "values (%d, %d) (type %d)" "$lock_class" "$lock_objid" "$lock_objsubid"
121         elif [[ "$lock_type" == "virtualxid" ]]
122         then
123             printf "virtual transaction #%s" "$lock_virtualxid"
124         elif [[ "$lock_type" == "transactionid" ]]
125         then
126             printf "transaction #%s" "$lock_transactionid"
127         fi
128         echo
129
130         printf "      - Running for %d seconds.\n" "$locking_runtime"
131         printf "      - Backend / transaction / query start: %s / %s / %s\n" "$locking_backend_start" "$locking_xact_start" "$locking_start"
132         printf "      - locking user and application: %s / %s\n" "$locking_user" "$locking_app"
133         printf "      - Connection from: %s (remote port %s)\n" "$locking_ip" "$locking_port"
134         if [[ "$locking_ip" == "unix socket" ]]
135         then
136             printf "        - Connection from unix socket cannot be traced back to origin.\n"
137         else
138             show_remote_data "$locking_ip" "$locking_port" | sed 's/^/        - /'
139         fi
140     done <<< "$locking_queries"
141
142     echo
143 done <<< "$locked_queries"
Note: See TracBrowser for help on using the browser.