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

----- Original Message -----
From: "Karsten Hoffrath" <maillists@khoffrath.de>
To: <pgsql-novice@postgresql.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