tags: - postgresql categories: - informational comments: true

date: 2022-04-10 00:00:00

DESCRIPTION

Monitor table locks by sending lock stats via collectd to riemann

ERRORS

VERIFICATION

COMMANDS

Create mock database and load data

Console 1

mgw clone /dbmon
cd dbmon
mgw testservice

Console 2

mkdir yugabyte
cd yugabyte
curl -O https://downloads.yugabyte.com/releases/2.11.2.0/yugabyte-2.11.2.0-b89-darwin-x86_64.tar.gz
tar xzf yugabyte-2.11.2.0-b89-darwin-x86_64.tar.gz yugabyte-2.11.2.0/share/
cd yugabyte-2.11.2.0

export PGPASSWORD="welcome"
psql -h 127.26.210.54 -d billing -U billing_owner -f share/schema.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/products.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/users.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/orders.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/reviews.sql

Transaction not completed - no end/commit sent

Console 3

psql -h 127.26.210.54 -d billing -U billing_owner

billing=>begin;
BEGIN
billing=> update orders set user_id = 112 where id = 5;
UPDATE 1
billing=>

Attempt to update same record

Console 4

psql -h 127.26.210.54 -d billing -U billing_owner

billing=>begin;
BEGIN
billing=> update orders set user_id = 114 where id = 5;

Monitor script as database owner

Monitor script as dbmon owner

psql -h 127.26.210.54 -d billing -U dbmon

\x
SELECT COUNT(mode) AS count, mode
FROM pg_locks
GROUP BY mode
UNION SELECT COUNT(*) AS count, 'waiting' AS mode
FROM pg_locks
WHERE granted is false ;

count |       mode       
-------+------------------
     1 | waiting
     1 | ShareLock
     1 | AccessShareLock
     4 | RowExclusiveLock
     6 | ExclusiveLock
(5 rows)

When there are no locks

count |      mode       
-------+-----------------
     1 | AccessShareLock
     1 | ExclusiveLock
     0 | waiting

Other lock types

AccessExclusiveLock AccessShareLock ExclusiveLock RowExclusiveLock RowShareLock ShareLock ShareRowExclusiveLock ShareUpdateExclusiveLock

Details from `pg_locks` table

Admin to login and run query to get details

select * from pg_locks;

 locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fastpath 
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
 relation      |    16391 |    24605 |      |       |            |               |         |       |          | 6/3                | 16947 | RowExclusiveLock | t       | t
 relation      |    16391 |    24601 |      |       |            |               |         |       |          | 6/3                | 16947 | RowExclusiveLock | t       | t
 virtualxid    |          |          |      |       | 6/3        |               |         |       |          | 6/3                | 16947 | ExclusiveLock    | t       | t
 relation      |    16391 |    24605 |      |       |            |               |         |       |          | 5/7                | 16918 | RowExclusiveLock | t       | t
 relation      |    16391 |    24601 |      |       |            |               |         |       |          | 5/7                | 16918 | RowExclusiveLock | t       | t
 virtualxid    |          |          |      |       | 5/7        |               |         |       |          | 5/7                | 16918 | ExclusiveLock    | t       | t
 relation      |    16391 |    11673 |      |       |            |               |         |       |          | 4/63               | 16885 | AccessShareLock  | t       | t
 virtualxid    |          |          |      |       | 4/63       |               |         |       |          | 4/63               | 16885 | ExclusiveLock    | t       | t
 transactionid |          |          |      |       |            |         24520 |         |       |          | 5/7                | 16918 | ExclusiveLock    | t       | f
 transactionid |          |          |      |       |            |         24520 |         |       |          | 6/3                | 16947 | ShareLock        | f       | f
 tuple         |    16391 |    24601 |    0 |     5 |            |               |         |       |          | 6/3                | 16947 | ExclusiveLock    | t       | f
 transactionid |          |          |      |       |            |         24521 |         |       |          | 6/3                | 16947 | ExclusiveLock    | t       | f
(12 rows)

References

https://docs.yugabyte.com/latest/explore/query-1-performance/pg-stat-activity/

https://download.yugabyte.com/#macos

https://stackoverflow.com/questions/22775150/how-to-simulate-deadlock-in-postgresql

https://wiki.postgresql.org/wiki/Lock_Monitoring