Enterprise Computing CNT 4714
University of Central Florida
Popular in Course
Popular in Computer & Information Science
This 105 page Class Notes was uploaded by Clemens Konopelski on Thursday October 22, 2015. The Class Notes belongs to CNT 4714 at University of Central Florida taught by Mark Llewellyn in Fall. Since its upload, it has received 29 views. For similar materials see /class/227506/cnt-4714-university-of-central-florida in Computer & Information Science at University of Central Florida.
Reviews for Enterprise Computing
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 10/22/15
CNT 4714 Enterprise Computing Fall 2008 Part 239 Programming Multithreaded Applications in Java Dr Mark Llewellyn marklcsucfedu HEC 236 4078232790 httpwwwcsucfeducoursescnt4714fall2008 Instructor School of Electrical Engineering and Computer Science University of Central Florida CNT4714 Threading Part 2 Page 1 Mark Llewellyn Threads In the previous section of notes the thread examples all involved threads which were unsynchronized None of the threads actually needed to communicate with one another and they did not require access to a shared object The threads we ve seen so far fall into the category of unrelated threads These are threads which do different tasks and do not interact with one another A slightly more complex form of threading involves threads which are related but unsynchronized In this case multiple threads operate on different pieces of the same data structure An example of this type of threading is illustrated on the next page with a threaded program to determine if a number is prime I CNT4714 Threading Part 2 Page 2 Mark Llewellyn I 6 class for threaded prime number testing no inheritance issues so using the simple form of thread creation class testRange extends Thread static long possPrime long from to test range for a thread constructor e Number Teste record the number to be tested and the range to be tried testRangeint argFrom long argpossPrim e possPrime argpossPrime if argFrom 0 from 2 else from argFrom toargFrom99 implementation of run public void runO for long ifrom i lt to ampamp iltpossPrime il t if possPrime i i divides possPrime exactly Systemoutprintln factor i found by thread getNameO break exit for loop immediately yieldO suspend thread I CNT4714 Threading Part 2 Page 3 Mark Llewellyn I 6 D 39 er Class for 39me driver class to demonstrate threaded prime number tester Number TeSter public class testPrime public static void main String s num er to be tested for primality is entered as a command line argument examples 5557 is prime 6841 is prime 6842 is not prime long possPrime LongparseLongs0 int centuries int possPrimelOO l for int i0 iltcenturiesil t new testRangeil 00 possPrimestart This is an example of related but unsynchronized threads In this case the threads are related since they are each working on a piece of the same data but approach it from a slightly different perspective However they are unsynchronized since they do not share information I CNT4714 Threading Part 2 Page 4 Mark Llewellyn I 6 Emmet Messages gellmds mndnw lnnls mm elp vHson yMame 1 Elaswrimejm anagram FllesUalra j k15 bin IGMSP csl Jam all n le gull lzw lelnplmes gnmpiler gun VmGbenlh Help WEE EE 35342 mauve Class U1 demonstrate l J lLEEdEd mm umber tester Puhlll Class testhzlme pnhnc scans void mai l39nllnleL tn be I n Strung a leslpnmmass red m l lumenquot 13 annexed as a command hue axgument tes1aneava examu1 15 puma 584 puma 15 1107 pumaL tes lamjleass tEsTRa ElL m nwse Film Dehuu 1Wnnltbench mpileMessagns iiGRnSPMesSauES Runllo I quotI W ijGRASP exec java eschime 2mm factoz 2 found by thread Thread clear faucet 512 fauna by thread Thread S factuz 125 tmmd by thread re m add quotaquot issue 1024 mmd by Izhxead Thzeadrl Eaccuz 256 fuund by thread Thzeadz 2048 and 6398 422are not 3RASP npemmon complete V prlme thelr factor W lms exec java cesuPzime 6541 6841 IS prlme are shown byfthe 39r adwl JGMSP nparanion complete discoveled the factor jGRASP EXEC java cesthime 6842 cm 522 Emmd by thzali Tm taste 2 Emma by thread Thread U factor 311 uund by hzead Thread Ii is eadr fauna 3421 uund by thread Thread 34 l Related and Synchronized Threads The most complicated type of threaded application involves threads which interact with each other These are related synchronized threads Without synchronization when multiple threads share an object and that object is modified by one or more of the threads indeterminate results may occur This is known as a data race or race condition The example on the following page illustrates a race condition In this example we simulate a steam boiler and the reading of its pressure The program starts 10 unsynchronized threads which each read the pressure of the boiler and if it is found to be below the safe limit the pressure in the boiler is increased by 15psi Looking at the results you can clearly see the problem with this approach I CNT4714 Threading Part 2 Page 6 Mark Llewellyn I 6 Class to Simulate a Steam Boiler Pressure Gauge class to simulate a steam boiler to illustrate a race condition in unsynchronized threads public class SteamBoiler static int pressureGauge 0 static nal int safetyLirnit 20 public static void mainString args pressure psi new pressurelO for inti 0 i lt 10 i psii new pressure psiistart we now have 10 threads in execution to monitor the pressure for int i 0 i lt 10 i psiijoin wait for the thread to nish catch Exception e do nothing Systemoutprintln Gauge reads pressureGauge the safe limit is 20 I CNT4714 Threading Part 2 Page 7 Mark Llewellyn I 6 Thread Class to Read Steam Boiler Pressure Gauge and Increase the Pressure if Within Range thread class to raise the pressure in the Boiler class pressure extends Thread void RaisePressureO i n 391 A lt n quot afieth imit15 wait brie y to simulate some calculations try sleep100 catch Exception e SteamBoilerpressureGauge 15 raise the pressure 15 psi Systemoutprintlnquot Thread getNam e0 finds pressure within limits increases pressurequot else the pressure is too high do nothing end RaisePressure public void runO RaisePressureO this thread is to raise the pressure I Page 8 Mark Llewellyn I 6 CNT4714 Threading Part 2 Emmet Messages gemnus mnuuw 11ml mm elp nt ass 9 i ff i soLcnem1cIass Nduead class to 5135 rue pressure 111 Lbs Bauer ThlS IS what am Lcligm 4Eass BLESS pressure extends Th LCV a mud RaxsEPzessuzeU caused the Face 5 39fem39t 5 1 SteamBullezpzesauEEauge lt Steam uilensa condition to occur SQLcnemmml 4mm btlat ly a 3va mnz esmuerm SQLCIientJm m1 sleepuun catch Excepuon e 15 L39EISE CHE pL s Smeam nile prsssuresaugse e u sum 15 ps1 umprinclnirhxsgedleuen quot nnds pr esure mthln mars 7 1w Dre 5 mesmriquot 39 SIeamBuiIErclass eamBoIIerJava 2132 51mm lass systemuuprmunrhsgemaueu quot hurls pessuzamp ns mgr 7 an unuungquot 39 public vuld rum Reesepressurec Hrms mresa 15 to rsree rhe pressure 4 m I gt Main Line12 Cut Cnde z SUCMEP EILSE 7 e i J e 7 M muse Find Dehuu kahensh GRASPMessaes Runlto Equotquot jGRASP exec java Steamsuile clear Tureen mus pressure quot1min me 7 Increases pressure I Thread9 fines pressure uiuun mics 4 increases pressure output From Execution Thread2 finds pressure urchm 11mins luczeases pressure quot V 139 1 39339 1mm masses prams Illustrating the Race Condition Threa 1 as umhm June 7 7 muss 7 Thread6 finds pressure urchin 11mins xncreesee pressure Threadu finds pressure wlchin 11mins 7 lucreases pressure a n s n hunts mereeses pressure Threads funds pressure umun 11111115 s mereases pressure Gauge reeds 15m rhe safe max is an Bu l l M w h l Interesting Note on Race Conditions You may remember the large North American power blackout that occurred on August 14 2003 Roughly 50 million people lost electrical power in a region stretching from Michigan through Canada to New York City It took three days to restore service to some areas There were several factors that contributed to the blackout but the official report highlights the failure of the alarm monitoring software which was written in C by GE Energy The software failure wrongly led operators to believe that all was well and precluded them from rebalancing the power load before the blackout cascaded out of control Because the consequences of the software failure were so severe the bug was analyzed exhaustively The root cause was nally identi ed by artificially introducing delays in the code just like we did in the previous example There were two threads that wrote to a common data structure and through a coding error they could both update it simultaneously It was a classic race condition and eventually the program lost the race leaving the structure in an inconsistent state That in turn caused the alarm event handler to spin in an infinite loop instead of raising the alarm The largest power failure in the history of the US and Canada was caused by a race condition bug in some threaded C code Java is equally vulnerable to this kind of bug I GMT 4714 Threading Part 2 Page 10 Mark Llewellyn I 6 The Therac 25 Accidents Starting in 1976 the Therac25 treatment system built by Atomic Energy of Canada Limited AECL and COR MeV of France was used to ght cancer by providing radiation to a speci c part of the body in the hope of destroying tumors SiX known Therac25 accidents have been documented all involved massive overdoses of radiation and three resulted in the death of the patient serious longterm injury and dis gurement occurred in the other cases Patients received an estimated 17000 to 25000 rads to very small body areas By comparison doses of 1000 rads can be fatal if delivered to the whole bo y Analysis determined that the primary cause of the overdoses was faulty software The software was written in assembly language and was developed and tested by the same person The software included a scheduler and concurrency in its design When the system was rst built operators complained that it took too long to enter the treatment plan into the computer As a result the software was modi ed to allow operators to quickly enter treatment data by simply pressing the Enter key when an input value did not require changing I GMT 4714 Threading Part 2 Page 11 Mark Llewellyn I 6 The Therac 25 Accidents cont This modi cation created a synchronization error a race condition developed between the code that read the data entered by the operator and the code controlling the machine As a result the actions of the machine would lag behind e commands the operator entered The machine appeared to administer the dose entered by the operator but it fact had an improper setting that focused radiation at full power to a tiny spot on the body The race condition was subsequently found to occur only when a certain non typical keystroke sequence was entered an X to select a 25MeV photon followed by cursorup E to correctly set the 25MeV Electron mode then Enter since this sequence of keystrokes did not occur very often the error went unnoticed for a long time AECL was ultimately cited for improperly testing the software which was only tested on site in hospitals after a machine was assembled in place The designer had reused software from older Therac6 and Therac20 models that had hardware interlocks which masked the software defects Some operators noted that certain situations caused the machines to display MALFUNCTION followed by a number between 1 and 64 on the display screen However the user manual did not explain nor even address error codes so the operators pressed the P key for proceed to override the warning and proceed with the treatment I GMT 4714 Threading Part 2 Page 12 Mark Llewellyn I 6 Thread Synchronization To prevent a race condition access to the shared object must be properly synchronized Lost update problem one thread is in the process of updating the shared value and another thread also attempts to update the value Even worse is when only part of the object is updated by each thread in Which case part of the object re ects information from one thread While another part of the same object re ects information from another thread The problem can be solved by giving one thread at a time exclusive access to code that manipulates the shared object During that time other threads desiring to manipulate the object must be forced to wait GMT 4714 Threading Part 2 Page 13 Mark Llewellyn I 6 Thread Synchronization cont When the thread with exclusive access to the object finishes manipulating the object one of the blocked threads will be allowed to proceed and access the shared object The next selected thread will be based on some protocol most common of these is simply FCFS priorityqueue based The In this fashion each thread accessing the shared object excludes all other threads from accessing the object simultaneously exclusion This is the process known as mutual Mutual exclusion allows the programmer to perform thread synchronization which coordinates access to shared objects by concurrent threads GMT 4714 Threading Part 2 Page 14 Mark Llewellyn I 6 Synchronization Techniques There have been many different methods used to synchronize concurrent processes Some of the more common ones are Test and Set Instructions All general purpose processors now have this kind of instruction and it is used to build higherlevel synchronization constructs Test and set does not block that must be built on top of it p and v semaphores Introduced by Dijkstra in the 1960 s and was the main synchronization primitive for a long time Its easy to build semaphores from test and set instructions Semaphores are lowlevel and can be hard for programmers to read and debug For your information the p is short for the Dutch words proberen te verlangen which means to try to decrement and the v stands for verhogen which means to increment GMT 4714 Threading Part 2 Page 15 I Mark Llewellyn I 6 Synchronization Techniques cont 7 Readwrite Locks These are also commonly referred to as mutexes although some people still use the term mutex to refer to a semaphore A lock provides a simple turnstile only one thread at a time can be going through executing in a block protected by a lock Again it is easy to build a lock from semaphores 7 Monitors A monitor is a higherlevel synchronization construct built out of a lock plus a variable that keeps track of some related condition such as the number of unconsumed bytes in the buffer It is easy to build monitors from readwrite locks A monitor de nes several methods as a part of its protocol Two of those prede ned methods are wait and notify I GMT 4714 Threading Part 2 Page 16 Mark Llewellyn I 6 Types of Synchronization There are two basic types of synchronization between threads 1 Mutual exclusion is used to protect certain critical sections of code from being executed simultaneously by two or more threads Synchronization without cooperation 2 Signalwait is used when one thread need to wait until another thread has completed some action before continuing Synchronization with cooperation Java includes mechanisms for both types of synchronization All synchronization in Java is built around locks Every Java object has an associated lock Using appropriate syntax you can specify that the lock for an object be locked when a method is invoked Any further attempts to call a method for the locked object by other threads cause those threads to be blocked until the lock is unlocked I GMT 4714 Threading Part 2 Page 17 Mark Llewellyn I 6 Thread Synchronization In Java Any object can contain an object that implements the Lock interface package java util concurrent locks A thread calls the Lock s lock method to obtain the lock Once a lock has been obtained by one thread the Lock object will not allow another thread to obtain the lock until the thread releases the look by invoking the Lock s unlock method If there are several threads trying to invoke method lock on the same Lock object only one thread may obtain the lock with all other threads being placed into the wait state I GMT 4714 Threading Part 2 Page 18 Mark Llewellyn I 6 An Aside on Reentrant Locks Class Reent rantLoc k package javautilconcurrentlocks is a basic implementation of the Lock interface The constructor for a ReentrantLock takes a boolean argument that specifies whether the lock has a fairness policy If this is set to true the ReentrantLock s fairness policy states that the longest waiting thread will acquire the lock when it is available If set to false there is no guarantee as to which waiting thread will acquire the lock when it becomes available Using a lock with a fairness policy helps avoid indefinite postponement starvation but can also dramatically reduce the overall efficiency of a program Due to the large decrease in performance fair locks should be used only in necessary circumstances I I GMT 4714 Threading Part 2 Mark Llewellyn I Page 19 Condition Variables If a thread that holds the lock on an object determines that it cannot continue with its task until some condition is satisfied the thread can wait on a condition variable This removes the thread from contention for the processor by placing it in a wait queue for the condition variable and releases the lock on the object Condition variables must be associated with a Lock and are created by invoking Lock method newCondition which returns an object that implements the Condition interface To wait on a condition variable the thread can call the Condition s await method see Life Cycle of a thread in previous set of notes I GMT 4714 Threading Part 2 Page 20 Mark Llewellyn I 6 Condition Variables cont Invoking the await method immediately releases the associated Lock and places the thread in the wait state for that Condition Other threads can then try to obtain the Lock When a runnable thread completes a task and determines that the waiting thread can now continue the runnable thread can call Condition method signal to allow a thread in that Condition s wait queue to return to the runnable state At this point the thread that transitioned from the wait state to the runnable state can attempt to reacquire the Lock on the object Of course there is no guarantee that it will be able to complete its task this time and the cycle may repeat I GMT 4714 Threading Part 2 Page 21 Mark Llewellyn I 6 Condition Variables cont If multiple threads are in a Condi ti on s wait queue when a signal is invoked the default implementation of Condition signals the longestwaiting thread to move to the runnable state If a thread calls Condition method signalAll then all of the threads waiting for that condition move to the runnable state and become eligible to reacquire the Lock When a thread is finished with a shared object it must invoke method unlock to release the Lock I GMT 4714 Threading Part 2 Page 22 Mark Llewellyn I 6 Thread States With Synchronization Queue of threads waiting for lock notify by Already locked by anotherthread another thread Queue of threads waiting for notify I I Unlock b Thread attempting another thread Iafgess J one in queue moves to running state Lock obtained wait by this thread by this thread unlock by this thread oes not remove it from the running state Running State I GMT 4714 Threading Part 2 Page 23 Mark Llewellyn I Deadlock Deadlock will occur when a waiting thread call it thread 1 cannot proceed because it is waiting either directly or indirectly for another thread call it thread 2 to proceed while simultaneously thread 2 cannot proceed because it is waiting either directly or indirectly for thread 1 to proceed When multiple threads manipulate a shared object using locks ensure that if one thread invokes await to enter the wait state for a condition variable a separate thread eventually will invoke method signal to transition the waiting thread on the condition variable back to the runnable state If multiple threads may be waiting on the condition variable a separate thread can invoke method 5 i gna 1A1 l as a safeguard to ensure that all of the waiting threads have another opportunity to perform their tas GMT 4714 Threading Part 2 I6 Page 24 Mark Llewellyn ProducerConsumer Problem Threads V thout Synchronization In a producerconsumer relationship the producer portion of an application generates data and stores it in a shared object and the consumer portion of an application reads data from the shared object Common examples are print spooling copying data onto CDs etc In a multithreaded producerconsumer relationship a producer thread generates data and places it in a shared object called a buffer A consumer thread reads data from the buffer What we want to consider first is how logic errors can arise if we do not synchronize access among multiple threads manipulating shared data I Mark Llewellyn I GMT 4714 Threading Part 2 Page 25 ProducerConsumer wo Synchronization The following example sets up a producer and consumer thread utilizing a shared buffer code is on the webpage The producer thread generates the integer numbers from 1 to 10 placing the values in the shared buffer The consumer process reads the values in the buffer and prints the sum of all values consumed Each value the producer thread writes into the buffer should be consumed exactly once by the consumer thread However the threads in this example are not synchronized 7 This means that data can be lost if the producer writes new data into the buffer before the consumer has consumed the previous value 7 Similarly data can be incorrectly duplicated if the consumer thread consumes data again before the producer thread has produced the next value I GMT 4714 Threading Part 2 Page 26 Mark Llewellyn I 6 ProducerConsumer wo Synchronization cont Since the producer thread will produce the values from 1 to 10 the correct sum that should be 55 The consumer process will arrive at this value only if each item produced by the producer thread is consumed exactly once by the consumer thread No values are missed and none are consumed twice I ve set it up so that each thread writes to the screen what is being produced and what is being consumed Note the producerconsumer threads are put to sleep for a random interval between 0 and 3 seconds to emphasize the fact that in multithreaded applications it is unpredictable when each thread will perform its task and for how long it will perform the task when it has a processor I GMT 4714 Threading Part 2 Page 27 Mark Llewellyn I 6 ndo publrc class Producer lmple ents Runn p v Producer39s run method stores the values 1 to 10 in buffer lmport javautllRa m r1 ate statlc Random generator prlvate Buffer sharedLocatlon ab el 7 new Ran Om reference to shared object Construc Buffer shared r shared end Producer Constructor Producer Thread Class store values from 1 to 10 in sharedLocatlon publrc vold 1n sum count lt 10 count l sleep 0 to 3 seconds Threadsleep th n place value in Bu generator nextInt sharedLocatlon set f r 3000 sleep thread unt set value 1n buffer um c lncrement sum of val Systemoutprlntf quott2dnquot l en try sum Randomly ead lnterrupted prlnt stack trace sleep the catch InterruptedExceptlon exceptlon r e eptlonprlntstackTrace thread form l end catch to 3 seconds l end for Systemoutprlntf quotnsnsnquot quotProducer done producrngquot quot ermlnatlng Producerquot d method run l end class Producer GMT 4714 Threading Part 2 Page 28 Mark Llewellyn Consumer39s run method loops ten times reading a value from buffer import javautllRandom public class Consumer implements Runnable private static Random generator new Random private Buffer sharedLocation reference to shared object constru tor public Consumer Buffer shared sharedLocation shared l end Consumer constructor read sharedLocatlon39s value four times and sum the values ConsumerThread Class public void run int sum 0 for int count 1 count lt 10 count l sleep 0 to 3 seconds read value from buffer and add to sum tr l Threadsleep generatornextlnt 3000 sum sharedLocatlonget Systemoutprlntf quotttt2dnquot sum l end try Randomly 1f sleeping thread interrupted print stack trace sleepthe catch InterruptedExceptlon exception thread forup exceptionprintstackTrace to 3 seconds l end catch l end for Systemoutprlntf quotns dnsnquot quotConsumer read values totalingquot sum quotTerminating Consumerquot l end method run l end class Consumer GMT 4714 Threading Part 2 Page 29 Mark Llewellyn Buffer interface speci es methods called by Producer and Consumer public interface Buffer public Void set int Value place int Value into Buffer WRITE public int getO return int Value from Buffer READ end interface Buffer Buffer Interface UnsynchronizedBuffer represenw a single shared integer public class UnsynchronizedBuffer implements Buffer private int buffer l shared by producer and consumer threads place Value into buffer public Void set int Value Systemoutprintf quotProducer Writest2d Value b er V ue end method set return Value from buffer public int get stemoutprintf quotConsumer readst2d buffer return buffer end method get end class UnsynchronizedBuffer GMT 4714 Threading Part 2 Page 30 Mark Llewellyn I 6 App import import public p l e lication shows two threads manipulating an unsynchronlzed buffer javautllconcurrentExecutorServlce javautllconcurrentEXecutors ProducerConsumer class SharedBufferTest DriverCIaSS lic static void main stringll args H create new thread pool with two threads ExecutorsnewleedThreadPool 2 ExecutorServlce application create UnsynchronlzedBuffer to store ints t n Buffer s redLo i ew UnsynchronlzedBuffer System outprint quot tt tSum tSumquot System out println quotActlol lttValuetPrOducedtCOnsumedquot Systemoutprintln quot ssss ttet ssssss quott ssssss quotnquot try to start producer and consumer giVing each of them access to SharedLocatlon try applicationexecute new Producer applicationexecute new Consumer l end try Exception exception ceptionpri l end catch sharedLocation sharedLocation J J l ntStackTrace application shutdown terminate application when threads end end main nd class SharedBufferTest GMT 4714 Threading Part 2 Page 31 Mark Llewellyn Producer Side Unsynchronized Case sharedLocationsetcount 4 Consumer Side sharedLocation Buffer set Hem6d refur n s sum sharedLocationget I l ga39n39qa aa39r39e ta r s Both the producer and consu threads are always in gt the running state never blocked GMT 4714 Threading Part 2 Page 32 Mark Llewellyn EmiECI Messanes Swings Vinnqu IDDIS DUI elp lkas massages mum L rrrerRASP upexaunn cuuplece ml 2 Massages jGRA5P Exec java Shared uffezTesc Clear Sum Sum Help Acclun Value Produced Consumed ex 1 1 Produce wanes Z 3 Consume reads 2 2 Prod El ICES 3 6 Ptmiucet writes 4 1D Producer WKJEES 5 15 anduce wnces S 21 Consume reads 6 8 Produce wntea 7 28 Producer WI 3 B 36 Producer MIJEES 9 45 Consumer reads 9 17 C an ad 9 26 Consumer leads 9 35 Consumer reads 9 44 Produce wnnes l 55 quduce dune producing V I I ng g z i gaggquHm 54 The unsynchronlzed threads dId not Consume zeaas m 54 produce the same sum The producer Em 3 5 1 7 produced values that sum to 55 but the unsume reads in 34 consumer consumed values that sum to E d l 1 Ed T angu m quotquot1 84 Notice that the consumer read the both value 9 and 10 four times but failed to GRASP E 1 be t L 3 we 1 quot my E read the value of severalrvalues atall eg Eruiecl Messages Swings Mnunw Innis mp Massages I mi Clear Help L um elp l ds messages unll jGRASP Uparacmn complete vii 35mm EXEC java Shared uffe 39 st Sum Sum Accnm Value Pruduced Consumed Consume E fl 1 Pruducez write 1 1 P 6 CE 2 3 Consumer read 2 1 Consume 5515 2 3 mm mm 3 5 In this execution the sum qud Ce 1 LEE 4 10 Produce wntea 5 is produced by the consumer Eons er 2 dz 5 a g ED mm 5 13 IS much closer to the correct quduce mm s 21 result but still off because u a s 6 19 Produce Wmtea 7 25 the consumer reag the Purim3r EASE 26 26 values 2 and395 Mice and a use m as Eunsulnez Eads s 34 failed to read the values 1 3 Produce wnces 9 45 Consume meals 9 43 and394 at all39 Producer WKJEES 1U 55 quduce dune pmducxng Tezmlnacing Producer Cunsumer reeds ll 53 Eunsulnex read values ntallng 53 Teminacing msunax GFASP npemtion complete L SynchronizedBuffer synchronizes access to a single shared integer importj avautilconcurrentlocksLock imp ort j avautilconcurrentlocksReentrantLock imp ort j avautilconcurrentlocksCondition public class SynchronizedBuffer implemenw Buffer Synchronized Buffer Class No fairness policy needed since only a single producer thread and single Lock to control synchronization With this buffer consumer thread private Lock accessLock new ReentrantLockO condition variables to control reading and Writing private Condition canWrite accessLockneWConditionO private Condition canRead accessLockneWConditionO Condition variables on the lock Condition canWrite contains a queue for threads waiting to write while the buffer is full If private int buffer l shared by producer and consumer threads the buffer is full the producer private boolean occupied false Whether buffer is occupied place int value into buffer public void set int value Acquire ock accessLocklockO lock this object output thread information and buffer information then Wait calls method await on this condition When the Consumer reads data from a full buffer it calls method signal on this Condition Condition canRead contains a queue for threads waiting while the buffer is empty If the buffer is empty the Consumer calls method await on this Condition When the Producer writes to the empty buffer it will call method signal on this Condition While buffer is not empty place thread in Waiting state While occupied Systemoutprintln quotProducer tries to Write displaySlate quotBuffer full Producer Waiw canWriteaWaitO Wait until buffer is empty end While I GMT 4714 Threading Part 2 Page 35 Mark Llewellyn I 6 buffer Value set new buffer Value indicate producer cannot store another Value until consumer retrieves current buffer Value occupied true displayState quotProducer Writes buffer signal thread Waiting to read from buffer canReadSlgnal03 Signal Consumer thread that a value enduy has been produced and can be read catch InterruptedException exception exceptionprintStackTraceO end catch Unlock object before exiting method nally accessLockunlockO unlock this object end nall end method set return Value from buffer public int getO 1nt readValue 01n1t1a11ze Value readW Acquire lock on the buffer accessLocklockO lock this object output thread information and buffer information then Wait HY While no data to read place thread in Waiting state While loccupied Systemoutprintln quotConsumer tries to read consumer mUSt wait until a value displaySlate quotBuffer empty Consumer Waiw has been pmduced by the canReadaWaitO Wait until buffer is full Preducel39 Await Signal by end While Producer I GMT 4714 Threading Part 2 Page 36 Mark Llewellyn 6 I indicate that producer can store another Value cause consumer just retrieved buffer Value occupied f se readValue buffer retrieve Value from buffer displaySlate quotConsumer reads readValue signal thread Waiting for buffer to be empty canWritesignalO end try if Waiting thread interrupted print stack trace catch InterruptedException exception eptionprintStackTraceO end catch nally accessLockunlockO unlock this object end nally return readValue end method get display current operation and buffer state public Void displaySlate String operation System occup end method displaySlate end class SynchronizedBuffer GMT 4714 Threading Part 2 gnal waiting Producer that e buffer is empty and it can write Si t Make sure lock is released outprintf 40sdttttbn operation buffer ie Page 37 Mark Llewellyn i6 Application shows two threads manipulating a synchronized buffer imp ort j aVautilconcurrentExecutorS erv1ce imp ort j aVautilconcurrentExecutors public class SharedBufferTestZ public static Void main String args create new thread pool with two threads Synchronization n Driver Claseror Illustrating ProducerConsumer Problem ExecutorS ervice application ExecutorsnewFixedThreadPool 2 create SynchronizedBuffer to store ins Buffer sharedLocation new SynchronizedBufferO Systemoutp1intln Using Standard Locking Systemoutp1intf 40ssttsn40ssnnquot quotOperationquot e u u u u tt quotBuffer Contents quotOccupi d try try to start producer and consumer applicationexecute new Producer sharedLocation Only change between SharedBufferTest for applicationexecute new Consumer sharedLocation end catch Exception exception exceptionp1intStackTraceO end catch applicationshutdownO end main end class est2 unsynchronized version GMT 4714 Threading Part 2 Page 38 Mark Llewellyn Synchronized Case lock acquisition queue accessLockock acquire lock released accessLock ock acquire lock released while occupied buffer not empty while loccupied buffer empty canReadawait block canWrite condition queue canWriteawait block on condition signal from producer fcanRead con Ion queue l bU er Value quotperform Wr39te occupied false indicate read occupied true indicate write XV39te readquot readVaIue buffer i canReadsignaI signa thread canWritesignaI signa thread accessLockunock reease lock SharedLocation accessLockunock reease lock B uffe r set method returns get method returns I GMT 4714 Threading Part 2 Page 39 Mark Llewellyn I 6 State Diagram Synchronized Version Running bufferfull occupied canWrite condition queue ca nWrite signaled lock request fails accessLock queue lock released lock request fails buffer em my canRead condition queue Running Consumer Thread canRead signaled 2m Blocked GMT 4714 Threading Part 2 Page 40 Mark Llewellyn I Erujecl Messanes Swings Mndnw Innis ULquot elp Mzssages jGRASF39 Messages Run IIO I ll Dpezacmn Buffer Euncencs Dccupled Clear Producer mass 1 1 me a Produce Elles LD write Help Buffez full Pxnducex wants 1 uzue 3 Consume leads I 1 false Pro u e Writes 2 2 true Consumer reads 2 Z false Produce wzlnes 3 3 true Consume ads 3 false Produce Wntes 4 4 true Pr dueat tries 0 quotti e Buffer full Produce Halts true on when near 4 false quduce 11 s 5 5 true Produce cries CD mace Buffer full Produce alts 5 ttue I ulna ads 5 5 false quduce writes 5 6 true quduce Elle EU truce Buffer f 1 quduce malts 5 true Consumer reads 6 5 false Produce mute 7 7 true Produce tries to much Buffez full Produce wars 7 rue Consume lea E 7 7 false Producer mute E B Inns 0 ulnex earls B 3 false Produce muses 9 9 true unsume tea 9 false Cunsume tries to Buffer empty Consume quot315 9 false Produce W 10 If true Consume leads 1 10 false Ptuaucer duna producing mummy P D um Both the Producer and Consumer Consume read values totaling 55 7 threads produced the same Sum Tezmlnating Bummer Praline393 ptnduced values tuballng 55 synchronized threads 1 m Emjecl MESSIBES Swings A ndnw IDDIS UMI elp r75 2 Messages g jGRASP Messages I ml Clear Help 1117 In Dpezaclon Producer Tunas 1 consume e 2 Consume cues to ead Consume quot315 Pruducez unoee 3 us Consume tries to read Buffer empty Ennsuma quotalts Producer wunes a o ads 4 Consume cries co read Butte empty Ennsume walts Producer wmtea 5 consume eeos 5 tone e ones no need Buffer empcy Consume wane ee 5 S Consume tries to read Buffer empoy Consume malts 39 ee 7 39 a mute Buffer full Ptndute malts consumer reads 9 Produce vanes 10 quduce done prooumno Terminamng Prnducet Producer produced values cocanng 55 Consume reeds ln Consume read values cunaling 55 Butte Concencs NNFH N wmmqum menu w assumed false Monitors and Monitor Locks Another way to perform synchronization is to use Java s builtin monitors Every object has a monitor Strictly speaking the monitor is not allocated unless it is used A monitor allows one thread at a time to execute inside a synchronized statement on the object This is accomplished by acquiring a lock on the object when the program enters the synchronized statement synchronized object stat end synchronized statement Where object is the object Whose monitor lock will be acquired If there are several synchronized statements attempting to execute on an object at the same time only one 0 them may be active on the object at once 7 all the other threads attempting to enter a synchronized statement on the same object are placed into the blocked state see next page I GMT 4714 Threading Part 2 Page 43 Mark Llewellyn I 6 Monitors and Monitor Locks cont When a synchronized statement nishes executing the monitor lock on the object is released and the highest priority blocked thread attempting to enter a synchronized statement proceeds Java also allows synchronized methods A synchronized method is equivalent to a synchronized statement enclosing the entire body of a method If a thread obtains the monitor lock on an object and then discovers that it cannot continue with its task until some condition is satis ed the thread can invoke Object method wait releasing the monitor lock on the object This will place the thread in the wait state When a thread executing a synchronized statement completes or satis es the condition on which another thread may be waiting it can invoke Object method not i fy to allow a waiting thread to transition to the blocked state again I GMT 4714 Threading Part 2 Page 44 Mark Llewellyn I 6 Thread Class to Read Steam Boiler Pressure Gauge and Increase the Pressure if Within Range Synchronized M thod Version thread class to raise the pressure in the Boiler class pressure extends Thread synchronized void RaisePressureO jfl 391 1 a ltquot quot afetvlimit15 wait brie y to simulate some calculations try sleep100 catch Exception e SteamBoilerpressureGauge 15 raise the pressure 15 psi SystemoutprintlnquotThread getNameO finds pressure within limits increases pressurequot else the pressure is too high do nothing public void runO RaisePressureO this thread is to raise the pressure GMT 4714 Threading Part 2 Page 45 Mark Llewellyn Emmct Messages gemngs mnuuw 11ml mm elp 1 y pressurejm 39 39 39 39 a n 39 rgw lemplates Qamp er Elm nrkhench gem ZEUS I wmzead class to name thE prsssvue m LhE Hana PaimPanEI I Lelass PaIszneLclass class pressure ex a Thze paimpmmm a c Dbje z u 7 new nhjem sz Va 2 n PanelDemuclass Synthmumm Panel quotM It Steam mler 1155 suzasauge lt sneam nnezsataylm1c7153 Hum hueny m simulate e alcmaumns cl Y Sleepll catch Excepuun e Steam ullezPzessuze auge 15 y talse Che px ssvuamp 15 p3 Systemout prlntlnth3getllameJ quot fxnds pressure mlthln 111mm 7 Juareases pressuLequot purllandmlh 212 1 Ch c J d h h 1 L h 2 yscemwumpzint n 13gE an quot mm s pleasure mm 1g 7 meg I mgquot messure39dass Jami SW14hLDn12Ed black ETESSHI B 3V8 PlimChaLcl SS public was tun R aisePzessuze ms m 115151 EhE M 71 M l gt ws ELK Line13 Culi73 Cad 32 Tnpz1 PlintNummass Plin askmlass m t nurse Find Dehug lwuvkhannh Thzead U findS press ulthin limits 7 mczzases measure Thread1 finds pr wlthln 11mins mcreases esauze Thread 2 mas ptassute within 11mm mcreases pzessute a high 7 n namin E m m 1 h n H u a w 7 n m m w m n a 1 mzeag74 finds pressure c 1 7 ThzeadS finds pressure can high dues nucmng 39 5 Press a t a a r a y a a a i P 1 a Thread6 Ema u 0 high iues nothan Threa 7 find u 0 hi h in 11min g Threadr f as pressure E00 1 EIE nachi g7s tmgs pxessuxa can high 7 dues nothing Gauge reads 45 the safe mu i 5n Syscem quotDmlnal 4 w gt Mutual Exclusion Over a Block of Statements Applying mutual exclusion to a block of statements rather than to an entire class or an entire method is handled in much the same manner by attaching the keyword synchronized before a block of code You must explicitly mention in parentheses the object whose lock must be acquired before the block can be entered The following example illustrates mutual exclusion over a block using the pressure gauge example on pages 7 and 8 I GMT 4714 Threading Part 2 Page 47 Mark Llewellyn I 6 Increase the Pressure if Within Range Synchronized Version thread class to raise the pressure in the Boiler class pressure extends Thread Syn 3hr nlzed IStatement static object 0 new Objecto reqUIres an Object to lock Void RaisePressureO synchronizedO Thread Class to Read Steam Boiler Pressure Gauge and 11 A N quot quotimit15 Wait brie y to simulate some calculations try sleep100 catch Exception e teamBoilerpressureGauge 15 raise the pressure 15 psi Systemoutprintln Thread thisgetNarne nds pressure Within limits increases pressurequot else Systemoutprintln Thread thisgetName nds pressure too high do nothing end synchronized block public void run 1 RaisePressureO this thread is to raise the pressure I GMT 4714 Threading Part 2 Page 48 Mark Llewellyn I 6 Emmet Messages gemngs mnuuw 11ml mm elp l Epressurejm 39 39 l y 0 El le gull lzw lelnplmes gnmpiler gun VmGbenlh elp ew lies vJ Sort By Name 139 r R Enukmarks mner F39 ESUMWK I rum Wages li xLead class m gssniht gressure 1n me Belle c as pressure exren s res nucnunl WithSynnNewJava E mm D E quotw abject FaisePzessurEU synchtunlzadl 1E seemBouerpressuzelseuge lt SteamBmueLsafecylerrlE Mun LlLl fl y mulace Slrme Callrulaurms tLYl5lEEPl100 catch Excepcim E l sceem uilerpressureseuge 15 m e lltessuLE 15 p3 System crutpt1nt1nquotThtead quot thlsgalllame quot rmds pressure mrhm umcs 7 111 R dilinllmlass 39 mm anniEMeweraxe amexe MLIaSS BankSlmulatlnnSDepnsilTask class aankSimulanunmnurawrasmass ankSilnulalium ass SystemDucpnntlnquotThreadquot rhxsgedlamell quot fmds pressure we hlgh 7 dm nurhmg H nd synchLmuzza39 marx ll I cade116 Tum 1 l gt fw am Lunel c nwse Find Debug 1Wnrkhench l l mpile Me sages I jGRASF Messages Run IIO l quot39 Three eadU m ressure Wid n limits 7 hcreeses pressure Thread Thread1 fmds pressure wichm 11mth increases pressure Clear Thread Threea72 mas pressure 141mm lunlza 7 in s 1 e re Threemhreeea fmd res 00 hi h 7 do much Help ThreedThreed74 finds pressure ruu hlgh 7 do huuhmg EaCh thread Sleeps for 7 ThreedThreeuls nds pressure WU hlgh do nothlny 39 Threed39rhreed s mas re ur em b1gh do nothing 100 msec before Checkmg Threade a 7 Emds pressure 00 high 7 an nunhlng pressure gauge Thze a ee 75 imds pressure 00 hrgh 7 do narhmg Threed39lhreed79 finds pressure we high 7 tin nachan Gauge reeds 45 the safe llmlt is 50 7777JGRASP uperecmn samplece L l l lll gt l rm Emmet Messages gemnus mnunw 11ml vHsortByName 1V R Enukmarks mner F39 ESUMWK I rum iles Wages rimmed class m 5135 ess pressure exren s quotEf39fmmnhsynmew39lm scam Objecc n new Db MdIIImIElass vu Fai ePzessurEU r m synchrsmzedm annretviewemxe if 5552an Wm r FY 1519 pm scesmauuer es SYSCEIILWE class Banksimulmiunsoepnsnrasmass nurawrasmass ankSimulaIiunJ ass HEIP s sysc 3 ens syn 1 fw BLK LunaB c Epressurejm le gun mew Iemplmes gnmpilsr gun workbench elp ew Ith o11erpressuressuge lt r nrm r mula p e prmmnr39Threed quot thlsgalllame Chi pressure 1n me seller read ject SreamBn11esaf alculauuh m n e l x 3 Ce some e Gauge 15 sure emDupnntlnquotThreadquot ch13g2dlamey meea black I cade116 Tum ecyLnurrlE tessuLE 15 pa quot rmus pressure mmm hunts e 111 quot nds pressure we mgh rim nuchmg r gt nwse Find Debug 1 Wnrkhench mpile Me sages I jGRASF Messages Run IIO JGRASP exec java SteaInEuile I39m 1 Clear Thread Threa d 1nd pressure 14 Ha Th adThl 1nd5 pressur mu 7 Thread39fhzead7 finds pres u Wu Thresd39mre a s mus pre sur rue resumr a 5 Emds pressure 00 ThreadThzead 4 finds press 2 00 Thread39rhr 5 finds pressure we ThreadThzesds mds pressu e tuU Gauge reads as th ssre 11m 15 iiiIIGRASP uperaunn samplece z Thread Threadu fmds pressure I chm Chm um h 1m 111mm 7 increase 7 increases pzessuze muung 13 ins do increases pressure s pressure 39r l Each thread immediater checks pressure gauge I Z Caution When Using Synchronization As with any multithreaded application care must be taken when using synchronization to achieve the desired effect and not introduce some serious defect in the application Consider the variation of the pressure gauge example that we ve been dealing with on the following page Study the code carefully and try to determine if it will achieve the same effect as the previous version of the code Is it correct Why or why not No The this object is one ofthe 10 different threads that are created Each thread will successfully grab its own lock and there will be no exclusion between the different threads Synchronization excludes threads working on the same object it does not synchronize the same method on different objects I GMT 4714 Threading Part 2 Page 51 Mark Llewellyn I 6 Does this code correctly synchronize the pressure gauge reading threads thread class to raise the pressure in the Boiler class pressure extends Thread synchronized Void RaisePressureO i1 quot A N quot r 39 imit15 Wait brie y to simulate some calculations try sleep100 catch Exception e SteamBoilerpressureGauge 15 raise the pressure 15 psi Systemoutprintln Thread thisgetName nds pressure Within limiw increases pressure else Systemoutprintln Thread thisgetName nds pressure too high do nothing public Void run RaisePressureO this thread is to raise the pressure I GMT 4714 Threading Part 2 Page 52 Mark Llewellyn I 6 CNT 4714 Enterprise Computing Fall 2009 SQL Queries Instructor Dr Mark Llewellyn marklcsucfedu HEC 236 4078232790 httpwwwcsucfeducoursescnt4714fall2009 School of Electrical Engineering and Computer Science University of Central Florida CNT4714 Enterprise Computing SQL Queries Page 1 Mark Llewellyn The SQL Environment Catalog A set of schemas that constitute the description of a database Schema The structure that contains descriptions of objects created by a user base tables Views constraints Data De nition Language DDL Commands that define a database including creating altering and dropping tables and establishing constraints Data Manipulation Language DML Commands that maintain and query a database Data Control Language DCL Commands that control a database including administering privileges and committing data GMT 4714 Enterprise Computing SQL Queries Page 2 Mark Llewellyn 6 DDL DML DCL and the database development process DDL Define the database CREATE tables withinas1 views Establish loreign keys Drop or truncate tables Physical Design DML Load the database INSERT data UPDATE the database 39mp39ememat39on Manipulate the database 39 1 SELECT DCL fli IControl the database t GRANI ADD REVOKE Maintenance GMT 4714 Enterprise Computing SQL Queries Page 3 Mark Llewellyn SELECT Statement Used for queries on single or multiple tables Clauses of the SELECT statement SELECT List the columns and expressions that should be returned from the query FROM Indicate the tables or Views from which data will be obtained WHERE Indicate the conditions under which a row will be included in the result GROUP BY Indicate categorization of results HAVING Indicate the conditions under which a category group will be included ORDER BY Sorts the result according to speci ed criteria GMT 4714 Enterprise Computing SQL Queries Page 4 Mark Llewellyn 6 SQL statement FROM Identi es involved tables processing order WHERE Finds all rows meeting stated conditionis 1r GROUP BY Organizes rows according to values in stated colwmnuis V HAVING Finds all grruups mneeting stated conditionis SELECT Identi es columns V D RD E R EV Sorls rows wlv results GMT 4714 Enterprise Computing SQL Queries Page 5 Mark Llewellyn The following slides create tables for this enterprise data model CUSTOMER PRODUCT Places Has ls placed by ls for Contains ORDER ORDER is contained in LINE GMT 4714 Enterprise Computing SQL Queries Page 6 Mark Llewellyn SELECT Example Find products with standard price less than 275 SELECT PRODUCTNAME STANDARDPRICE FROM PRODUCTV WHERE STANDARDPRICE lt 275 GMT 4714 Enterprise Computing SQL Queries Page 7 Mark Llewellyn e SELECT Example using Alias Alias is an alternative column or table name SELECT CUSTCUSTOMER AS NAME CUSTCUSTOMERADDRESS FROM CUSTOMERV CUST WHERE NAME Home Furnishings GMT 4714 Enterprise Computing SQL Queries Page 8 Mark Llewellyn 6 SELECT Example Using a Function Using the COUNT aggregate function to nd totals SELECT COUNTC FROM ORDERLINEV WHERE ORDERID 1004 Note with aggregate functions you can t have single Valued columns included in the SELECT clause GMT 4714 Enterprise Computing SQL Queries Page 9 Mark Llewellyn 6 SELECT Example Boolean Operators AND OR and NOT Operators for customizing conditions in WHERE clause SELECT PRODUCTDESCRIPTION PRODUCTFINISH STANDARDPRICE FROM PRODUCTV WHERE PRODUCTDESCRIPTION LIKE Desk OR PRODUCTDESCRIPTION LIKE Table AND UNITPRICE gt 300 Note the LIKE operator allows you to compare strings using Wildcards For example the Wildcard in Desk indicates that all strings that have any number of characters preceding the word Desk Will be allowed GMT 4714 Enterprise Computing SQL Queries Page 10 Mark Llewellyn 6 SELECT Example Sorting Results with the ORDER BY Clause Sort the results rst by STATE and Within a state by CUSTOMERNAME SELECT CUSTOMERNAME CITY STATE FROM CUSTOMERV WHERE STATE IN FL TX CA HI ORDER BY STATE CUSTOMERNAME Note the IN operator in this example allows you to include rows Whose STATE value is either FL TX CA or HI It is more efficient than separate OR conditions GMT 4714 Enterprise Computing SQL Queries Page 11 Mark Llewellyn 6 SELECT Example Categorizing Results Using the GROUP BY Clause For use with aggregate functions Scalar aggregate single value returned from SQL query With aggregate function Vector aggregate multiple values returned from SQL query with aggregate function Via GROUP BY SELECT STATE COUNTSTATE FROM CUSTOMERV GROUP BY STATE Note you can use singlevalue elds with aggregate functions if they are included in the GROUP BY clause GMT 4714 Enterprise Computing SQL Queries Page 12 Mark Llewellyn 6 SELECT Example Qualifying Results by Category Using the HAVING Clause For use with GROUP BY SELECT STATE COUNTSTATE FROM CUSTOMERV GROUP BY STATE HAVING COUNTSTATE gt 1 Like a WHERE clause but it operates on groups categories not on individual rows Here only those groups With total numbers greater than 1 will be included in nal result GMT 4714 Enterprise Computing SQL Queries Page 13 Mark Llewellyn Processing Multiple Tables Joins JOlIl a relational operation that causes two or more tables with a common domain to be combined into a single table or View Equijoin a join in which the joining condition is based on equality between values in the common columns common columns appear redundantly in the result table Natural jOlIl an equijoin in which one of the duplicate columns is eliminated in the result table Outer jOlIl a join in which rows that do not have matching values in common columns are nonetheless included in the result table as opposed to inner join in which rows must have matching values in order to appear in the result table Union jOlIl includes all columns from each table in the join and an instance for each row of each table The common columns in joined tables are usually the primary key of the dominan table and the 7 of theide iendenttble in 1M relations 39 E GMT 4714 Enterprise Computing SQL Queries Page 14 Mark Llewellyn I I Natural Join Example For each customer who placed an order What is the customer s name and order number I Join involves multiple tables in FROM clause I SELECT CUSTOMER T MER ID CUSTOMERNAME ORDERID FRoM CUSTOMER T ORDER T WHERIE CUSTOMERTCUSTOMERID ORDERTCUSTOMERID T WHERE clailse performs the equality check for common columns of the two tables GMT 4714 Enterprise Computing SQL Queries Page 15 Mark Llewellyn 6 Outer Join Example List the customer name ID number and order number for all customers Include customer information even for customers that do have an order SELECT CUSTOMERTCUSTOMERID CUSTOMERNAME 0R DPRTD FROl lI CUSTOMER T LEFT OUTER JOIN ORDER Tl USTOMERT1 USTOMERID ORDERTCUSTOMERID LEFT OL39JTER JOIN syntax with ON keyword instead of WHERE 9 causes customer data to appear even if there is no corresponding order data GMT 4714 Enterprise Computing SQL Queries Page 16 Mark Llewellyn 6 Multiple Table Join Example Assemble all information necessary to create an invoice for order number 1006 I Four tables invplved in this join SELECT CUSTOMERTCUSTOMERID CUSTOMERN ME CUSTOMERADDRESS CITY SATE POSTALCOD ORDERTORDERID ORDERDATE QUANTITY PRODUCT NAME UNIT PRICE QUANTITY U T PRICE FRoM CUSTOMERT ORDERT ORDERLINET PRODUCTT WHERE CUSTOMERTCUSTOMERID ORDERLINECUSTOMERID AND ORDERTORDERID ORDERLINETORDERID AND ORDERLINETPR DUCTID PRODUCTPRODUCTID AND ORDERTORDERID 1006 Each pair of tables requires an equalitycheck condition in the WHERE clause matching primary keys against foreign keys GMT 4714 Enterprise Computing SQL Queries Page 17 Mark Llewellyn Processing Multiple Tables Using Subqueries Subquery placing an inner query SELECT statement inside an outer query Options In a condition of the WHERE clause As a table of the FROM clause Within the HAVING clause Subqueries can be Noncorrelated executed once for the entire outer query Correlated executed once for each row returned by the outer query GMT 4714 Enterprise Computing SQL Queries Page 18 Mark Llewellyn 6 Subquery Example Show all customers who have placed an order The IN operator will test to see if the CUSTOMERID value of a row is included in the list returned from the subquery SELECT CUSTOMERNAM ROM CUSTOMERT WHERE CUSTOMER min NI SELECT DISTINCT CUSTOMERID FROM ORDERT1 Subquery is embedded in parentheses In this case it returns a list that Will be used in the WHERE clause of the outer query GMT 4714 Enterprise Computing SQL Queries Page 19 Mark Llewellyn 6 Correlated vs Noncorrelated Subqueries Noncorrelated subqueries Do not depend on data from the outer query Execute once for the entire outer query Correlated subqueries Make use of data from the outer query Execute once for each row of the outer query Can use the EXISTS operator GMT 4714 Enterprise Computing SQL Queries Page 20 Mark Llewellyn Processing 21 noncorrelated subquery 1 The subquery executes and returns the customer IDs from the ORDERT table 2 The outer query on the results of the subquery SELECT CUETDMERNAME FRCIM GUETDMERET WHERE E UETGMERJD BM SELECT DIS39l39lNCT CUETCIMER n3 FnoM DRDER r3 The rqu ahaum in the lynx is increased rst anti an interimdime results table cmmled CUSTOMERle No reference to data 1 in outer query so 1 subquery executes i once only Grows selected 392 The outer grew return 1he requested Guam infcrrna liun fur each customariqu in he imarmeeiabe results tattle CMSTOMRNAME IIImnemme Casuals Value Furniture Iquot ung These are the only Imprmemt customers that have Gallfomia Classics IDS in the ORDERT Battle Creek Furniture El armvs selected GMT 4714 Enterprise Computing SQL Queries Page 21 Amarican Emu Lilieslyi e Mgur am Saunas Mark Llewellyn 6 Correlated Subquery Example Show all orders that include furniture nished in natural ash The EXISTS operator will return a TRUE value if the subquery resulted in a nonempty set otherwise it returns a FALSE SELECT DIS CT ORDERID FROM ORDERLINET WHER a SELECT FROM PRODUCT T WHERE PRODUCTID ORDERLINETPRODUCTI1 AND PRODUCTFINISH Natural ash T The subquery is testing for a value that comes from the outer query GMT 4714 Enterprise Computing SQL Queries Page 22 Mark Llewellyn 6 Processing a correlated subquery Note only the orders that involve products with Natural Ash will be included in the final results SELECT DISTINCT ORDER lD FR DM ORDER LlNE T WHERE EXISTS SELECT quot 1 Inn Jr Imam1n FROM PRODUCT T 39 i39 WHERE PRUDUCT lD ORDER LiNE TuF39RDDUCT ID 339 ANCI F39R39DDUCT FINISH Natural AM39 Subquery refers to outer query data so executes once for each row of outer query Il rlllll E Illllilll I Find Ill Pm lint Florin with Standard F1193 Fmtltr UM kl L n ls Char 517395 50 10301 2 HikeTable Cigalural rgtu 20303 2mm 39 4 3 CompmerDask gural A5117 5mm 2mm 4 Er lrlalr39rmln Canlrur Mil51rd Staph 3mm S Writer s Desk Cherry 532511 lElJlJl 39 E El rawer Vassar Whitth 53510 20301 w 7 Cllrmg Table Citing r mam 2mm E CompmarDask Wain 525003 30301 539 39 rum 1 The first order ID is selected From G DER LINE T ORDER ID 1 E3101 2 The subquary is Evaluated tr see if any product m that ardar has a natural ash linrsh Filmduct 2 does and is part at the order EXISTS is valued as true and the larder ID is added to the result table 3 The next order ID is selected from ORDER ILINE T ORDER ID 1ODE 4 The gubquary is Evaluated to see if the pmctuul ordered has a natural ash iimsh lt tines EXISTS is valued as true and the order ID is added to the result table 5 Processth continues through each order ID Orders 1004 1005 and 1011 are nui included in the result table because they do not include any furniture with a natural ash nish The final result table is shown in the rain on page 303 GMT 4714 Enterprise Computing SQL Queries Page 23 Mark Llewellyn Another Subquery Example Show all products Whose price is higher than the average One column of the subquery is an aggregate function that has an alias name That alias can then be referred to in the outer query Subquery forms the derived table used in the FROM clause of the outer query TPRODIICTDESCRIPTI N STANDARDPRICE AVGPR CE SFI FROM SELECT LVGSTANDARD PRICE AVGPRICEIFROM PRODUCT T PRODUCT T WHEREI STANDARDPRICE gt AVGPRICE The WHERE clause normally cannot include aggregate functions but because the aggregate is performed in the subquery its result can be used in the outer query s WHERE clause GMT 4714 Enterprise Computing SQL Queries Page 24 Mark Llewellyn 6 SQL Join Operations The SQL join operations merge rows from two tables and returns the rows that 1 Have common values in common columns natural join or 2 Meet a given join condition equality or inequality or 3 Have common values in common columns or have no matching values outer join We ve already examined the basic form of an SQL join which occurs when two tables are listed in the FROM clause and the WHERE clause speci es the join condition An example of this basic form of the join is shown on the next page GMT 4714 Enterprise Computing SQL Queries Page 25 Mark Llewellyn 6 SQL Join Operations cont SELECT PCODE PDESCRPT PPRCE VNAME FROM PRODUCT VENDOR WHERE PRODUCTVCODE VENDORVCODE The FROM clause indicates which tables are to be joined If three or more tables are speci ed the join operation takes place two tables at a time starting from left to right The join condition is specified in the WHERE clause In the example a natural join is effected on the attribute VCODE The SQL join syntax shown above is sometimes referred to as an oldstyle join The tables on pages 38 and 39 summarize the SQL join operations GMT 4714 Enterprise Computing SQL Queries Page 26 Mark Llewellyn 6 SQL Cross Join Operation A cross join in SQL is equivalent to a Cartesian product in standard relational algebra The cross join syntax is SE L ECT columnlist F RO M table 1 tab le2 SE LECT columnlist FROM tab61 CROSS JOIN tabe2 GMT 4714 Enterprise Computing SQL Queries Page 27 Mark Llewellyn 6 SQL Natural Join Operation The natural join syntax is SE LECT columnlist i FROM tab61 NATURAL JOIN tab62 The natural join will perform the following tasks Determine the common attributes by looking for attributes with identical names and compatible data types Select only the rows with common values in the common attributes If there are no common attributes return the cross join of the two tables GMT 4714 Enterprise Computing SQL Queries Page 28 Mark Llewellyn G SQL Natural Join Operation cont The syntax for the oldstyle natural join is S E L ECT columnlist FROM table 1 table2 WH E RE table 1 C1 z able2 CZ One important difference between the natural join and the oldstyle syntax is that the natural join does not require the use of a table quali er for the common attributes The two SELECT statements shown on the next page are equivalent GMT 4714 Enterprise Computing SQL Queries Page 29 Mark Llewellyn 6 SOL Natural Join Operation cont SELECT CUSNUM CUSLNAME NVNUMBER NVDATE FROM CUSTOMER INVOICE WHERE CUSTOMERCUSNUM INVOICE CUSNUM SELECT CUSNUM CUSLNAME NVNUMBER INVDATE FROM CUSTOMER NATURAL JOIN INVOICE GMT 4714 Enterprise Computing SQL Queries Page 30 Mark Llewellyn Join With Using Clause A second way to express a join is through the USING keyword This query will return only the rows with matching values in the column indicated in the USING clause The column listed in the USING clause must appear in both tables The syntax is SELECT columnlist FROM table1 JOIN tabe2 USING commoncolumn CNT 4714 Enterprise Computing SQL Queries Page 31 Mark Llewellyn 6 Join With Using Clause cont An example SELECT INVNUMBER PCODE PDESCRIPT LINEUNITS LINEPRICE FROM INVOICE JOIN LINE USING INVNUMBER JOIN PRODUCT USING PCODE As was the case with the natural join command the JOIN USING does not required the use of quali ed names quali ed table names In fact Oracle 9i will return an error if you specify the table name in the USING clause GMT 4714 Enterprise Computing SQL Queries Page 32 Mark Llewellyn 6 Join On Clause Both the NATURAL JOIN and the JOIN USING commands use common attribute names in joining tables Another way to express a join when the tables have no common attribute names is to use the JOIN ON operand This query will return only the rows that meet the indicated condition The join condition will typically include an equality comparison expression of two columns The columns may or may not share the same name but must obviously have comparable data types The syntax is S E LEC T columnlist FROM tablei JOIN tabe2 ON joincondition GMT 4714 Enterprise Computing SQL Queries Page 33 Mark Llewellyn e Join On Clause cont An example SELECT INVOICEINVNUMBER PCODE PDESCRIPT LINEUNITS LINEPRICE FROM INVOICE JOIN LINE ON INVOICEINVNUMBER LINEINVNUMBER JOIN PRODUCT ON LINEPCODE PRODUCTPCODE Notice in the example query that unlike the NATURAL JOIN and the JOIN USING operation the JOIN ON clause requires the use of table quali ers for the common attributes If you do not specify the table quali er you will get a column ambiguously de ned error message Keep in mind that the JOIN ON syntaX allows you to perform a join even when the tables do not share a common attribute name GMT 4714 Enterprise Computing SQL Queries Page 34 Mark Llewellyn 6 Join On Clause cont For example to general a list of all employees with the manager s name you can use the recursive query shown below which utilizes the JOIN ON clause SELECT EEMPMGR MEMPLNAME EEMPNUM EEMPLNAME FROM EMP E JOIN EMP M ON EEMPMGR MEMPNUM ORDER BY EEMPMGR GMT 4714 Enterprise Computing SQL Queries Page 35 Mark Llewellyn 6 Outer Joins We saw the forms for the LEFT OUTER JOIN and the RIGHT OUTER JOIN in the previous set of notes There is also a FULL OUTER JOIN operation in SQL A full outer join returns not only the rows matching the join condition that is rows with matching values in the common columns but also all the rows with unmatched values in either side table The syntax of a full outer join is SELECT columnlist FROM tabe1 FULL OUTER JOIN tabe2 ON joincondition GMT 4714 Enterprise Computing SQL Queries Page 36 Mark Llewellyn 6 Outer Joins cont The following example will list the product code vendor code and vendor name for all products and include all the product rows products Without matching vendors and also all vendor rows vendors Without matching products SELECT PCODE VENDORVCODE VNAME FROM VENDOR FULL OUTER JOIN PRODUCT ON VENDOR VCODE PRODUCTVCODE GMT 4714 Enterprise Computing SQL Queries Page 37 Mark Llewellyn 6 Summary of SQL JOIN Operations Join Join Type SQL Syntax Example Description Classification Cross CROSS SELECT Old style Returns the Cartesian product of T1 and JOIN FROM T1 T2 T2 SELECT New style Returns the Cartesian product of T1 and FROM T1 CROSS JOIN T2 T2 Inner Old Style SELECT Returns only the rows that meet the join condition in JOIN FROM T1 1 T2 the WHERE clause 7 old style Only rows with WHERE T1 01 T2 01 matching values are selected NATURAL SELECT Returns only the rows with matching values in the JOIN FROM T1 NATURAL JON T2 matching columns The matching columns must have the same names and similar data types JOIN USING SELECT Returns only the rows with matching values in the FROM T1 JON T2 USING columns indicated in the USING clause C1 JOIN ON SELECT Returns only the rows that meet the join condition FROM T1 JOIN T2 ON T1 01 T2C1 indicated in the ON clause GMT 4714 Enterprise Computing SQL Queries Page 38 Mark Llewellyn Summary of SQL JOIN Operations cont Join Join Type SQL Syntax Example Description Classification Outer LEFT JOIN SELECT Returns rows with matching values and includes all FROM T1 LEFT OUTER rows from the left table T1 with unmatched values JOIN T2 ON T1 C1 T2C1 RIGHT JOIN SELECT Returns rows with matching values and includes all FROM T1 RIGHT OUTER rows from the right table T2 with unmatched JON T2 values ON T1 C1 T2C1 FULL JOIN SELECT Returns rows with matching values and includes all FROM T1 FULL OUTER JOIN T2 ON T1 C1 T2C1 rows from both tables T1 and T2 with unmatched values GMT 4714 Enterprise Computing SQL Queries Page 39 Mark Llewellyn Subqueries and Correlated Queries The use of joins allows a RDBMS go get information from two or more tables The data from the tables is processed simultaneously It is often necessary to process data based on other processed data Suppose for example that you want to generate a list of vendors who provide products Recall that not all vendors in the VENDOR table have provided products some of them are only potential vendors The following query will accomplish our task SELECT VCODE vNAME FROM VENDOR WHERE VCODE NOT IN SELECT VCODE FROM PRODUCT GMT 4714 Enterprise Computing SQL Queries Page 40 Mark Llewellyn e Subqueries and Correlated Queries cont A subquery is a query SELECT statement inside a query A subquery is normally expressed inside parentheses The rst query in the SQL statement is known as the outer query The second query in the SQL statement is known as the inner query The inner query is executed rst The output of the inner query is used as the input for the outer query The entire SQL statement is sometimes referred to as a nested query GMT 4714 Enterprise Computing SQL Queries Page 41 Mark Llewellyn 6 Subqueries and Correlated Queries cont A subquery can return 1 One single value one column and one row This subquery can be used anywhere a single value is expected For example in the right side of a comparison expression 2 A list of values one column and multiple rows This type of subquery can be used anywhere a list of values is expected For example when using the IN clause 3 A virtual table multicolumn multirow set of values This type of subquery can be used anywhere a table is expected For example in the FROM clause 4 No value at all ie NULL In such cases the output of the outer query may result in an error or null empty set depending on where the subquery is used in a comparison an expression or a table set GMT 4714 Enterprise Computing SQL Queries Page 42 Mark Llewellyn 6 Correlated Queries 0 A correlated query really a subquery is a subquery that contains a reference to a table that also appears in the outer query 0 A correlated query has the following basic form SELECT FROM table1 WHERE co1 ANY SELECT co1 FROM table2 WHERE table2col2 table1co1 0 Notice that the subquery contains a reference to a column of table1 even though the subquery s FROM clause doesn t mention table1 Thus query execution requires a look outside the subquery and finds the table reference in the outer query GMT 4714 Enterprise Computing SQL Queries Page 43 Mark Llewellyn 6 WHERE Subqueries The most common type of subquery uses an inner SELECT subquery on the right hand side of a WHERE comparison expression For example to nd all products with a price greater than or equal to the average product price the following query would be needed SELECT PCODE PPRCE FROM PRODUCT WHERE PPRCE gt SELECT AVGPPRCE FROM PRODUCT GMT 4714 Enterprise Computing SQL Queries Page 44 Mark Llewellyn 6 WHERE Subqueries cont Subqueries can also be used in combination with joins The query below lists all the customers that ordered the product Claw hammer SELECT DISTINCT CUSCODE cusLNAME CUYSFNAME FROM CUSTOMER JOIN INVOICE USING CUSCODE JOIN LINE USING INVNUMBER JOIN PRODUCT USING PCODE WHERE PCODE SELECT PCODE FROM PRODUCT WHERE PDESCRPT Claw hammer GMT 4714 Enterprise Computing SQL Queries Page 45 Mark Llewellyn 6 WHERE Subqueries cont Notice that the previous query could have been written as SELECT DISTINCT CUSCODE CUSLNAME CUY SFNAME FROM CUSTOMER JOIN INVOICE USING CUSCODE JOIN LINE USING INvNUIvIBER JOIN PRODUCT USING PCODE WHERE PDESCRPT Cla w hammer However what would happen if two or more product descriptions contain the string Claw hammer You would get an error message because only a single value is expected on the right hand side of this expression GMT 4714 Enterprise Computing SQL Queries Page 46 Mark Llewellyn 6 IN Subqueries To handle the problem we just saw the IN operand must be used The query below lists all the customers that ordered any kind of hammer or saw SELECT DISTINCT CUSCODE CUSLNAME CUYSFNAME FROM CUSTOMER JOIN INVOICE USING CUSCODE JOIN LINE USING INVNUMBER JOIN PRODUCT USING PCODE WHERE PCODE IN SELECT P39CODE FROM PRODUCT WHERE PDESCRIPT LIKE h ammer OR PDESCRIPT LIKE saw CNT 4714 Enterprise Computing SQL Queries Page 47 Mark Llewellyn 6 HAVING Subqueries It is also possible to use subqueries with a HAVING clause Recall that the HAVING clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows For example the following query will list all products with the total quantity sold greater than the average quantity sold SELECT DISTINCT PCODE SUMLNEUNITS FROM LINE GROUP BY PCODE HAVING SUMLNEUNITS gt SELECT AVGLNEUNTS FROM LINE CNT 4714 Enterprise Computing SQL Queries Page 48 Mark Llewellyn 6 Multirow Subquery Operators ANY and ALL The IN subquery uses an equality operator that is it only selects those rows that match at least one of the values in the list What happens if you need to do an inequality comparison of one value to a list of values For example suppose you want to know What products have a product cost that is greater than all individual product costs for products provided by vendors from Florida SELECT PCODE PONHANDPPRCE FROM PRODUCT WHERE PONHANDPPRCE gt ALL SELECT PONHANDPPRCE FROM PRODUCT WHERE VCODE IN SELECT VCODE FROM VENDOR WHERE vSTATE FL CNT 4714 Enterprise Computing SQL Queries Page 49 Mark Llewellyn 6 FROM Subqueries In all of the cases of subqueries we ve seen so far the subquery was part of a conditional expression and it always appeared on the right hand side of an expression This is the case for WHERE HAVING and IN subqueries as well as for the ANY and ALL operators 0 Recall that the FROM clause speci es the tables from which the data will be drawn Because the output of a SELECT statement is another table or more precisely a Virtual table you could use a SELECT subquery in the FROM clause 0 For example suppose that you want to know all customers who have purchased products l3Q2P2 and 23lO9HB Since all product purchases are stored in the LINE table it is easy to nd out who purchased any given product just by searching the PCODE attribute in the LINE table However in this case you want to know all customers who purchased both not just one o The query on the next page accomplishes this task GMT 4714 Enterprise Computing SQL Queries Page 50 Mark Llewellyn 6 FROM Subqueries cont SELECT DISTINCT CUSTOMERCUSCODE FROM CUSTOMER SELECT NVOCECUSCODE FROM INVOICE NATURAL JOIN LINE WHERE PCODE 13Q2P2 CP I SELECT NVOCECUSCODE FROM INVOICE NATURAL JOIN LINE WHERE PCODE 23109 HB CP2 WHERE CUSTOMERCUSCODE CP1CUSCODE AND CP ICUSCODE CP2CUSCODE CUSTOMERLNAME GMT 4714 Enterprise Computing SQL Queries Page 51 Mark Llewellyn EEII I J Subqueries in MySQL The ability to handle subqueries like we ve just examined was not available in MySQL until version 41 If you are using a version of MySQL earlier than 41 you will need to download the latest version 50 before you begin to work on the next assignment which will involve the execution of subqueries There are a number of other enhancements that became active with version 41 that are extremely useful and we will examine a number of these over the coming days GMT 4714 Enterprise Computing SQL Queries Page 52 Mark Llewellyn 6 Subqueries in MySQL cont Subqueries are also useful in optimizing queries as they can be used to eliminate more costly join operations Consider the following general query SELECT DISTINCT table1col1 FROM table l table2 WHERE table1col1 table2col1 This query can be more efficiently expressed using subqueries as SELECT DISTINCT COI I FROM table l WHERE table lco1 IN SELECT COI I FROM table2 GMT 4714 Enterprise Computing SQL Queries Page 53 Mark Llewellyn 6
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'