Oracle Database Locking Policy – Back to Basics

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

4 thoughts on “Oracle Database Locking Policy – Back to Basics

  1. 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

    Like

    1. 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”.

      Like

  2. 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

    Liked by 1 person

    1. 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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s