CODEWITHSUNDEEP
databasepostgresql
Tin Ng
Tin Ng

Reputation: 1047

Why does postgres lock one table when inserting into another

My source tables called Event sitting in a different database and it has millions of rows. Each event can have an action of DELETE, UPDATE or NEW.

We have a Java process that goes through these events in the order they were created and do all sort of rules and then insert the results into multiple tables for look up, analyse etc..

I am using JdbcTemplate and using batchUpdate to delete and upsert to Postgres DB in a sequential order right now, but I'd like to be able to parallel too. Each batch is 1,000 entities to be insert/upserted or deleted.

However, currently even doing in a sequential manner, Postgres locks queries somehow which I don't know much about and why.

Here are some of the codes

            entityService.deleteBatch(deletedEntities);
            indexingService.deleteBatch(deletedEntities);

...
            entityService.updateBatch(allActiveEntities);
            indexingService.updateBatch(....);

Each of these services are doing insert/delete into different tables. They are in one transaction though.

The following query

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
         JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

returns

Query being blocked: "insert INTO ENTITY (reference, seq, data) VALUES($1, $2, $3) ON CONFLICT ON CONSTRAINT ENTITY_c DO UPDATE SET data = $4",
Blockking query: delete from ENTITY_INDEX where reference = $1

There are no foreign constraints between these tables. And we do have indexes so that we can run queries for our processing as part of the process.

Why would one completely different table can block the other tables? And how can we go about resolving this?

Upvotes: 1

Views: 1192

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

Your query is misleading.

What it shows as “blocking query” is really the last statement that ran in the blocking transaction.

It was probably a previous statement in the same transaction that caused entity (or rather a row in it) to be locked.

Upvotes: 2

Related Questions