I've seen the term used, but what is a "diffraction limit", when should I worry about it, and what undesirable effects are a result of it ?
There have been some very good answers, however there are a couple details that have not been mentioned. First, diffraction always happens, at every aperture, as light bends around the edges of the diaphragm and creates an "Airy Disk". The size of the airy disk, and the proportion of the disk that comprises the outer rings, and the amplitude of each wave in the outer rings, increases as the aperture is stopped down (the physical aperture gets smaller.) When you approach photography in the way Whuber mentioned in his answer:
You realize that every one of those points of light, when focused by your lens, is generating its own airy disk on the imaging medium.
Regarding Image Medium
It should also be clearly noted that the diffraction limit is not actually a limitation of a lens. As noted above, lenses are always creating a diffraction pattern, only the degree and extent of that pattern changes as the lens is stopped down. The "limit" of diffraction is a function of the imaging medium. A sensor with smaller photosites, or film with smaller grain, will have a lower limit of diffraction than those with larger photosites/grains. This is due to the fact that a smaller photosite covers less of the airy disk area than a larger photosite. When the airy disk grows in size and intensity as a lens is stopped down, the airy disk affects neighboring photosites.
The diffraction limit is the point where airy disks grow large enough that they begin to affect more than a single photosite. Another way to look at it is when the airy disks from two point light sources resolvable by the sensor begin to merge. At a wide aperture, two point light sources imaged by a sensor may only affect single neighboring photosites. When the aperture is stopped down, the airy disk generated by each point light source grows, to the point where the outer rings of each airy disk begin to merge. This is the point where a sensor is "diffraction limited", since individual point light sources no longer resolve to a single photosite...they are merging and covering more than one photosite. The point at which the center of each airy disk merges is the limit of resolution, and you will no longer be able to resolve any finer detail regardless of the aperture used. This is the diffraction cutoff frequency.
It should be noted that it is possible for a lens to resolve a smaller spot the pixels in an imaging medium. This is the case when airy disks focused by a lens cover only a fraction of a photosite. In this case, even if two highly resolved point light sources generate airy disks that merge over a single photosite, the end result will be the same...the sensor will only detect a single point light regardless of the aperture. The "diffraction limit" of such a sensor would be higher (say f/16) than for a sensor that is able to distinctly resolve both point light sources (which might be diffraction limited at f/8). It is also possible, and likely that point light sources will NOT be perfectly focused onto the center of a photosite. It is entirely plausible for an airy disk to be focused at the border between two photosites, or the junction of four photosites. In a black and white sensor or foveon sensor (stacked color sensels), that would only cause softening. In a color bayer sensor, where a square junction of 4 photosites will be capturing an alternating pattern of GRGB colors, as airy disk can affect the final color rendered by those four photosites as well as cause softening or improper resolution.
My Canon 450D, a 12.2mp APS-C sensor, has a diffraction limit of f/8.4. In contrast, the Canon 5D Mark II, a 21.1mp Full Frame sensor, has a diffraction limit of f/10.3. The larger sensor, despite having nearly twice as many megapixels, can go an extra stop before it encounters its diffraction limit. This is because the physical size of the photosites on the 5D II are larger than those on the 450D. (A good example of one of the numerous benefits of larger sensors.)
Wrenches in the mix
You may often come across tables on the internet that specify a specific diffraction limited aperture for specific formats. I often see f/16 used for APS-C sensors, and f/22 for Full Frame. In the digital world, these numbers are generally useless. The diffraction limiting aperture (DLA) is ultimately a function of the relation of the size of a focused point of light (including the airy disk pattern) to the size of a single light sensing element on a sensor. For any given sensor size, APS-C or Full Frame, the diffraction limit will change depending on the size of the photosites. An example of this can be seen with Canon's EOS Rebel line of cameras over the years:
The story should be similar for film grain size. Films with finer grain would ultimately be more susceptible to diffraction softening at lower apertures than films with larger grains.
The Diffraction Cutoff Frequency
Diffraction is often touted as an image killer, and people talk about the "diffraction limit" as the point at which you can no longer resolve an image "usefully". On the contrary, the diffraction limit is only the point where diffraction starts to affect an image for the particular image medium you are using. The diffraction cutoff frequency is the point at which additional sharpness is impossible for a given aperture, and this is indeed a function of the lens and physical aperture.
The formula for diffraction cutoff frequency for (perfect) optical systems is as follows:
fc = 1 / (λ * f#) cycles/mm
This states that the reciprocal of the wavelength of the light being focused multiplied by the f-number of the lens is the number of cycles per millimeter that can be resolved. The diffraction cutoff frequency is generally the point where resolution reaches the wavelength of the frequencies of light itself. For visible light, λ between 380-750nm, or 0.38-0.75 microns. Until the cutoff frequency has been met for a given aperture, more resolution can be achieved.
Whubers sequence of images above is a decent example of the effect of diffraction, as well as the effect of optical aberrations when the lens is wide open. I think it suffers a bit from some focus shift due to spherical aberration, so I have created an animated GIF that demonstrates the effects of changing the aperture of a Canon 50mm f/1.4 lens down from its widest aperture to its narrowest, in full stops.
(Note: The image is large, 3.8meg, so let it fully download to see the comparison of sharpness at each stop.) The image exhibits marked optical aberration when shot wide open, particularly Chromatic Aberration and some Spherical Aberration (there may be some slight purple fringing...I tried to get focus dead on.) Stopped down to f/2, CA is lessened considerably. From f/2.8 through f/8, sharpness is at its prime, with f/8 being ideal. At f/11, sharpness drops ever so slightly, due to diffraction. At f/16 and particularly f/22, diffraction visibly affects image sharpness. Note that even with diffraction blurring, f/22 is still considerably sharper than f/1.4 or f/2.
Think of a scene as comprised of many small discrete points of light. A lens is supposed to convert each point to another point at an appropriate place on the image. Diffraction causes every point to spread in a circular wave-like pattern, the Airy disk. The diameter of the disk is directly proportional to the f-number: that's the "diffraction limit."
As the f-number is increased from its minimum (a wide open lens), the light falling at a point on the image will come from a narrower region of the lens. That tends to make the image sharper. As the f-number is increased, the Airy disks get larger. At some point the two effects balance to make the sharpest image. This point is typically in the f/5.6 to f/8 range on SLR cameras. With smaller f-numbers, overall properties of the lens (its aberrations) take over to make a softer image. With larger f-numbers, the softness is dominated by the diffraction effect.
You can measure this reasonably well with your own lenses and no special equipment. Mount the camera on a tripod in front of a sharp, detailed, well-lit flat target having lots of contrast. (I used a page from a magazine; it worked fine.) Use your best settings: lowest ISO, proper exposure, mirror locked up, medium focal length for a zoom lens (or vary the focal length, too), middle distance, perfectly in focus, RAW format. Take a series of photos in which you vary only the f/stop and the exposure time (to keep the exposure constant). Look at the sequence of pictures at 100% on a good monitor: you will see where your camera's "sweet spot" is and you will see the effects of using wider or narrower apertures.
The following sequence is taken from a series for the Canon 85 mm f/1.8 lens, which is a pretty good one. From top to bottom are 100% crops (converted to high-quality JPEG for Web display) at f/1.8, 2.8, 5.6, 11, and 22. You can see the increasing effects of diffraction at f/11 and f/22 in the bottom two images. Note that for this particular lens used with this particular camera (EOS T2i, an APS-C sensor), the diffraction softness at high f-numbers doesn't approach the softness seen with the lens wide open. Having comparable information for your own lenses, which can be obtained in a few minutes, can be valuable for choosing exposure parameters in important photos.
Diffraction happens. It's a fact of life. When lenses are used wide open, other lens abberations are far too prominent for you to notice a minor sharpness loss due to diffraction. Stop down a little bit, and those abberations are minimised -- the lens seems to just get better and better. Diffraction is there, but you still don't really notice it because light that is not passing near the edges significantly outvotes the light that is passing getting a little too close to the aperture blades.
At some point while you are stopping the lens down, the gains you make by eliminating the optical differences between the center and outer parts of the lens elements starts to go away -- there is no longer enough crisply focused light to drown out the out-of-focus image caused by light bending around the edges of the optical path (diffraction). The lens isn't going to get any better when you stop down anymore -- too much light is being diffracted compared to the light that's getting through the middle. From this point on, stopping down will make the image softer.
The point at which the lens is stopped down as far as it can go without increasing softness is the diffraction limit. On some lenses, that's as far as you can stop down -- Nikon, for example, has traditionally kept a relatively wide minimum aperture (f/16) on many of their designs. On other lenses (macros, especially) you might still have a couple of stops or more available to you; depth of field considerations may be more important than absolute sharpness in some applications.
All of photography is a compromise. There may be times when you want to stop down farther than the optimum, but it helps to be aware of the compromises you're making. Stopping down is an easy answer to DOF, but if you're hooked on landscapes and taking them all at f/22 or f/32, it may be time to take a look at a tilt/shift lens.
While the answers already here describe diffraction well. Diffraction limit is most often used to describe the point at which stopping down your lens does not give you more details in relation to the pixel-size of your camera's sensor.
When you have reached the diffraction limit of your camera, ANY lens stopped beyond that aperture will give you softer results. It is directly related to the size of individual pixels, not the sensor size.
On modern DSLRs, the diffraction limit will be hit between F/11 and F/16. On cameras with small sensors, it may be F/8 or even less. You'll notice that most tiny cameras do not use apertures smaller than F/8 for this very reason. Some even use a fixed aperture (F/3.5 or so) and simulate less light coming in by slipping a ND filter instead of stopping-down. Unfortunately, they actually put the simulated F-stop in the EXIF, so you need to know the camera to realize it uses an ND filter rather than a normal aperture.
This page at the Cambridge In Color site has a detailed technical explanation of the diffraction limit. It's also got an on-line calculator for checking whether a particular combination of aperture, camera, print size, and viewing distance is diffraction-limited or not.
The Diffraction Limit is the smallest spot a given lens system can create/resolve/focus.
Arm-waving: Lenses can focus light to a small spot but not a point. The spot size can vary with the wavelength, with short wavelengths forming smaller spot sizes than longer ones. When a very good, aberration-free (diffraction-limited) lens is used, collimated light will produce an airy disk as a spot at the focus. An airy disk is still the smallest spot that can be produced with that lens at that aperture with that wavelength (using collimated light). Larger apertures produce smaller spot sizes with tighter focus and reduced depth of focus than smaller apertures.
Note that you cannot produce an airy disk with a pictorial scene. Collimated light does not form an image.
Whoa, Stop right there: Larger numerical apertures produce smaller spots makes sense if you consider that in the formula, the aperture is used as a reciprocal value. Dispersion also plays a role here, too.