1

I'm trying to understand how Postgresql under the hood is implementing a row lock so that I understand what resource implication this has.

If there are two database transactions. One of them has locked a row. The other database transaction is waiting to release the lock on that row. The transaction which is waiting for the release what resources will this consume?

Background: I would like to understand whether the waiting has a real impact on the resource consumption or whether such a lock situation provides just slow responses to the client.

Improve this question
2
  • “The other database transaction is waiting to release the lock on that row.” — do you mean ‘…waiting for the lock on that row to be released’? As written it implies that the other transaction already has that lock (which can't be true).
    – gidds
    Jul 11, 2022 at 16:41
  • I mean that one transaction is waiting for the other to release the lock. I do not talk about a deadlock situation.
    – Thomas Hunziker
    Jul 11, 2022 at 20:09

1 Answer 1

Reset to default
3

The waiting session will hold on to all memory resources it's acquired so far; it will consume very little CPU, sleeping while waiting on the lock; and no I/O.

Improve this answer
2
  • PostgreSQL does not spin on the lock, otherwise fine.
    – Laurenz Albe
    Jul 11, 2022 at 2:27
  • @mustaccio: Does this apply as well when the database waits due to the transaction isolation level?
    – Thomas Hunziker
    Jul 12, 2022 at 4:22

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.