In the mid-1970s, film transitions were usually elegant: fades, dissolves, wipes. The Python team—specifically co-directors Terry Gilliam and Terry Jones—had other ideas. The Holy Grail klaxxon is a formal joke. It’s a meta-joke about storytelling.
In the real world, a klaxon is a type of electromechanical horn used on cars, ships, and air raid sirens. In the 1975 film, the sound effect was likely lifted from a standard BBC sound effects library (possibly a "horn, klaxon, double blast"). But what makes the Holy Grail klaxxon unique is not the sound itself, but when and how it is used.