Expert One-on-One: Oracle: Pt. 3

By now, you might be able to see where I’m going in this section.
I have made references above 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 — and is in fact quite impossible unless
you know exactly how each database works in great detail.

For example, 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 Oracle. What I’ll show
here is that if you migrate your application from one database to
another database you will have to verify that it still works
correctly in these different environments.

Let’s assume that we had deployed the initial resource scheduler
application in a database that employed page-level locking with
blocking reads (reads are blocked by writes) and there was an
index on the SCHEDULES table:

create index schedules_idx 
      on schedules( resource_name, start_time );

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 page-locking system, due
to the update of the index page by RESOURCE_NAME and START_TIME
it is very likely that we would have serialized these
transactions. The system would have processed these inserts
sequentially due to the index page being locked (all of the
RESOURCE_NAMEs with START_TIMEs near each other would be on the
same page). In that page level locking database our application
would be apparently well behaved — our checks on
overlapping resource allocations would have happened one after
the other, not concurrently.

If we migrated this application to Oracle and simply assumed that
it would behave in the same way, we would be in for a shock. On
Oracle, which does row level locking and supplies non-blocking
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 multi-user 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 does it 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.

For example, very recently I was helping to 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:

declare
  l_some_variable   varchar2(25);
begin
  if ( some_condition )
  then
    l_some_variable := f( ... );
  end if;

  for x in ( select * from T where x = l_some_variable )
  loop
    ...

The goal here was to find all of the rows in T where X was Null
if some condition was not met or where x equaled a specific value
if some condition was met.

The complaint was that, in Oracle, this query would return no
data when L_SOME_VARIABLE was not set to a specific value (when
it was left as Null). In Sybase or SQL Server, this was not the
case — the query would find the rows where X was set to a
Null value. I see this on almost every conversion from Sybase or
SQL Server to Oracle. SQL is supposed to operate under tri-valued
logic and Oracle implements Null values the way ANSI SQL requires
them to be implemented. Under those rules, comparing X to a Null
is neither True or False — it is, in fact, unknown. The
following snippet shows what I mean:

[email protected]> select * from dual;

D
-
X

[email protected]> select * from dual where null=null;

no rows selected

[email protected]> select * from dual where null<>null;

no rows selected

Latest Articles