Database Reference
In-Depth Information
The
INSERT
statement will be provided a read-consistent view of
T.
It will not see the rows that it just inserted;
rather, it will only insert the rows that existed at the time the
SELECT
began. Some databases won't even permit
recursive statements such as the preceding because they can't tell how many rows might actually be inserted.
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.
Database Independence?
By now, you might be able to see where I'm going in this section. I have made references to other databases and how
features are implemented differently in each. With the exception of some read-only applications, it is my contention
that building a wholly database-independent application that is highly scalable is extremely hard—it is, in fact, quite
impossible unless you know exactly how each database works in great detail. And, if you knew how each database
worked in great detail, you'd understand that database independence is not something you really want to achieve
(a very circular argument!).
To illustrate, let's revisit our initial resource scheduler example (prior to adding the
FOR UPDATE
clause). Let's say
this application had been developed on a database with an entirely different locking/concurrency model from that of
Oracle. What I'll show here is that if you migrate your application from one database to another, you'll have to verify
that it still works correctly in these different environments and substantially change it as you do!
Let's assume that we had deployed the initial resource scheduler application in a database that employed
blocking reads (reads are blocked by writes). Also consider that the business rule was implemented via a database
trigger (
after
the
INSERT
had occurred but before the transaction committed, we would verify that only our row
existed in the table for that time slot). In a blocking read system, due to this newly inserted data, it would be true that
insertions into this table would serialize. The first person would insert her request for “room A” from 2:00 p.m. to 3:00
p.m. on Friday and then run a query looking for overlaps. The next person would try to insert an overlapping request
and, upon looking for overlaps, would become blocked (waiting for the newly inserted data to become available for
reading). In that blocking read database, our application would be apparently well behaved, though it could just as
easily
deadlock
(a concept covered in the chapter on locking) if we both inserted our rows and then attempted to read
each other's data. Our checks on overlapping resource allocations would have happened one after the other, never
concurrently.
If we migrated this application to Oracle and simply assumed it would behave in the same way, we would be in
for a shock. On Oracle, which does row-level locking and supplies nonblocking reads, it appears to be ill behaved.
As we saw previously, we had to use the
FOR UPDATE
clause to serialize access. Without this clause, two users could
schedule the same resource for the same times. This is a direct consequence of not understanding how the database
we have works in a multiuser environment.
I have encountered issues such as this many times when an application is being moved from database A to
database B. When an application that worked flawlessly in database A does not work or works in an apparently bizarre
fashion on database B, the first thought is that database B is a “bad database.” The simple truth is that database B just
works
differently.
Neither database is wrong or bad; they are just different. Knowing and understanding how they work
will help you immensely in dealing with these issues. Taking an application from Oracle to SQL Server exposes SQL
Server's blocking reads and deadlock issues—it goes both ways.
For example, I was asked to help convert some Transact SQL (the stored procedure language for SQL Server) into
PL/SQL. The developer doing the conversion was complaining that the SQL queries in Oracle returned the “wrong”
answer. The queries looked like this:
