Inside Symbian SQL: A Mobile Developers Guide to SQLite
ISBN: 0470744022
EAN: 2147483647
EAN: 2147483647
Year: 2004
Pages: 29
Pages: 29
Authors: Ivan Litovski, Richard Maynard
Transaction Management > Transaction Types
Managing transactions is crucial for database consistency. Database systems implement ACID properties to ensure consistencies. SQLite relies on native file locking and page journaling to implement ACID properties. You may recall that SQLite supports only flat transactions; it does not have nesting and savepoint capabilities.
SQLite executes each SQL statement in a transaction. It supports both read- and write-transactions. Applications cannot read data from a database except in a read- or write-transaction, and they cannot write into a database except in a write-transaction. They do not need to explicitly tell SQLite to execute individual SQL statements within transactions. SQLite automatically does so; this is the default behavior, and the system is said to be in autocommit mode. Those transactions are called automatic or system level transactions. For a SELECT statement, SQLite creates a read-transaction. For a non-SELECT statement, SQLite first creates a read-transaction, and then converts it into a write-transaction. Each transaction is automatically committed (or aborted) at the end of the statement execution. Applications are not aware of system transactions. They submit SQL statements to SQLite, which takes care of the rest as far as ACID properties are concerned. Applications receive back outcome of the SQL execution from SQLite. An application can initiate concurrent executions of SELECT statements (read-transactions) on the same database connection, but it can initiate one non-SELECT (write-transaction) on an idle connection.
The autocommit mode might be expensive for some applications, especially for those that are highly write intensive, because SQLite requires reopening, writing to, and closing the journal file for each non-SELECT statement. You will see shortly that SQLite discards the page-cache at the end of each statement (including SELECT) execution in the autocommit mode. It rebuilds the cache for each statement execution. Cache rebuilding is a costly, inefficient action, as it involves doing I/O from disk. In addition, there is also concurrency control overhead, as applications need to reacquire and release locks on database files for each SQL statement execution. This overhead can incur a significant performance penalty (especially for large applications), and can only be curtailed by opening a user level transaction surrounding many SQL statements, as shown in the "Working with multiple databases" section. Here's another simple SQLite application that creates a user transaction:
BEGIN; INSERT INTO tablel values(100); INSERT INTO table2 values(20, 100); UPDATE tablel SET x=x+1 WHERE y> 10; INSERT INTO table3 VALUES (1,2,3); COMMIT;
An application can manually start a new transaction by explicitly executing a BEGIN command. The transaction is referred to as a user level transaction (or simply a user transaction). When this is done, SQLite leaves the default autocommit mode; it does not invoke a commit or abort at the end of each SQL statement execution, nor does it discard the page cache. Successive SQL statements become a part of the user transaction. SQLite commits (respectively, aborts) the transaction when the application executes the COMMIT (respectively, ROLLBACK) command. If the transaction aborts or fails, or the application closes the connection, the entire transaction is rolled back. SQLite reverts back to the autocommit mode on completion of the transaction.
NOTE
SQLite supports only flat transactions. Also, an application cannot open more than one user transaction on a database connection at a time. You'll get an error if you execute a BEGIN command inside a user transaction.
A user transaction is a little more than a flat transaction. Each non-SELECT statement in the transaction is executed in a separate statement level subtransaction. Although SQLite does not have the general savepoint capability, it does, however, ensure savepoint for the current statement subtransaction. If the current statement execution fails, SQLite does not abort the user transaction. It instead restores the database to the state prior to the start of the statement execution; the transaction continues from there. The failed statement does not alter the outcome of other previously executed SQL statements, or the new ones, unless the main user transaction aborts itself. SQLite helps a long user transaction to withstand some statement failures amicably.
In the above example, each of the four SQL statements is executed in a separate subtransaction, one after another. If, for example, a constraint error occurs on the tenth row of the UPDATE, all previous nine rows of that update will be rolled back, but the changes from the three INSERTs (surrounding the UPDATE) will be committed when the application executes the COMMIT command.