I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY violation occurs, SQLite will continue to run, but performance slows down by about 1000x. Breaking into the code shows that it seems to be continually re-reading the database. When I finally get to COMMIT , an exception is thrown, but this can take hours , literally as I have a single global transactions.
Of course the solution is to debug the problem by setting the mode to IMMEDIATE, detecting the issue at the source, then returning to DEFERRED. But the '1000x slowdown' behavior is an interesting quirk I don't really understand. Andrew