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