Dealing with locking on batch updates.
Hi,
Though the following issue we are facing at present with Oracle 10g database, but I just want to know how PostgreSQL would solve this problem. We are planning a migration to any open-source RDBMS in future, so just wanted to clear this issue.
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.
I want to know how we can handle this issue in PostgreSQL.
Regards,
Rohit P. Khare
On 2 November 2010 10:47, RP Khare <passionate_programmer@hotmail.com>wrote:
Hi,
Though the following issue we are facing at present with Oracle 10g
database, but I just want to know how PostgreSQL would solve this problem.
We are planning a migration to any open-source RDBMS in future, so just
wanted to clear this issue.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.I want to know how we can handle this issue in PostgreSQL.
Regards,
Rohit P. Khare
Maybe you should just lock the rows during those operations? Simple select
for update should be enough I think. And Oracle can do that too.
regards
Szymon
On Tue, Nov 2, 2010 at 5:47 AM, RP Khare
<passionate_programmer@hotmail.com> wrote:
I want to know how we can handle this issue in PostgreSQL.
When you load your 50,000 record batch, do it inside a transaction
block and "SELECT FOR UPDATE" the rows instead of plain "SELECT".
That will lock those rows from being updated by other transactions,
which will just wait for your transaction to finish, and conversely,
your transaction will not proceed until the other transactions have
released their update locks as well.
This is the most efficient way you can do locking.