Are there any languages that could actually make use of self modifying code? Machines wouldn't have the same problems reasoning about it as humans would. Or is it a question of compilers not being good enough until processor tech made the optimisation not worth it?
LISP macros are compile time self modifying code. So are many other macro systems.
The compiler internals of pretty much any programming language do similar tree to tree transformations: just not ones that the program itself can specify as part of its code. We don't call C self-modifying because a for(;;) loop structure was changed by the compiler into if/goto with generated labels, and then changed again into assembly code.
A macro that mutates the input source code on which it operates wouldn't pass a code review in any competent Lisp shop.
It can happen. For instance (setf (car '(1 2)) 2) is self-modifying code; it tries to replace the 1 with a 2, and that 1 is embedded in the program code itself, in a literal list. This is undefined behavior according to ANSI CL, very similarly to how "abc"[1]++ (modifying a string literal) is undefined behavior in C. Note that this code isn't implementing a macro.
I've never heard of compiled Lisp object code being mutated.
Lisp programs as such can be self-modifying; a common example of that is updating while running: loading new versions of existing modules, replacing old functions with new ones. That doesn't involve modifying code.