SQL Zone is brought to you in partnership with:

Mitch Pronschinske is the Lead Research Analyst at DZone. Researching and compiling content for DZone's research guides is his primary job. He likes to make his own ringtones, watches cartoons/anime, enjoys card and board games, and plays the accordion. Mitch is a DZone Zone Leader and has posted 2576 posts at DZone. You can read more from them at their website. View Full User Profile

SQLite Gets Write-Ahead Logs

07.21.2010
| 8861 views |
  • submit to reddit
There's reason to celebrate today as SQLite 3.7 is released in GA.  The lightweight, ubiquitous database includes some game-changing new features, which have arrived about two years after the 3.6 beta came out.  Today's release will feature nested transactions, constrained foreign keys, write-ahead logs, and more.

You can probably find a few devices around you right now that use SQLite.  The FOSS database engine is embedded in everything from Skype and AIR, to programming language bundles like Python and PHP, to browsers like Firefox and Chrome, and all major smartphones.  

SQLite is an in-process library that can be used when a client/server database is not the best option.  It implements a serverless, self-contained, transactional SQL DB engine that requires no configuration.  SQLite 3.7 will add query planner enhancements, nested transactions, constrained foreign keys, and write-ahead logging, which has been one of the most anticipated new features.

Write-Ahead Logging (WAL)

There are several distinct advantages to using WAL instead of SQLite's rollback journal for implementing atomic commits and rollbacks.

  1. In many cases, WAL is much faster.
  2. Better concurrency.  WAL doesn't let readers block writers and vice versa.  
  3. WAL's disk I/O operations are usually more sequential.
  4. Uses fewer fsync() operations, making it less vulnerable to issues in systems with broken fsync() system calls

Some disadvantages in this approach include an extra operation of checkpointing and less appeal as an application file-format.  WAL is only available if the VFS supports shared-memory primitives.  WAL also does not work over a network filesystem.

Oracle Joins SQLite

In related news, Oracle joined the SQLite Consortium last month.  This means that Oracle will be have access to more complete test suites for validating Berkeley DB's SQLite API, which was unveiled in March.  Oracle Senior Project Manager Gregory Burd had nothing but glowing compliments for the little engine that can.  He said that SQLite 3's ANSI C API is the "new 'ODBC'" for relational DB access, and that SQLite makes "the right trade-offs."  Now Oracle has plans to continually work with SQLite developers, which means that conservative project manager of yours can get behind using SQLite knowing that Oracle is now behind it.

The documentation should show you how to migrate from 3.6 to 3.7.  Go here to find out how to use SQLite in different languages