Database Reference
In-Depth Information
Chapter 1
Getting Started
I spend a great deal of time working with Oracle technology. Often I'm called in to assist with diagnosing and
resolving performance issues. Many of the applications I've worked with have experienced problems in part due
to the developers (and to some degree database administrators) treating the database as if it was a black box. In
other words, the team hadn't spent any time becoming familiar with the database technology that was at the core
of their application. In this regard, a fundamental piece of advice I have is do not treat the database as a nebulous
piece of software to which you simply feed queries and receive results. The database is the most critical piece of
most applications. Trying to ignore its internal workings and database vendor-specific features results architectural
decisions from which high performance cannot be achieved.
Having said that, at the core of understanding how a database works is a solid comprehension of how its
transactional control mechanisms are implemented. The key to gaining maximum utility from an Oracle database is
based on understanding how Oracle concurrently manages transactions while simultaneously providing consistent
point-in-time results to queries. This knowledge forms the foundation from which you can make intelligent decisions
resulting in highly concurrent and well-performing applications. Also important is that every database vendor
implements transaction and concurrency control features differently. If you don't recognize this, your database will
give “wrong” answers and you will have large contention issues, leading to poor performance and limited scalability.
Background
There are several topics underpinning how Oracle handles concurrent access to data. I've divided these into the
following categories: locking, concurrency control, multiversioning, transactions, and redo and undo. These features
are the focus of this topic. Since these concepts are all interrelated, it's difficult to pick which topic to discuss first.
For example, in order to discuss locking, you have to understand what a transaction is, and vice versa. Keeping that in
mind, I'll start with a brief introduction to locking, and then move on to the other related subjects. This will also be the
order in which we cover these topics in subsequent chapters in this topic.
Locking
The database uses
locks
to ensure that, at most, one transaction is modifying a given piece of data at any given time.
Basically, locks are the mechanism that allows for concurrency—without some locking model to prevent concurrent
updates to the same row, for example, multiuser access would not be possible in a database. However, if overused or
used improperly, locks can actually inhibit concurrency. If you or the database itself locks data unnecessarily, fewer
people will be able to concurrently perform operations. Thus, understanding what locking is and how it works in your
database is vital if you are to develop a scalable, correct application.
What is also vital is that you understand that each database implements locking differently. Some have page-level
locking, others row-level; some implementations escalate locks from row level to page level, some do not; some use
read locks, others don't; some implement serializable transactions via locking and others via read-consistent views
