Overview

We have all heard that Oracle has a great Locking mechanism, way superior than other databases. While in different databases, readers can block writers and writers can block readers, Oracle doesn't allow that by default. In oracle you can select without a problem (although there is an exception to that rule) and you can write without being afraid that someone is selecting your data.

The hearth of this mechanism is the Multi Versioning. Meaning Oracle keeps several copies of the same data which is consistent with different points of time. For example, one row can be consistent with 12:00 and other row can be consistent with 11:50. Both of these rows are in the buffer cache.

That is why when you start a select you are having a CONSISTENT (with the point of time your query started) GET and when you do DML you are having a CURRENT (the latest version of the data) GET.

In order to protect and achieve that mechanism oracle is using several structures:

• Latches
• Mutexes
• Enqueues (Generally known as Locks)

Let's examine each of the structures now: