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

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 of data (no locks). These small differences can balloon into huge performance issues or downright bugs in your application if you don’t understand how they work.

The following points sum up Oracle’s locking policy:

• Oracle locks data at the row level on modification. There is no lock escalation to a block or table level.
• Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads.
• A writer of data does not block a reader of data. Let me repeat: reads are not blocked by writes. This is fundamentally different from many other databases, where reads are blocked by writes. While this sounds like an extremely positive attribute (and it generally is), if you do not understand this thoroughly and you attempt to enforce integrity constraints in your application via application logic, you are most likely doing it incorrectly.
• A writer of data is blocked only when another writer of data has already locked the row it was going after. A reader of data never blocks a writer of data.

You must take these facts into consideration when developing your application and you must also realize that this policy is unique to Oracle; every database has subtle differences in its approach to locking. Even if you go with lowest common denominator SQL in your applications, the locking and concurrency control models employed by each vendor assure something will be different. A developer who does not understand how his or her database handles concurrency will certainly encounter data integrity issues. (This is particularly common when a developer moves from another database to Oracle, or vice versa, and neglects to take the differing concurrency mechanisms into account in the application.)

Concurrency Control

Concurrency control ensures that no two transactions modify the same piece of data at the same time. This is an area where databases differentiate themselves. Concurrency control is an area that sets a database apart from a file system and databases apart from each other. As a programmer, it is vital that your database application works correctly under concurrent access conditions, and yet time and time again this is something people fail to test. Techniques that work well if everything happens consecutively do not necessarily work so well when everyone does them simultaneously. If you don’t have a good grasp of how your particular database implements concurrency control mechanisms, then you will:

• Corrupt the integrity of your data.
• Have applications run slower than they should with a small number of users.
• Decrease your applications’ ability to scale to a large number of users.

Notice I don’t say, “you might...” or “you run the risk of...” but rather that invariably you will do these things. You will do these things without even realizing it. Without correct concurrency control, you will corrupt the integrity of your database because something that works in isolation will not work as you expect in a multiuser situation. Your application will run slower than it should because you’ll end up waiting for data. Your application will lose its ability to scale because of locking and contention issues. As the queues to access a resource get longer, the wait gets longer and longer.

An analogy here would be a backup at a tollbooth. If cars arrive in an orderly, predictable fashion, one after the other, there won’t ever be a backup. If many cars arrive simultaneously, queues start to form. Furthermore, the waiting time does not increase linearly with the number of cars at the booth. After a certain point, considerable additional time is spent “managing” the people who are waiting in line, as well as servicing them (the parallel in the database would be context switching).

Concurrency issues are the hardest to track down; the problem is similar to debugging a multithreaded program. The program may work fine in the controlled, artificial environment of the debugger, but it crashes horribly in the real world. For example, under race conditions, you find that two threads can end up modifying the same data structure simultaneously. These kinds of bugs are terribly hard to track down and fix. If you only test your application in isolation and then deploy it to dozens of concurrent users, you are likely to be (painfully) exposed to an undetected concurrency issue.

So, if you are used to the way other databases work with respect to query consistency and concurrency, or you never had to grapple with such concepts (i.e., you have no real database experience), you can now see how understanding how this works will be important to you. In order to maximize Oracle’s potential, and to implement correct code, you need to understand these issues as they pertain to Oracle—not how they are implemented in other databases.

Multiversioning

Multiversioning is related to concurrency control, as it forms the foundation for Oracle’s concurrency control mechanism. Oracle operates a multiversion, read-consistent concurrency model. In Chapter 4, we’ll cover the technical aspects in more detail, but, essentially, it is the mechanism by which Oracle provides for the following:

• Read-consistent queries: Queries that produce consistent results with respect to a point in time.
• Nonblocking queries: Queries are never blocked by writers of data, as they are in other databases.

These are two very important concepts in the Oracle database. The term multiversioning basically describes Oracle’s ability to simultaneously maintain multiple versions of the data in the database. The term read consistency reflects the fact that a query in Oracle will return results from a consistent point in time. Every block used by a query will be “as of” the same exact point in time—even if it was modified or locked while you performed your query. If you understand how multiversioning and read consistency work together, you will always understand the answers you get from the database. Before we explore in a little more detail how Oracle does this, here is the simplest way I know to demonstrate multiversioning in Oracle:

EODA@ORA12CR1> create table t as select username, created from all_users;
Table created.

