Sedation without going into general anesthesia is not an easy thing at all, and even experienced anesthesia providers will sometimes struggle. However, patients moving or being otherwise disoriented is quite frequent and in itself is a non-event except when trying to reach for the surgical site, as you describe. As for distress, it's often not from pain but rather confusion, which can somtimes devolve into agitation and necessity to deepen the anesthesia. In my experience, true awareness is exceptionally rare. It's true that some procedures, such as cardiac or emergency C-section have an increased risk of event due to the particular context, though.