1

I'm stumped by a simple query that takes way too long to update a single row by primary key. It only happens to some rows and not all the time. It's sporadic - for instance, it could affect id=182 for 5-10 mins now and then affect id=245 10 mins later. Application is lock-free and I've used this wiki to verify there are no locks. When a row is affected, it remains that way for a couple of mins.

Table has ~20K entries and the entire database takes ~500MB on disk. Client has a statement timeout of 15s, so updates to affected rows causes transaction to rollback.

PG version 9.6.21

Table definition:

create table shift (
    id         serial not null constraint shift_pkey primary key,
    created_at timestamp default now() not null,
    updated_at timestamp default now() not null,
    supervisor varchar(120),
    location_id integer,
    position_id integer
    -- additional 12 numeric and integer fields that are not indexed

);

create index ix_shift_position_id
    on shift (position_id);

create index ix_shift_location_id
    on shift (location_id);

Update query:

UPDATE shift SET updated_at=now(), supervisor='Mr.Bean' WHERE shift.id = 12321

What should I do to troubleshoot/fix this issue?

6
  • That is the full definition? No other indices eg? Jul 26, 2021 at 23:24
  • work_shift? Please fix your invalid statement. Aside from that, there must be something else at play. The UPDATE you show should not get anywhere near a timeout of 15 sec. Triggers? Indexes? FK dependencies? Also, Postgres 9.6 reaches EOL this year. Consider upgrading to a current version. Jul 26, 2021 at 23:45
  • Also, there are no locks cannot be entirely true, as the UPDATE you show takes a write lock on affected rows. If there can be multiple concurrent transactions, locks can be the issue. Are you running multiple statements in a single transaction? Jul 26, 2021 at 23:52
  • Despite what you say about there being no locks, this sounds very much like lock contention to me. Like someone trying to edit the table data in pgadmin or something.
    – mustaccio
    Jul 26, 2021 at 23:52
  • 1. Table has 2 indices. Updated in definition. 2. No triggers in app. 3. Application has foreign key columns but FK constraint isn't set. FK is just column + index as shown in the definition. 4. There are two statements in this transaction - one to read the row by PK and another to update it. 5. There are concurrent transactions all with default transaction isolation level. Rows from this table is updated 5-10 times a day. 6. There is definitely people hand editing data in pgadmin but this it's very rare for this table. Is there an easy way to tell if it's a lock issue?
    – Abs
    Jul 27, 2021 at 8:02

1 Answer 1

1

While the application is lock-free (no explicit lock), UPDATE statement locks the row. Using the wiki pointed in the problem along with helpful tips from this community, a long running background process was identified as the culprit.

The background process, using SQLAlchemy ORM, was expected to make changes to the hundreds of records in memory. After all the changes, the process was thought to flush (invoke UPDATE statement) and commit. However, the changes were flushed immediately after each record was modified and the long duration between flush and commit meant records were locked for extended periods of time.

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.