Problem solve Get help with specific problems with your technologies, process and projects.

How writers do not block readers in Oracle

Just a question based on your statement "writers do not block readers" in Oracle. Could you elaborate on how Oracle does that?

Some database systems have writers block readers of the data. So if UserA is writing data to a table, UserB won't be able to read data from that table until UserA is done with her transaction. For a table that experiences a high number of concurrent transactions, this can slow the application down quite a bit. To create a highly-concurrent system, Oracle decided to implement the policy that "writers do not block readers". This policy, and Oracle's implementation of that policy, is one of the things that separates Oracle's RDBMS software from other vendors.

Oracle implements writers not blocking readers by using rollback segments and generating "read-consistent" images. To explain how this happens, we step through a simple example. UserA will be updating rows in a table. When UserA begins updating these rows, information is captured in Oracle's rollback segments. This information includes a form of a timestamp called the System Change Number (SCN), and a before image of the data.

Primarly, the rollback segments are used to roll back, or undo the transaction. But rollback segments are also used to create read-consistent images. Let's suppose that UserA is still in the process up updating (or writing to) the table. Now UserB comes along and wants to just read data from that same table. UserB's transaction begins *after* UserA's transaction. Therefore, UserB has a higher SCN than UserA. When UserB begins reading data, the system notices that UserA is updating this same data. Since UserB has a higher SCN, UserB will use the before-image information in the rollback segment that UserA created. UserB will see data consistent with the time before UserA began changing the data. This is UserB's read-consistent view of the data. No one but UserA will see the changes until UserA commits those changes. Until UserA commits the changes, UserB and other users will still be able to read the data, even though another user is writing the data.

So, it is the rollback segments and SCNs that let Oracle implement the policy that writers never block readers.

For More Information

  • What do you think about this answer? E-mail the editors at [email protected] with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture