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.

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. Jul 11, 2022 at 20:09

1 Answer 1

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.

2
  • PostgreSQL does not spin on the lock, otherwise fine. Jul 11, 2022 at 2:27
  • @mustaccio: Does this apply as well when the database waits due to the transaction isolation level? 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.