0

Based on the 2nd answer on https://stackoverflow.com/questions/22775150/how-to-simulate-deadlock-in-postgresql I performed the following snippet:

Transaction 1                    Transaction 2
BEGIN;
                                 BEGIN;
SELECT salary1 
FROM   deadlock_demonstration
WHERE  worker_id = 1
FOR    UPDATE;
                                 SELECT salary1 
                                 FROM   deadlock_demonstration
                                 WHERE  worker_id = 2
                                 FOR    UPDATE;
UPDATE deadlock_demonstration
SET    salary1 = 100
WHERE  worker_id = 2;

                                 UPDATE deadlock_demonstration
                                 SET    salary1 = 100
                                 WHERE  worker_id = 1;

                    --> ... 💣 deadlock!

Now, following the guidelines on https://wiki.postgresql.org/wiki/Lock_Monitoring I performed

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,
        blocked_activity.application_name  AS blocked_application,
        blocking_activity.application_name AS blocking_application
FROM    pg_catalog.pg_locks                     blocked_locks
        inner JOIN pg_catalog.pg_stat_activity  blocked_activity    on  blocked_activity.pid                =   blocked_locks.pid
        inner 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
        INNER JOIN pg_catalog.pg_stat_activity  blocking_activity   ON  blocking_activity.pid               =   blocking_locks.pid
WHERE   NOT blocked_locks.GRANTED

, expecting to see the correct values for the columns blocked_statement and current_statement_in_blocking_process, only to found the blocking statement was SHOW search_path.

Is there any way to see both blocked and blocking statements or pid's?

How shall I correct the above query?

Thanks in advanced!!!

2
  • Unless state = 'active' in pg_stat_activity the statement you see, is the last statement that session ran. It's not necessarily the blocking statement. I don't think there is a way you can show the statement that acquired the lock (it could be the first one from thousands of statements that were ran in that session).
    – user1822
    Commented Sep 21, 2021 at 6:48
  • ouch... anyway, my ultimate client shall be a backend, not dbeaver, so i can live with it. Why don't you response as an answer so i can vote it, @a_horse_with_no_name ?
    – glezo
    Commented Sep 22, 2021 at 15:45

1 Answer 1

1

Whatever client you are using to send your queries to the database decides to send "SHOW search_path" whenever it is bored.

How shall I correct the above query?

Stop using that client.

2
  • I'm using dbeaver community edition 7.3.3.202101161839. Despite dbeaver certainly throwing weir queries, shouldn't postgresql engine detect that SHOW search_path is not locked, but second query in the second transaction is locked?
    – glezo
    Commented Sep 21, 2021 at 6:12
  • Hmmm. I've used dbeaver community 21.2.1.202109181446 and didn't notice it doing that. Maybe it is version dependent or depends on some setting. Anyway, it is not blocked, it is blocking. Or as the column name says with much precision, 'current_statement_in_blocking_process'. The database doesn't keep track of all statements on a connection, only the most recent one.
    – jjanes
    Commented Sep 21, 2021 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.