Re: Dealing with locking in bulk update scenario

Options

1. pessimistic locking: both scripts (especially the batch) should lock each row as they are selected (use select for update). Then they can't overwrite each other's changes. But you have to deal with the case where the select for update fails because one or more of the records are already locked. You might want to look at SELECT .. FOR UPDATE SKIP LOCKED (is
2. optimistic locking: select the data without locking. when you come to update the data, check it hasn't changed since you read it. For example, select the orginal customer balance; the update should succeed only where customer_balance = original_customer_balance:

update account
set balance = :newbalance
where accountid = :accountid
and balance = :originalbalance

You then need a mechanism to identify rows that weren't updated, and to ensure they are (eventually) correctly processed. Remiguisz' suggestion of ORA_ROWSCN is an even more reliable indicator that something has changed - but in some cases you can be more optimistic (you may only care if a subset of columns is updated)

3) incremental update: if your updates are commutative, you may be able to change the updates so that both are taken into account. For example, rather than simply calculating the new balance, calculate the change to the balance:

update account
set balance = balance + :increment where accountid = :accountid;

In this case, two threads can calculate increments (the recharge, cost of calls, whatever) without needing to know what the other is doing.

Best choice will depend on your situation.

Regards Nigel

On 2 November 2010 09:43, RP Khare <passionate_programmer_at_hotmail.com>wrote:

> 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.
>
> <snip>

--
http://www.freelists.org/webpage/oracle-l