0

I'm having a problem where a couple UPDATE sql statements are locking up on a particular table in a database. For some reason these statements seem to be requiring access to a "ShareLock" and its not being granted. From looking at Postgres documentation a Sharelock should only be used when a CREATE INDEX is being run. However, none of the statements/queries in my application make of CREATE INDEX. Could it be triggered implicitly somehow?

The update in question is as follows:

<update id="updateHealth">
        UPDATE table d
        SET fk_table_health_status_id = 
            (SELECT pk_table_health_status_id 
            FROM stat_table_health_status sdhs
            WHERE sdhs.table_health_status = #{health})
            <if test="health == 'Failed' || health == 'Other Failed'">
            ,last_failure = NOW(),
            failure_count = failure_count + 1</if>
        WHERE d.name = #{name}
    </update>

From what I can tell this statement alone shouldn't cause can serious issues. I've synchronized the code so that no two threads can write to this table at the same time. So I'm kind of at a loss at how this is happening....

In my attempts to debug this I've been running this query:

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         LEFT(a.query, 20),
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

This tells a better story about what's happening but if I'm being completely honest I don't entirely understand it (not a dba, just a developer voluntold to fix a problem). As you can see from the table below there is a transaction attempting to access a ShareLock on the dyn_device table and it is not being Granted. However that transaction appears to be separate from the normal UPDATE statement (and yet its still that same query...). I also noticed that the relation for that transaction is null. Not entirely sure why that's happening either.

So does anyone have an idea why this might be happening? Or maybe make more sense of this pg_stat_activity query? Any help would be appreciated.

If you want a better idea the software making the call, I wrote up a stackoverflow question with a more java centic focus .

https://stackoverflow.com/questions/69746549/idle-transactions-mybatis-jboss-6-4-postgres-9-6?noredirect=1#comment123288064_69746549

"dbname" "relation" "transactionid" "mode" "granted" "usename" "query" "query_start" "age" "pid"
"table" "akey_unique_silence_rel_device_relation" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_u
nique_stringset_name" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_silence_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_silence_status" NULL "RowShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" NULL "12849" "ExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stringset_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stringset_pkey" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "string_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_string" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_string" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_device_name" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_pool_category" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_silence_connection_parameter_value" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "template_rel_template_function_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_connection_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_stringset_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "pk_string_status_id" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_stringset_rel_string_relation" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_silence_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_silence_name" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_from_device_to_device" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_stringset_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_availability_status_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_device_availability_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_device_health_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_health_status_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_device_health_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_type_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_device_type_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "template_rel_device_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_device_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_device_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_template_rel_device" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_availability_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_health_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_type" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_device" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" NULL NULL "ExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_silence_connection_parameter" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_silence_conn_param_function_group" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_device_rel_comm_parameter_relation" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_type" NULL "RowShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_template_function" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_template" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "message_log_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_type_rel_device_parameter" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "pool_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_system_configuration" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_function_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_silence_connection_parameter_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_pkey" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_rel_comm_parameter_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "priority_group_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_silence_function" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "string_rel_template_connection_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_connection_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_silence_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_status_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_priority_group" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_stringset" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_stringset" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_rel_comm_parameter" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_connection_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_template_rel_template_function" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_string_name_active" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_string_name_active" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_template_function_pair" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_connection_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_silence_connection_parameter_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_string_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_string_status" NULL "RowShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_stringset_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_stringset_status" NULL "RowShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stringset_status_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_configuration_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_availability_status" NULL "RowShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_device_name" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_parameter_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_health_status" NULL "RowShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_parameter" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_device" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_priority_group" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_silence_function" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_connection" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "comm_parameter_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "connection_status_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_device_type_parameter_pair" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_rel_device_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "template_function_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_device_availability_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_system_configuration" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_comm_parameter" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_string_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_replacement_mode_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_device_parameter_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_connection_parameter_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_template_function" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "pool_category_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_pool" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_silence_status" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "connection_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_template_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "message_log_pk_message_log_id_seq" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "system_configuration_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_silence_function" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_silence" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_silence" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_template_function" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "string_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "string_pkey" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_pool_category_function_pair" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "silence_connection_parameter_value_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_device_replacement_mode" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_template_rel_connection" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "event_log_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "message_log" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "message_log" NULL "RowShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "message_log" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_template_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_device_replacement_mode" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stringset_rel_string_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "template_rel_connection_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_device_replacement_mode" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_category_function_pair" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "akey_unique_string_connection_pair" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "template_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_string_rel_template_connection" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_type_rel_device_parameter_pkey" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_silence_rel_device" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_pkey" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "event_log" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "event_log" NULL "RowExclusiveLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "stat_silence_connection" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "dyn_stringset_rel_string" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "idx_unique_stringset_name" NULL "AccessShareLock" True "user" "INSERT INTO message_" "2021-11-03 12:08:44.300988-04" "00:00:09.479979" 7528
"table" "device_pkey" NULL "RowExclusiveLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" NULL "12857" "ExclusiveLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "idx_unique_device_name" NULL "RowExclusiveLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" NULL "12849" "ShareLock" False "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "akey_unique_device_health_status" NULL "AccessShareLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "dyn_device" NULL "ExclusiveLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "device_health_status_pkey" NULL "AccessShareLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "idx_unique_device_health_status" NULL "AccessShareLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "stat_device_health_status" NULL "AccessShareLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "dyn_device" NULL "RowExclusiveLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" NULL NULL "ExclusiveLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "akey_unique_device_name" NULL "RowExclusiveLock" True "user" "UPDATE dyn_device d" "2021-11-03 12:08:44.312693-04" "00:00:09.468274" 12132
"table" "pg_authid_rolname_index" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" NULL NULL "ExclusiveLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" "pg_authid_oid_index" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" "pg_authid" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" "pg_database" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" "pg_database_datname_index" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" "pg_locks" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" "pg_database_oid_index" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
"table" "pg_stat_activity" NULL "AccessShareLock" True "postgres" "SELECT a.datname " "2021-11-03 12:08:53.780967-04" "00:00:00"
Improve this question

2 Answers 2

Reset to default
1

The documentation you allude to is for a ShareLock on a table. But this is not a table lock, it is a transaction lock. That is, it is waiting for another transaction to end. (Each session owns an ExclusiveLock on it own transactionid, and different sessions wait for it by requesting the corresponding ShareLock)

This is a row lock. One session can have locks on millions of rows at the same time. Rather than entering each one into the lock table held in RAM, conflicts are recorded as waiting for the other transaction to end, rather than waiting for a specific rowlock to end.

The table you reproduce here is rather garbled. The columns shift over part way down, and I suspect rows are missing as well. There should be another row with a transaction id of "12849".

Improve this answer
5
  • You are correct there are rows missing. There is also another row with the same transaction Id. I dismissed it because that particular statement it referencing is an INSERT too a completely separate logging table (Although the rows missing do reference many relations that I don't understand why it needs). So you're saying that two separate sessions are trying to access the same transaction ID at the same time? Or that both transactions are colliding on the row lock?
    – Cody Herbst
    Nov 4, 2021 at 10:38
  • I updated the table to include some of the additional rows (there are many more but I dont want to make the table too long). One thing that sticks out to me is the process Id is different between the two transactions... How does that happen...
    – Cody Herbst
    Nov 4, 2021 at 12:57
  • I updated the table with the full query result. Little much but I'm not sure there's a better way to post the data. 12849 is still there just further down.
    – Cody Herbst
    Nov 4, 2021 at 14:10
  • The query reported is the one that that session is currently running, or (if idle in transaction) most recently ran. So while PID 7528 is currently doing an INSERT on a separate table, it had previously in the same transaction done something to acquire a row lock on a row in (presumably) dyn_device and still holds that lock.
    – jjanes
    Nov 4, 2021 at 19:40
  • The process IDs must be different. To be blocked, you need a blocker and a blockee. PostgreSQL doesn't allow a process to block itself.
    – jjanes
    Nov 4, 2021 at 19:46
0

So I discovered what the problem was. The issue really wasn't the database's fault or even the queries that were being used. It turns out that our system was using the same Transaction subsystem for both it our Data Source (Postgres Database) and our JMS messaging system. When a JMS message was sent, it created a transaction and every transactional based action that followed during the life cycle of that tread/transaction would be treated as part of that transaction. Which includes all of our database calls.....

This explains why a query as simple as insert into a message log was touching all of our relations in the database. The debug queries only showed me the first query, not all of the others that were used during the life cycle of the JMS message. There were several ways to fix this but my team opted for the easiest which was preventing the Data Source from using the JBoss provided Transaction Manager.

<datasource jndi-name="java:jboss/datasources/PostgresDS" jta="false" pool-name="PostgresDS" enabled="true" use-java-context="true">

Improve this answer

Your Answer

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

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