I've tested SQLite and came to the conclusion that it is a very awesome SQL implemention, especially for applications on embedded devices. It's working rather well for web-based projects, too. One thing that concerns me a bit, though, is the database-level locking mechanism. Transactions will probably help with performance but for projects with many concurrent users, the database-level locking may prove to be somwhat of a bottleneck unless one uses the nosync option, which then defeats the purpose of using transactions or may leave the database file corrupted in case of a power outage. This however, is very neat:
Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:". If the main database is ":memory:" then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not. Atomic commit of attached databases is a new feature of SQLite version 3.0. In SQLite version 2.8, all commits to attached databases behaved as if the main database were ":memory:".
If the locking mechanism were to be improved (probably nearly impossible given that there's no database server process), and table inheritance was implemented similar to PostgreSQL's implementation, and MySQL's SQL_CALC_FOUND_ROWS was supported, I'd be in heaven!