Understanding how oracle internally manages the transactions is very crucial for your applications. Dealing with the database as a black box (sending queries and receiving results), will end up with bad performance to your application or/and non-scalable application.
Oracle uses locks to internally manages the concurrent access to data.
Only one transaction can modify any piece of data (e.g. one row) at any given point of time.
So, “locks” are the internal mechanism that oracle uses to allow concurrency.
If “locks” are overused or used in a wrong or improper way, your application will provide low level of concurrency.
So, understanding how locks is used is very vital to develop a correct and scalable application.
Please also note that each database vendor implements locking in its own way, so, you cannot design one application to run on many databases and getting the same level of performance.
Following is a high-level summary of how Oracle manages locking internally:
– No locks put when user is just reading data.
– Oracle locks data at the row level, when user updates the data, no lock escalation to table or block level (only locking the modified rows).
– The user updating data doesn’t block a user reading the same data.
– A user modifying data will only be blocked if another user modifying the same data at the same time.
Following is a quick demo about these rules:
You should understand this policy when you develop your application. These rules are unique to Oracle. If you are going to use another database like: SQL Server, DB2,…, please refer to the database vendor documentation to understand how the vendor is implementing locking.
Again, this is just high-level overview about this topics, there a lot of details that we may touch in future posts.
Regards
Ahmed
Ahmed AbdelFattah Blog ! 
Please explain this:
It has been told that only 1 user can manipulate a row data at one single point of time say T1 while noother user can manipulate the same row data at that time T1.
I understand this very well but the below confuses me ” A user modifying data will only be blocked if another user modifying the same data at the same time.”
Please explain in detail with an example.
Thanks and regards,
Avinash
LikeLike
In a simple word: Only one user can modify row(s) data at a specific point of time.
I added a quick demo to explain these rules. Note on the demo that when the first session updated one row, the other session had to wait when it tried to modify the same row until the first session issued a “Rollback”.
LikeLiked by 1 person
Please clarify the below with an example:
A user modifying data will only be blocked if another user modifying the same data at the same time.
regards
Avinash V
LikeLiked by 2 people
Hellow Avinash,
If session # 1 is updating a specific row, then any other session that will try to update the same row will have to wait until the first session is either “commit” the update or “rollback” it.
LikeLiked by 1 person
What if a session closes/times out before committing/rolling back? How will the lock on the row be released? Can you please advise me on this?
LikeLiked by 1 person
PMON process will detect this type of session failure and will release any locks.
LikeLiked by 1 person