Re: Dealing with locking in bulk update scenario

  • From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
  • To: <passionate_programmer@xxxxxxxxxxx>
  • Date: Tue, 2 Nov 2010 10:58:12 +0100

RP Khare pisze:
> 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.
> ..................Regards,Rohit P. Khare
>                                         
If it is really so simple, You need some kind of locking in Your
application (what is strange for me it should already be built-in) -
pessimistic (ie. for example SELECT FOR UPDATE) or optimistic (column
VERSION updated with every update or using Oracle's built-in ORA_ROWSCN,
which changes anyway in a WHERE clause).
Unless this is more complicated (such application should be built with
row locking obligatory) and there is something more than what You just
describe here

Regards
Remigiusz

-- 
Pole nakazi

----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos   : DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77

Nordea Bank Polska S.A. z siedzib? w Gdyni, ul. Kielecka 2, 81-303 Gdynia, 
wpisan? do Rejestru Przedsi?biorców Krajowego Rejestru S?dowego pod numerem: 
0000021828, 
dla której dokumentacj? przechowuje S?d Rejonowy Gda?sk - Pó?noc w Gda?sku, 
VIII Wydzia? Gospodarczy Krajowego Rejestru S?dowego, 
o kapitale zak?adowym i wp?aconym w wysoko?ci: 227.593.500,00 z?otych, 
NIP: 586-000-78-20, REGON: 190024711
--
//www.freelists.org/webpage/oracle-l


Other related posts: