Postgres Pro
Downloads
Home   >   mailing lists

Re: Deadlocks and transactions - Mailing list pgsql-novice

From JORGE MALDONADO
Subject Re: Deadlocks and transactions
Date
Msg-id CAAY=A78Dr2CqgiFV5LHGX+BiTxdOxhKpb3fFQXFXZcrwV036BQ@mail.gmail.com
Whole thread Raw
In response to Re: Deadlocks and transactions  (Lætitia Avrot <laetitia.avrot@gmail.com>)
Responses Re: Deadlocks and transactions  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Deadlocks and transactions  (David Rowley <david.rowley@2ndquadrant.com>)
List pgsql-novice
Tree view
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


On Tue, Mar 20, 2018 at 4:01 AM, Lætitia Avrot <laetitia.avrot@gmail.com> wrote:
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

2018-03-19 23:18 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:
On Mon, Mar 19, 2018 at 2:46 PM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
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.



pgsql-novice by date:

Previous
From: Stephen Froehlich
Date:
Subject: Replicate a table through a client?
Next
From: "David G. Johnston"
Date:
Subject: Re: Deadlocks and transactions