0

I am seeing this error in my RDS logs in Postgresql.

Id column is Primary Key in my table.

I also logged the queries in server site and seen that both statements have different IDs.

Do you have any idea how can I fix this ?

Process 3095 waits for ShareLock on transaction 16391215; blocked by process 17623.
    Process 17623: 
            UPDATE dummytable
            SET latestheartbeat = $1
            WHERE
                id = $2
                AND (
                        latestheartbeat < $1
                        OR latestheartbeat is NULL
                    )
    Process 3095: 
            UPDATE dummytable
            SET latestheartbeat = $1
            WHERE
                id = $2
                AND (
                        latestheartbeat < $1
                        OR latestheartbeat is NULL
                    )
ahmet gül
  • 193
  • 1
  • 11
  • All we can say is that the transactions deadlock on row locks ("waits for ShareLock on transaction"). You'll have to figure out what these transactions are doing. Your application should have got an error, that should help you debug that. – Laurenz Albe Feb 16 '23 at 13:55
  • ID column is unique. Both query should access the different row. How can deadlock happen in that case? – ahmet gül Feb 16 '23 at 13:57
  • have you checked explain query? have you a combined index of if and latestheartbeat and remove the OR use `COALESCE(latestheartbeat , somevalue)` that is samller than $1) – nbk Feb 16 '23 at 14:02
  • The deadlock probably happens because an earlier DML statement in the same transaction locked something else. You'll have to examine the application and figure out what SQL statements are executed in it. If you can afford to log all SQL statements, that may be a way too. – Laurenz Albe Feb 16 '23 at 14:16

0 Answers0