Re: Dealing with locking on batch updates.

On 2 November 2010 10:47, RP Khare <passionate_programmer(at)hotmail(dot)com>wrote:

> Hi,
>
> Though the following issue we are facing at present with Oracle 10g
> database, but I just want to know how PostgreSQL would solve this problem.
> We are planning a migration to any open-source RDBMS in future, so just
> wanted to clear this issue.
>
> Let me clear the scenario, the real-life issue that we faced on a very
> large database. Our client is a well-known cell phone service provider.
> Our database has a table that manages records of the current balance left
> on the customer's cell phone account. Among the other columns of the table,
> one column stores the amount of recharge done and one other column manages
> the current active balance left.
>
>
> We have two independent PL/SQL scripts. One script is automatically fired
> when the customer recharges his phone and updates his balance.
> The second script is about deduction certain charges from the customers
> account. This is a batch job as it applies to all the customers. This script
> is scheduled to run at certain intervals of a day. When this script is run,
> it loads 50,000 records in the memory, updates certain columns and performs
> bulk update back to the table.
>
>
> The issue happened is like this:
>
>
> A customer, whose ID is 101, contacted his local shop to get his phone
> recharged. He pays the amount. But till the time his phone was about to
> recharge, the scheduled time of the second script fired the second script.
> The second script loaded the records of 50,000 customers in the memory. In
> this in-memory records, one of the record of this customer too.
>
>
> Till the time the second script's batch update finishes, the first script
> successfully recharged the customer's account.
>
>
> Now what happened is that is the actual table, the column:
> "CurrentAccountBalance" gets updated to 150, but the in-memory records on
> which the second script was working had the customer's old balance i.e, 100.
> The second script had to deduct 10 from the column:
> "CurrentAccountBalance". When, according to actual working, the customer's
> "CurrentAccountBalance" should be 140, this issue made his balance 90.
> Now how to deal with this issue.
>
> I want to know how we can handle this issue in PostgreSQL.
>
> Regards,
> Rohit P. Khare
>

Maybe you should just lock the rows during those operations? Simple select
for update should be enough I think. And Oracle can do that too.

regards
Szymon