-- Thanks for the question regarding "concurrency", version 9iR2

You Asked

Hello Tom,

I have the following question.

Suppose that we have 3 active sessions executing the same query:

UPDATE t
SET value = value + 1
WHERE name = 'U';

For example, the "value" column has the value of 10.

If the 1st session execute the statement but not commit (value = 10), then the 2nd session execute the statement and commit (value = 11), and then the 3th session execute the statement and commit (value = 12)... when the 1st session wants to commit, the data in the "value" column would be again 10 right?

How we can prevent things like this in a concurrent environment and prevent implementing a solution that cause locking issues?

Thanks

and we said...

it would not work like that at all.

Session 1: update t set value = value+1 where name = 'U'

that would get an exclusive lock on that row, other sessions can query but certainly MAY NOT modify it

session 1 would see the value 10 in the table, every other session in the database would see the last committed version - 9 - in that table

session 2: update t set value = value+1 where name = 'U' - this would block, the update cannot proceed, that row it locked. session 2 will enqueue on session 1.


session 1 would continue to see the value 10 in the table, every other session in the database would continue to see the last committed version - 9 - in that table

session 3: udpate t set value = value+1 where name = 'U' - this would block and this session 3 would enqueue on session 2 (get in line behind)



session 1 would continue to see the value 10 in the table, every other session in the database would continue to see the last committed version - 9 - in that table


When session 1 commits, session 2 will unblock.



Now everyone EXCEPT session 2 will be able to see 10 in the table, session 2 will see 11 since it just updated it. But only session 2 will see 11


When session 2 commits, session 3 will unblock.


Now everyone EXCEPT session 3 will be able to see 11 in the table, session 3 will see 12 since it just updated it. But only session 3 will see 12


When session 3 commits, everyone will see 12 in the table.




You should get the server concepts guide (free on otn.oracle.com) and read it from start to finish. This is the very basic of basics as far as concurrency control goes.


If you like the way I say things - my book Expert Oracle Database Architecture explains locking, concurrency controls, multi-versioning, read consistency in in depth detail.

Reviews
August 5, 2009 - 1pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  Great Explanation!!! Use Sequences   August 21, 2009 - 3pm Central time zone Bookmark | Bottom | Top Reviewer: Sam  Use an Oracle sequence instead of using a "Sequence Like" table.

---

Write a Review