Transactions are actually incredibly tricky to get right, particularly in a portable manner. Not exactly the sort of thing one can implement in a weekend.

For example: http://sqlite.org/atomiccommit.html

Also, it will slow down the database a bit (particularly if added to the db as an afterthought!), which seems to really irritate people who would rather have potentially corrupt information as soon as possible rather than automatic guarantees of internal consistency.