Database Reference
In-Depth Information
people inserting into a table, or someone trying to query a table with others inserting rows (the query returns zero
rows per second). The differences between the way Oracle does it and the way almost every other database does it are
phenomenal—they are night and day.
Needless to say, neither the Informix nor the SQL Server technicians were too keen on the database log table
approach during their attempts. They preferred to record their timings to flat files in the operating system. The
Oracle people left with a better understanding of exactly how to compete against Sybase SQL Server and Informix:
just ask the audience “How many rows per second does your current database return when data is locked?” and take
it from there.
The moral to this story is twofold. First , all databases are fundamentally different . Second, when designing an
application for a new database platform, you must make no assumptions about how that database works. You must
approach each new database as if you had never used a database before . Things you would do in one database are
either not necessary or simply won't work in another database.
In Oracle you will learn that:
Transactions are what databases are all about. They are a good thing.
You should defer committing until the correct moment. You should not do it quickly to avoid
stressing the system, as it does not stress the system to have long or large transactions. The
rule is commit when you must, and not before . Your transactions should only be as small or as
large as your business logic dictates.
You should hold locks on data as long as you need to. They are tools for you to use, not things
to be avoided. Locks are not a scarce resource. Conversely, you should hold locks on data only
as long as you need to. Locks may not be scarce, but they can prevent other sessions from
modifying information.
There is no overhead involved with row-level locking in Oracle—
none . Whether you
have 1 row lock or 1,000,000 row locks, the number of resources dedicated to locking this
information will be the same. Sure, you'll do a lot more work modifying 1,000,000 rows
rather than 1 row, but the number of resources needed to lock 1,000,000 rows is the same as
for 1 row; it is a fixed constant.
You should never escalate a lock (e.g., use a table lock instead of row locks) because it would
be “better for the system.” In Oracle, it won't be better for the system—it will save no resources.
There are times to use table locks, such as in a batch process, when you know you will update
the entire table and you do not want other sessions to lock rows on you. But you are not using
a table lock to make it easier for the system by avoiding having to allocate row locks; you are
using a table lock to ensure you can gain access to all of the resources your batch program
needs in this case.
Concurrency and consistency can be achieved simultaneously. You can get it fast and correct,
every time. Readers of data are not blocked by writers of data. Writers of data are not blocked
by readers of data. This is one of the fundamental differences between Oracle and most other
relational databases.
Before we discuss the various types of locks that Oracle uses (in Chapter 3), it is useful to look at some locking
issues, many of which arise from badly designed applications that do not make correct use (or make no use) of the
database's locking mechanisms.
Search WWH ::




Custom Search