RE: Dealing with locking in bulk update scenario

From: <dbvision_at_iinet.net.au>
Date: Wed, 03 Nov 2010 08:52:17 +0800
Message-Id: <37927.1288745537_at_iinet.net.au>



Rohit,

if you're going that way then you're up for a lot of lock management pain. I'd rather use the inherent locking in the database.

If your bulk batch updater needs to change a column, it should do so by reading and locking only the rows it intends to change, then updating them, then releasing the locks by commit.

This can be done in Oracle with select for update followed by the update, or by a set update with suitable predicates.

It all boils down to how your batch script works. If it reads 50K rows but only decides later which ones are to be updated, then you have no choice but to lock all of them on initial read or re-read-and-lock only the change candidates and re-calc at the actual update time.

Both can be achieved without the aid of additional timestamps and will be slower if you use those timestamp columns.

There is no such thing as a free lunch when it comes to concurrency control between OLTP and batch processing: they are not the same and will never be the same.

HTH
Cheers
Nuno
in sunny Sydney, Australia
dbvision_at_iinet.net.au
On Tue Nov 2 23:35 , RP Khare sent:

>
>
>
>
>
>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_at_hotmail.com
>To: oracle-l_at_freelists.org
>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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 02 2010 - 19:52:17 CDT

Original text of this message