Deadlocks and transactions

[JORGE MALDONADO]

I have a process that inserts a record in one table and, after that, a
record in another table is updated. Because there are 2 DB operations, I
decided to perform both of them in a transaction.

Can a deadlock take place even if transactions are used?

Best regards,
Jorge Maldonado

[David G. Johnston]

Post by JORGE MALDONADO
I have a process that inserts a record in one table and, after that, a
record in another table is updated. Because there are 2 DB operations, I
decided to perform both of them in a transaction.
Can a deadlock take place even if transactions are used?

Its impossible to deadlock without transactions​.

Simplistically, a deadlock happens when there are two processes - one holds
lock A and wants lock B while the other wants lock A while holding lock B.

Your choice to use a transaction here is good but you will have at least
some risk of deadlock with others parts of the system. Other processes
running this same exact code, however, should not pose a risk since the
locking order would be consistent.

David J.

[Lætitia Avrot]

Hi Jorge,

Here are the two advices I give developpers to reduce the risk a deadlock
occures :
- Always change(update, delete...) data in the same order
- Keep your transactions short

Cheers,

LÊtitia

Post by David G. Johnston

Post by JORGE MALDONADO
I have a process that inserts a record in one table and, after that, a
record in another table is updated. Because there are 2 DB operations, I
decided to perform both of them in a transaction.
Can a deadlock take place even if transactions are used?

Its impossible to deadlock without transactions​.
Simplistically, a deadlock happens when there are two processes - one
holds lock A and wants lock B while the other wants lock A while holding
lock B.
Your choice to use a transaction here is good but you will have at least
some risk of deadlock with others parts of the system. Other processes
running this same exact code, however, should not pose a risk since the
locking order would be consistent.
David J.

[JORGE MALDONADO]

My application is a website.

LetÂŽs suppose the following scenario.
* User 1 has already loaded a web page and clicks a button that triggers a
transaction that includes 2 tables.
* User 2 loads the same page and SELECTs data from the 2 tables currently
in the transaction generated by User 1. User 2 does not trigger a
transaction because he/she only gets data from the DB.

Is there any issue/problem for User 2?

(Is it correct to reply-to-all when posting back to a question?)

Best regards,
Jorge Maldonado

Post by Lætitia Avrot
Hi Jorge,
Here are the two advices I give developpers to reduce the risk a deadlock
- Always change(update, delete...) data in the same order
- Keep your transactions short
Cheers,
LÊtitia

Post by David G. Johnston

Post by JORGE MALDONADO
I have a process that inserts a record in one table and, after that, a
record in another table is updated. Because there are 2 DB operations, I
decided to perform both of them in a transaction.
Can a deadlock take place even if transactions are used?

Its impossible to deadlock without transactions​.
Simplistically, a deadlock happens when there are two processes - one
holds lock A and wants lock B while the other wants lock A while holding
lock B.
Your choice to use a transaction here is good but you will have at least
some risk of deadlock with others parts of the system. Other processes
running this same exact code, however, should not pose a risk since the
locking order would be consistent.
David J.

[David G. Johnston]

Post by JORGE MALDONADO
My application is a website.
LetÂŽs suppose the following scenario.
* User 1 has already loaded a web page and clicks a button that triggers a
transaction that includes 2 tables.
* User 2 loads the same page and SELECTs data from the 2 tables currently
in the transaction generated by User 1. User 2 does not trigger a
transaction because he/she only gets data from the DB.

​Well, if you want a consistent point-in-time picture of the data in those
two tables you should probably perform the selects in a transaction too.​

Is there any issue/problem for User 2?
MVCC mechanics eliminate the possibility of deadlocking between update
queries and select queries (not select-for-update though). Because of it
deadlocking is only possible between two updating transactions.

https://www.postgresql.org/docs/current/static/mvcc-intro.html

(Is it correct to reply-to-all when posting back to a question?)
​Yes, reply-to-all is preferred; top-posting replies is not. Please inline
or bottom-post (and trim quoting) as appropriate.​

David J.

[David Rowley]

Let´s suppose the following scenario.
* User 1 has already loaded a web page and clicks a button that triggers a
transaction that includes 2 tables.
* User 2 loads the same page and SELECTs data from the 2 tables currently in
the transaction generated by User 1. User 2 does not trigger a transaction
because he/she only gets data from the DB.
Is there any issue/problem for User 2?

There's no deadlock risk between a read and a write transaction. This
is mentioned in the 2nd paragraph in
https://www.postgresql.org/docs/10/static/mvcc-intro.html

That might be interesting for you to read as it explains in a bit of
detail how concurrency is handled in PostgreSQL.

(Is it correct to reply-to-all when posting back to a question?)

Yes. It's also our preference that you don't top post in replies. We
generally use the interleaved style described in
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style