EODA@ORA12CR1> set autoprint off
EODA@ORA12CR1> variable x refcursor;
EODA@ORA12CR1> begin
  2      open :x for select * from t;
  3  end;
  4  /
PL/SQL procedure successfully completed.

EODA@ORA12CR1> declare
  2      pragma autonomous_transaction;
  3      -- you could do this in another
  4      -- sqlplus session as well, the
  5      -- effect would be identical
  6  begin
  7      delete from t;
  8      commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

EODA@ORA12CR1> print x

USERNAME        CREATED
--------------- ---------
GSMCATUSER      01-OCT-14
ANONYMOUS       01-OCT-14
XDB             01-OCT-14
...
DBSNMP          01-OCT-14
ORACLE_OCM      01-OCT-14

36 rows selected.

In this example, we created a test table, T, and loaded it with some data from the ALL_USERS table. We opened a cursor on that table. We fetched no data from that cursor: we just opened it and have kept it open.

In the same session (or maybe another session would do this; it would work as well), we proceed to delete all data from the table. We even go as far as to COMMIT work on that delete action. The rows are gone—but are they? In fact, they are retrievable via the cursor (or via a FLASHBACK query using the AS OF clause). The fact is that the resultset returned to us by the OPEN command was preordained at the point in time we opened it. We had touched not a single block of data in that table during the open, but the answer was already fixed in stone. We have no way of knowing what the answer will be until we fetch the data; however, the result is immutable from our cursor’s perspective. It is not that Oracle copied all of the preceding data to some other location when we opened the cursor; it was actually the DELETE command that preserved our data for us by placing it (the before image copies of rows as they existed before the DELETE) into a data area called an undo or rollback segment (more on this shortly).

Transactions

A transaction comprises a unit of database work. Transactions are a core feature of database technology. They are part of what distinguishes a database from a file system. And yet, they are often misunderstood and many developers do not even know that they are accidentally not using them.

Transactions take the database from one consistent state to the next consistent state. When you issue a COMMIT, you are assured that all of your changes have been successfully saved and that any data integrity checks and rules have been validated. Oracle’s transactional control architecture ensures that consistent data is provided every time, under highly concurrent data access conditions.

Redo and Undo

Key to Oracle’s durability (recovery) mechanism is redo, and core to multiversioning (read consistency) is undo. Oracle uses redo to capture how the transaction changed the data; this allows you to replay the transaction (in the event of an instance crash or a media failure). Oracle uses undo to store the before image of a modified block; this allows you to reverse or rollback a transaction.

It can be said that developers do not need to understand the details of redo and undo as much as DBAs, but developers do need to know the role they play in the database. It’s vital to understand how redo and undo are related to a COMMIT or ROLLBACK statement. It’s also important to understand that generating redo and undo consumes database resources and it’s essential to be able to measure and manage that resource consumption.

Summary

In the following chapters, we’ll discover that different databases have different ways of doing things (what works well in SQL Server may not work as well in Oracle). We’ll also see that understanding how Oracle implements locking, concurrency control, and transactions is absolutely vital to the success of your application. This book first discusses Oracle’s basic approach to these issues, the types of locks that can be applied (DML, DDL, and latches), and the problems that can arise if locking is not implemented carefully (deadlocking, blocking, and escalation).

We’ll also explore my favorite Oracle feature, multiversioning, and how it affects concurrency controls and the very design of an application. Here we will see that all databases are not created equal and that their very implementation can have an impact on the design of our applications. We’ll start by reviewing the various transaction isolation levels as defined by the ANSI SQL standard and see how they map to the Oracle implementation (as well as how the other databases map to this standard). Then we’ll take a look at what implications multiversioning, the feature that allows Oracle to provide nonblocking reads in the database, might have for us.

This book also examines how transactions should be used in Oracle and exposes some bad habits that may have been picked up when developing with other databases. In particular, we look at the implications of atomicity and how it affects statements in Oracle. We also discuss transaction control statements (COMMIT, SAVEPOINT, and ROLLBACK), integrity constraints, distributed transactions (the two-phase commit, or 2PC), and finally, autonomous transactions.

The last few chapters of this book delve into redo and undo. After first defining redo, we examine what exactly a COMMIT does. We discuss how to find out how much redo is being generated and how to significantly reduce the amount of redo generated for certain operations using the NOLOGGING clause. We also investigate redo generation in relation to issues such as block cleanout and log contention. In the undo section of the chapter, we examine the role of undo data and the operations that generate the most/least undo. Finally, we investigate the infamous ORA-01555: snapshot too old error, its possible causes, and how to avoid it.