RE: Dealing with locking in bulk update scenario
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <passionate_programmer@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Nov 2010 14:13:57 -0400
Rohit,
Same problem as I stated before. Since the update of the timestamp
column happens after your transaction starts your blind to the update.
Dick Goulet
Senior Oracle DBA
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of RP Khare
Sent: Tuesday, November 02, 2010 11:36 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Dealing with locking in bulk update scenario
Hi Professionals,
One possible solution that flashes my mind is to request my DBA to add a
TimeStamp column and also create a trigger. The trigger's job would be
to match the time-stamp before each update. If the in-memory time-stamp
and the existing time-stamp mismatches, it must not update that row and
log the event. Updates on such rows must be performed after this batch
update job is over.
Just a view. Let me know your views.
..................
Rohit P. Khare
________________________________
From: passionate_programmer@xxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: Dealing with locking in bulk update scenario
Date: Tue, 2 Nov 2010 15:13:46 +0530
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
Other related posts:
