The SQLite documentation says it is transactional.
The explanation in the linked article states that if a C++ program which has the SQLite C++ code statically linked into it is forcibly terminated (for example, TerminateProcess() in WinAPI) or crashes when a write is being performed the database remains intact - either fully updated or fully unchanged.
Has anyone actually seen this reliably working? Does this work unconditionally or does it require any additional measures to be taken?
As there is no totally secure way to do atomic transactions there is always the chance to have a corrupted state.
SQLite however takes special care when commiting and should leave the database intact even when the program crashes. It's saver than using flat files and the write-truncate-rename cycle which was the problem in the recent discussion about ext4.
See Atomic Commit for reference on how this is done.
It also depends on your journal mode. If you are journalling to a file and the database does become corrupted there is a good chance it can be recovered.