caleb, 1 month ago

@thisismissem Postgres does row-level locking by default so that's one way to get deadlocks with simultaneous transactions. This SO has more details: https://stackoverflow.com/questions/10245560/deadlocks-in-postgresql-when-running-update

thisismissem, 1 month ago

@caleb yeah, I saw that. Not sure how it cab help me though

caleb, 1 month ago

@thisismissem I don't know a lot about your specific situation, but I thought that if you suspected multiple transactions updating the same rows, you might try the SELECT FOR UPDATE approach.

thisismissem, 1 month ago

@caleb maybe. I'll have to look..

caleb, 1 month ago

@thisismissem SELECT FOR UPDATE was a bad suggestion, it won't help (if row-level locks are causing the deadlock), @tomasv is correct.

thisismissem, 1 month ago

I think a source might be because we've two or three processes that can update data in transactions, maybe without enough indexes, and these get interleaved.

Fixing this means "locking" the "store the latest data" job on the "push the latest data" job (currently these are on separate cron schedules and may overlap)

#postgresql

benoit, 1 month ago

@thisismissem Hello. Do you have a specific question about this? You want to get the source of the deadlock? Why it's being aborted?

thisismissem, 1 month ago

@benoit both. I'm seeing these in our logs but our transactions are literally nust inserting or updating records, they should be fast.