On 2 November 2010 10:47, RP Khare <passionate_program...@hotmail.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
