Re: Dealing with locking in bulk update scenario
From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
Date: Tue, 2 Nov 2010 10:58:12 +0100
Message-ID: <4CCFE0B4.9000402_at_nordea.com>
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
Date: Tue, 2 Nov 2010 10:58:12 +0100
Message-ID: <4CCFE0B4.9000402_at_nordea.com>
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_at_nordea.com> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 02 2010 - 04:58:12 CDT