P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML
SQL ̇OLE WL040/Bidgolio-Vol I WL040-Sample.cls June 20, 2003 13:16 Char Count= 0
362 STRUCTUREDQUERYLANGUAGE(SQL)other, they can never finish their processing. An exam-
ple of deadlock would be as follows: User 1 is updating
all of the rows in the MOVIE table and at the same time
User 2 is updating all of the rows in the TICKETTYPE
table. Now, User 1 decides to update all of the rows in
the TICKETTYPE table. The database will make the user
wait (because User 2 currently has the TICKETTYPE
data locked—the transaction has not been committed).
Meanwhile, User 2 decides to modify all of the rows in
the MOVIE table. Again the database will make User 2
wait (because User 1 has not committed his or her data).
At this point deadlock has occurred—both users are in a
wait state, which can never be resolved. Many commer-
cial RDBMSs test for deadlock and can perform some
corrective measures once it is detected. Corrective mea-
sures can range from aborting the SQL statement that
caused the deadlock to terminating the session with the
least amount of process time that was involved with the
deadlock—refer to the RDBMS’s user guide for details on a
system.ENHANCED VERSIONS OF SQL
Even though there have been three versions of the SQL
language published, there is no vendor that adheres
100% to the standard. All vendors add features to their
database engines in an attempt to entice consumers. This
is not always a bad feature. This is part of the rea-
son that there have been three standards to date: the
database vendors are continuously improving their prod-
ucts and exploring new areas that can be supported by
database technologies. Two of the most popular exten-
sions are procedural extensions and specialized deriva-
tives of SQL.Procedural Extensions to SQL
Although SQL is great at manipulating sets of data, it
does have some shortcomings. A few immediately come
to mind. It is usually very difficult with standard SQL to
produce a topnlisting, e.g., the top 10 songs on theBill-
boardcharts. It is impossible to process a result set, one
row at a time. Complex business rules governing data val-
idation cannot be handled by normal constraint process-
ing. Last, performance of SQL queries (from the entire
parse, execute, send results perspective) can be very poor
when the number of clients is large, such as in a Web en-
vironment. Note that a performance gain can be had if
every query was stored as a View (see above). The first
three of these items are typically handled in stored mod-
ules and triggers. The last item can be handled either by
using database views or by making use of the database en-
gine’s query caching scheme (if it exists)—this is beyond
the scope of this chapter.Stored Modules
A stored module (typically a procedure or function) is a
piece of procedurally enhanced SQL code that has been
stored in the database for quick retrieval. The benefits of
having the code stored are numerous. By storing the code,an end user does not have to type the code in each and
every time that it needs to be called upon. This supports
the concepts of code reuse and modularity. Performance
is increased, because the code has been verified to be cor-
rect and security policies can be enforced. For example, if
we want to limit access to a given table, we can create an
insert stored procedure and grant end users rights to ex-
ecute the procedure. We can then remove all rights to the
table from all users (except the table’s owner) and now, if
our database is broken into by any username other than
the table’s owner, the only operation that can be performed
is the insert stored procedure.Triggers
A trigger is a piece of procedurally enhanced SQL code
that has been stored in a database, which is automatically
called in response to an event that occurs in the database.
Triggers are typically considered unavoidable—i.e., if the
event for which a trigger has been defined occurs, the trig-
ger fires (is executed). Some RDBMSs do allow bulk load
programs to disable the triggering mechanism—refer to
the RDBMS’s user guide for details.
For example, for security reasons it has been deter-
mined that the finance manager will log all modifications
to the EMPLOYEESALARY table for review. Granted, we
could make use of a stored module (refer to the example
above) and add the logging code into the stored module.
However, we still have the loophole that the database
could be broken into using the EMPLOYEESALARY
table’s login ID (or worse yet the database administrator’s
login!). If this were the case, the stored module would
not be the only access point to the EMPLOYEESALARY
table.CONCLUSION
This chapter has given a brief introduction to the SQL
language, including its historical and mathematical foun-
dations. There are many good resources available online
and in books, periodicals, and journals. Even though there
is a standard for the SQL language, many vendors have
added extensions and alterations that are often used to
gain performance enhancements, which are very critical
in Web apps. Complications in multiuser environments
require that a locking analysis be conducted to detect and
correct bottlenecks.APPENDIX: SAMPLE SCHEMA
AND DATA
Figure 1 depicts the tables that are used in this chapter and
their relationships. The database supports the operation
of a set of movie theaters (named SITES). Each site can
run a MOVIE for a given number of weeks (this is stored in
MOVIERUN). Because a movie can have different show-
times each week that it is being shown, this information
is stored in the MOVIERUNTIMES table. Of course,
a movie cannot be shown without selling some tickets
(TICKETSSOLD). Because there are various charges for
movies, this information is stored in the TICKETPRICE
table.