Specifically in decoupling and bypass applications, can the Self Resonance frequency cause problems (compared to ideal)? If so, what kind of problems can it cause?
An ideal capacitor has an impedance that falls with increasing frequency, which is good for decoupling high-frequency noise.
However, real capacitors have some amount of parasitic inductance, which appears in series with the capacitance, forming a series-resonant circuit.
Such a circuit has a minimum impedance at its resonant frequency, and at frequencies higher than that, the impedance starts rising again, which is less useful for decoupling.
That's why it's sometimes useful to use a number of different capacitors to decouple wide-bandwidth applications; each one provides the low impedance required for a particular band of frequencies.
But beware of strange cross-resonant effects! Sometimes the capacitance of one capacitor will interact with the inductance of another capacitor to create a parallel-resonant circuit, which has a very high impdeance at its resonant frequency. Verify your implementation with a wide-band network analyzer.
A bypass capacitor is never in isolation, even when there is only "one" on a board. There are internal (i.e on chip) bypass caps, formed from gate capacitance or MIM/PIP (Metal Insulator Metal or Poly Insulator Poly), there is the bond wire capacitance, the stray capacitance with other pins, capacitance of traces to ground. When the whole of these are considered then ...
Absolutely, this resonance would be tickled/sparked or excited by frequency content present in the current surges that are meant to be suppressed by the by-pass caps. In that case not only is there no energy absorption of unwanted signal but there may also be amplification or additional energy in very distinct frequencies being impressed upon those pins.
In most designs the the higher the frequency the harder it is to have good PSRR (Power Supply Rejection Ratio) and thus this resonant frequency likely would be noticeable).
Take a look at the results from here, a snip from a salient plot is shown below: (please the article for context)