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.