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

Possible problem when two clients update the same record?

From
Karsten Hoffrath
Date:
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'm using PostgresSQL 8.0.2 under Windows, the client is written
with Delphi 7 + ZEOS.


Thanks in advance.

Karsten


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

From
"Sean Davis"
Date:
----- 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