********************
NOT-REACHED-LAB-EXERCISE HOMEWORK BONUS OPPORTUNITY
*******************
* I am handing out the not-given Week 12 and Week 13 Lab Exercise
handouts to serve as more-SQL-and-SQL*Plus practice
and possibly boost your homework grade a little
* IF you do the Week 12 Lab Exercise and submit its pieces
by 11:59 pm on Friday, December 2, you can receive up to
25 homework "bonus" points (as if 25 points were added
to a Homework grade)
* IF you do the Week 13 Lab Exercise and submit its pieces
by 11:59 pm on Friday, December 2, you can receive up to
25 homework "bonus" points (as if 25 points were added
to a Homework grade)
...or up to 50 homework "bonus" points total
* (for this bonus purposes, you do NOT have work in pairs,
BUT you MAY if you wish (that's REAAALLLLLLY pairing,
two people at one keyboard and terminal,
one typing, one saying what to
type, much discussion and hopefully-learning going on,
and submit JUST one submission between you)
==================
a report tidbit from yesterday (also in the reports SQL reading packet):
==================
* consider:
col salary heading Salary format $99,999.99
* there is a COL command option, LIKE, to say that one column should be
formatted LIKE anothe column --
col commission LIKE salary heading Commission
...nice, because if later you change salary's format,
commission's (and any other with LIKE SALARY) will
change accordingly;
^ useful for more-reliable maintenance!
===============
BACK to transaction management and concurrency control
===============
* more on concurrency control!
* of the MANY algorithms for concurrency control,
there include broad categories such as:
* locks
* time stamping
* optimistic methods
^ let's talk a bit about an example or two from these categories...
(OK, in more detail for some than others...)
* let's talk about a couple of classic examples from the
locks category:
* let's try to prevent two transactions from accessing
the same data at the same time in an UNFORTUNATE way
(or a way that COULD be unfortunate)
* you LIMIT sharing somewhat, curtail concurrency somewhat,
make a TRADEOFF between concurrency and "safety"
by requiring that a transaction obtain a LOCK for
a thing before it can access/use/etc. that thing
* also sometimes called "resource locking"
* there are implicit locks and explicit locks --
implicit locks are placed/handled automatically by the
DBMS, and that's what we are assuming here
(explicit locks must be requested by the application
programmer...)
* NOTE: LOCK GRANULARITY is how much is locked by a lock --
one table? one row? one cell? the whole database?
and various levels in between...
* BINARY locks are one simple, "classic" approach
* a binary lock has TWO states, 1 and 0
a "thing" can thus be currently "locked" or "unlocked"
* if a "thing" is locked by a transaction A,
transaction B cannot use that "thing", B has to
wait until it is unlocked
* if the "thing" is unlocked, transaction B can
have the lock and now the "thing" is locked
* when the transaction with a lock on a "thing"
is done, it releases the lock -- now the
"thing" is again unlocked
* notice, then, every transaction requires a
lock and an unlock operation for every
"thing" to be accessed
* relatively simple -- also relatively restrictive,
doesn't allow, for example, multiple concurrent
reads;
* you CAN trade some complexity and memory for
more concurrency -- for example,
* you could have smaller lock granularity
* you could also try another kind of lock
* Shared/Exclusive locks (Read/Write locks)
* this lock has THREE states,
* unlocked,
* exclusive-locked (or write-locked)
* shared-locked (or read-locked)
* if a "thing" is exclusive-locked, write-locked,
ONLY the transaction with that lock can do
anything to that "thing"
* other transactions cannot obtain
shared OR exclusive locks until this is
released
* if a "thing" is shared-locked, read-locked,
* if another transaction also wants a shared lock,
it can obtain one
* if another transaction wants an exclusive lock,
it must wait until all the shared locks are
released
* if a "thing" is unlocked,
* if a transaction wants a shared lock,
it can obtain one
* if a transaction wants an exclusive lock,
it can obtain one
* let's turn this around:
* transaction T wants a shared lock on "object" O;
does it get it?
* IF the object is not locked, YES, T gets a
shared lock
* ELSE IF the object is share-locked, YES, T gets
a shared lock
* ELSE IF the object is exclusive-locked, NO, T
will NOT get a shared lock,
it has to WAIT until the exclusive-lock is
released
* transaction T wants an exclusive lock on "object" O;
does it get it?
* IF the object is not locked, YES, T gets the
exclusive lock
* ELSE IF the object is shared-locked, NO, T
will NOT get the exclusive lock,
it has to WAIT until all the shared-locks are
released
* ELSE IF the object is exclusive-locked, NO, T
will NOT get an exclusive lock,
it has to WAIT until the exclusive-lock is
released
* Locking approaches CAN be subject to
DEADLOCK
* be sure to look at this discussion in the reading packets!