| Updating table through concurrent sessions [message #493131] |
Sat, 05 February 2011 05:55  |
 |
arunb1982
Messages: 34
Registered: December 2010
Location: Bangalore
|
Member |
|
|
Hi ,
I have a table with counter value which will be incremented or decremented by several application servers.
SQL> select * from test;
COUNTER
----------
10
Application servers(multiple servers) will be running update against this row for increasing the counter value or decreasing the counter value.
update test set counter=counter+1;
update test set counter=counter-1;
update test set counter=counter+1;
update test set counter=counter+1;
So when update happens concurrently to this table will the counter value gets messed up?
I did a small test by opening multiple sessions for running update and the result I got for above update statement was 11,10,11,12.
But our developer is bit skeptical about this approach and he is using select for update and then updating the row.
Which approach will be better?
Thanks,
Arun
[Updated on: Sat, 05 February 2011 06:06] Report message to a moderator |
|
|
|
| Re: Updating table through concurrent sessions [message #493135 is a reply to message #493131] |
Sat, 05 February 2011 07:01  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
arunb1982 wrote on Sat, 05 February 2011 12:55So when update happens concurrently to this table will the counter value gets messed up?
No, when two or more sessions will try to update the same row, the first will do it and the other ones will wait until the session updating the row ends its transaction (issue COMMIT/ROLLBACK).
arunb1982 wrote on Sat, 05 February 2011 12:55I did a small test by opening multiple sessions for running update and the result I got for above update statement was 11,10,11,12.
How exactly did you get these figures? It might return different value than the updated one if queried with additional SELECT after the transaction end (COMMIT). As UPDATE statement supports RETURNING clause (at least from version 8.1.7), I would use it. UPDATE test
SET counter = counter + 1
RETURNING counter INTO <your variable>; |
|
|
|
|
|
|
|
| Re: Updating table through concurrent sessions [message #493224 is a reply to message #493131] |
Sun, 06 February 2011 21:51 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
>for increasing the counter value or decreasing the counter value
Increasing OR DECREASING the counter value ?
So what happens when the counter get's reset to a previous
value ?
Say :
Application A increased the value from 10 to 11.
Application B, next in the queue, increased it to 12.
Application C decreased it to 11.
Application A comes back and increases it to 12 again ?
You have had the values 11 and 12 set twice. Do the applications read the COUNTER ? Does some other "master" application read the COUNTER ? So, how do you handle the same value having appeared twice ?
Hemant K Chitale
|
|
|
|
| Re: Updating table through concurrent sessions [message #493330 is a reply to message #493131] |
Mon, 07 February 2011 06:03 |
|
arunb1982
Messages: 34
Registered: December 2010
Location: Bangalore
|
Member |
|
|
Hi Hemanth,
Thanks for the response.
for increasing the counter value or decreasing the counter value
Increasing OR DECREASING the counter value ?
>Counter will be column which is gives you the no of jobs processing by application servers.
We will be having several application servers to which jobs are assigned and each server will be updating the counter as when jobs gets finished or assigned.
So what happens when the counter get's reset to a previous
value ?
Value in counter will be updated by server.When job is finished value decreases in counter column and when jobs gets assigned to server,value in counter increases.
1000 of jobs will be assigned to servers.So in an interval of milliseconds updates will be happening to counter column.
Say :
Application A increased the value from 10 to 11.
Application B, next in the queue, increased it to 12.
Application C decreased it to 11.
Application A comes back and increases it to 12 again ?
You have had the values 11 and 12 set twice. Do the applications read the COUNTER ? Does some other "master" application read the COUNTER ? So, how do you handle the same value having appeared twice ?
Application servers updates the counter and there is no master application to do the update. Each application servers will be doing the update to the common counter table located in database.
Example:
Value of counter=10
Application server 01, fires update table test set counter = counter +1 =>11
Application server 02, fires update table test set counter = counter -1 =>10
Application server 03, fires update table test set counter = counter +1 =>11
Application server 04, fires update table test set counter = counter +1 =>12
So when several servers are accessing the same table and doing the update based on the job assigned or finished where updates are happening in milliseconds interval, developer is skeptical whether it will mess up the counter value.( Will it take a old counter value and do the update instead of taking the latest counter value)
As far as I know, oracle will be doing this serially and there will be a rowlock contention happening.
And as of now application is designed to run two sql statements to do it.Select for update + update.
Hi Michel,
Hope now it's clear for you.
Thanks,
Arun
|
|
|
|
| Re: Updating table through concurrent sessions [message #493335 is a reply to message #493330] |
Mon, 07 February 2011 06:28 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Your developer needs to read up on how db's work.
As long as you do
update table test set counter = counter +1
or the equivalent minus it'll always be right - though you will have locking issues if you're not careful.
If you read the current value of counter in a select and then try an use that in a seperate update statement:
update table test set counter = <previously selected value> +1
[Updated on: Mon, 07 February 2011 06:28] Report message to a moderator |
|
|
|
| Re: Updating table through concurrent sessions [message #493337 is a reply to message #493335] |
Mon, 07 February 2011 06:31 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
That said, if you get the select the value with select for update, you'll still be safe as no-one else can modify the row between the select and the update. So what you're doing at the moment will work as well. It's just more code than necessary.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
