Re: Possible problem when two clients update the same record?

----- Original Message -----
From: "Karsten Hoffrath" <maillists(at)khoffrath(dot)de>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Saturday, July 23, 2005 10:27 AM
Subject: [NOVICE] Possible problem when two clients update the same record?

> Hi all.
>
> I'm converting a dos based application using DBase files to PostgresSQL.
> The application is a pawn shop system.
>
> The dos application works as follows:
> Table A holds all items which the customers bring to the shop.
> Table B has one record for each day which holds the totals (pieces,
> costs, etc.) for this particular day.
> When B is updated, the particular record gets locked, updated and
> released.
>
> The new application works this way:
> - start a new transaction,
> - insert the new record in A,
> - the insert operation fires a trigger which updates B
> - the transaction is committed.
>
> Now my question:
> What happens if two clients insert a new record to A at the same time?
> Will B get updated correctly or will one value get lost?

I can't see a reason why one value would get lost. There will be two
transactions, each independent of the other. I'm not sure about what would
lead to a deadlock in this case, but I would think that would be pretty
unlikely, as the insert operation and update to B will take milliseconds at
most. B is not locked during the entire transaction, as you describe it
above, I think.

Sean