The Internet Encyclopedia (Volume 3)

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

MULTIUSERENVIRONMENTS 361

Removing Data

Syntax:

delete <table>

{where_clause};

For a Single Row

To delete only one row in a table we must construct a

whereclause that returns only that row. For example, if

we wanted to remove the SENIORS movie ticket type, we

would issue the following:

delete TICKET_PRICE

where TICKET_TYPE = 'SENIORS';

For Multiple Rows

For deleting multiple rows there are two options—if we

want to delete all of the rows in a given table, we just leave

off thewhereclause as such:

delete TICKET_PRICE;

If we want to delete a set of specific rows from a table,

then we must specify awhereclause that returns only

those rows. For example, if we want to delete only the

movies that allow passes, we issue the following:

delete MOVIE

where PASS_ALLOWED = 'Y';

TRANSACTION CONTROL

A database transaction is a unit of work performed.

This could be the checkout portion of a shopping cart

application—when the user finally hits the purchase

button. It could be a data entry person entering time

card data or performing an inventory to verify that stock

levels match what is on the shelves. In database terms

a transaction is a series of DML statements that are

logically grouped together. Because there are humans

involved, SQL provides us with the ability to recover from

mistakes (to a certain degree). We can start a transaction

by issuing a “begin transaction” statement. This marks

the beginning of our work session. We can then proceed

with modifying the data in any way that is required. Once

we are sure of our changes—verified via SQL queries, no

doubt—we can issue a “commit.” The commit statement

tells the database engine that we are done with this unit of

work and the changes should be made a permanent part

of the database. If we are not satisfied with out changes or

we made a mistake we can issue a “rollback” statement,

which undoes all of the work performed since the “begin

transaction” was issued. Note that SQL does not support

multiple levels of undo, like many computer applications.

Once a commit is issued, we must manually undo any

portion of our transaction that we are not satisfied with.

The classis example that is often used to illustrate mutual,

dependent, or two-phase commits is the use of an ATM

machine. When someone uses the ATM machine he or she

expects to receive the money requested, and the account

will be altered to reflect this withdrawal. Likewise, the

bank is willing to give the requesting person his or her

money (if sufficient funds are available) and the account

is properly adjusted. If both of these actions (dispensing

funds and altering account) are successful, then all is well.

If either action fails, then all transactions must be rolled

back.

MULTIUSER ENVIRONMENTS

One of the reasons for the success of the RDBMS is its

ability to handle multiple, simultaneous users. This is a

critical feature for many Web sites. Amazon.com would

not be around too long if only one person at a time could

look up a book’s information or check out. However, in

order to handle multiple, simultaneous users we must

have some locking mechanism in place in order for users

not to overwrite each other’s information. Likewise, we

do not want to delay a user’s ability to browse through

our dataset while it is being worked on—a common prob-

lem for any system (Web site or otherwise) that is in a

24 ×7 support mode. Various operations cause various

types or levels of locks to be placed on the data. For ex-

ample, in modifying the data, an exclusive lock is entered.

This forbids any other user to modify the same data until

the first user has completed his or her transaction. A com-

mit or rollback statement will either permanently record

or undo the change. This is the why developers should

commit often and as early as possible. Too many locks

without committing or rolling back the data have pro-

found performance implications. Different RDBMSs lock

at different levels—some lock only the data that are modi-

fied, others lock certain chunks of data (the affected rows

and other rows that are contiguously stored with them).

Techniques for dealing with various locks are beyond this

chapter.

Concurrency Issues

Concurrency is a big issue in multiuser systems. Every

user wants the most current data, yet just because we

are entering data into a system does not mean that we

entered them correctly—this is why transaction control

was invented. All enterprise RDBMSs have a form of lock-

ing that permits the querying of data that are in the pro-

cess of being modified. However, because the data being

modified are not yet committed to the database, the query

will only be allowed to see the committed data. The impact

is this—we can run a query, study the result, and 5 min

later, rerun the query and get different results. The only

alternative would be to have all queries wait until all of

the pieces of information that they are requesting were

not being modified by any other users. Another impact,

where a user may have to wait until another user has

completed a transaction, is when a user wants to mod-

ify a piece of information that is in the process of being

modified by another user. In this case only one user can

modify a piece of information (set of rows in a table) at a

time.

Deadlock

Deadlock is a condition where two users are waiting for

each other to finish. Because they are waiting on each