Android using SQLITE3 wal mode

SQLite supports the write ahead logging (WAL) mode, which opens the Wal mode to increase the speed of writing to the database, which is not blocked between read and write, but is still blocked between write and write, but if you use the default truncate mode, When the data is written, it blocks the read operation of other threads or processes in Android and decreases concurrently. Conversely, using Wal can improve concurrency. Since using Wal is less write I/O than the rollback journal mode, it is faster to write, but it is slower to read because the data is read by the Wal log to verify the correctness of the data. So we have to use this mode according to the scene of your application.

So how do you turn on the Wal mode in Android?

See Sqlitedatabase Open Wal's core method source code.

Public boolean enablewriteaheadlogging ()  {        //  MAKE SURE THE DATABASE IS NOT READONLY. WAL DOESN ' T make  sense for readonly-databases.        if  ( IsReadOnly ())  {            return false;         }        //  acquire lock - no that no other thread is enabling wal  At the same time        lock ();         try {            if   (Mconnectionpool != null)  {                 // already enabled                 return true;            }             if  (Mpath.equalsignorecase (MEMORY _db_path))  {                 LOG.I (tag,  "Can ' t enable wal for memory databases.");                 return  false;            }             // make sure this database has no  attached databases because sqlite ' s write-ahead-logging             // DOESN ' t work for databases with attached databases             if  (Mhasattacheddbs)  {                 if  (log.isloggable (Tag, log.debug) )  {                     LOG.D (tag,                              "This database:   " + mPath + "  has attached databases. can ' t  enable  wal. ");                 }                 return false;             }             mconnectionpool = new databaseconnectionpool (This);             setjournalmode (mpath,  "WAL");             return true;         } finally {             Unlock ();         }    }

In the source notes, it reads: "This method enables parallel execution of queries from multiple threads on the same database." You can support multiple threads through this approach Queries a database concurrently. The example code is given in the comments as follows:

Sqlitedatabase db = Sqlitedatabase.opendatabase ("Db_filename", Cursorfactory,create_if_necessary, Mydatabaseerrorhandler); Db.enablewriteaheadlogging ();

You can turn on the Wal mode by calling Db.enablewriteaheadlogging. Core points of interest in the enablewriteaheadlogging approach:

Mconnectionpool = new Databaseconnectionpool (this); Setjournalmode (MPath, "WAL");

1. Create a database connection pool, which requires connection pooling support because of the need to support concurrent access.

2. Call Setjournalmode to set the mode to Wal.

When the Wal mode is turned on, the beginning of the transaction needs to be noted, and the source code notes are written in this way.

Writers should use {@link #beginTransactionNonExclusive ()} or * {@link #beginTransactionWithListenerNonExclusive (sqlit Etransactionlistener)}

The caller needs to use begintransactionnonexclusive or begintransactionwithlistenernonexclusive to start the transaction, i.e. execution: begin IMMEDIATE; Supports multiple concurrency.

Note: For exclusive and Immediate mode please refer to my other blog http://blog.csdn.net/degwei/article/details/9672795

When the WAL mode update data is turned on, the data is written to the *.db-wal file instead of directly modifying the database file, and the data is updated to the database file when the checkpoint is executed or at some point in time. When the rollback also just clears the Wal log file, and rollback journal mode, when the data is updated, the data needs to be modified back to the journal file, and then modify the database file, when the occurrence of rollback, Remove the data from the journal log, modify the database file, and then clear the journal log. From the above process, Wal in the data update on the i/0 amount of small, so write operations faster.

When you turn on the Wal mode disk, this is the file format, when the data file name is: Test as:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/54/27/wKiom1R5uCGxWAowAAAq7mW3ptI158.jpg "title=" 2.jpg " alt= "Wkiom1r5ucgxwaowaaaq7mw3pti158.jpg"/>

The red part of the graph is the log file for Wal.

Then when the data in the Wal log is updated to the database file, it is mentioned when the checkpoint is manually executed or committed at a point in time by the current thread.

How to manually execute checkpoint, see Sqlitedatabase.endtransaction Source:

/**     * end a transaction. see begintransaction for  notes about how to use this and when transactions      * are committed and rolled back.     */     public void endtransaction ()  {         verifylockowner ();        try {             ...             if  (mtransactionissuccessful)  {                 execsql (Commit_sql);                 // if write-ahead logging is  used, we have to take care of checkpoint.                 // todo: should applications be given the  flexibility of choosing when to                 // trigger checkpoint?                 // for now, do  checkpoint after every commit because that is the fastest                 // way to  guarantee that readers will see latest data.                 // but this is the  Slowest way to run sqlite with in write-ahead logging mode.                 if  (this.mconnectionpool != null)  {                      execsql ("pragma wal_checkpoint;");                      if  (sqlitedebug.debug_sql_statements)  {                         LOG.I (TAG,   "Pragma wal_checkpoint done");                     }                 }                // log the transaction time  to the eventlog.                 if  (enable_db_sample)  {                     logtimestat (Getlastsqlstatement (),  Mtransstarttime, commit_sql);                 }            } else  {               &NBSP, .....             }         } finally {             Mtransactionlistener = null;            unlockforced ();             if  (False)  {                 LOG.V (tag,  "unlocked "  +  Thread.CurrentThread ()                          +  ", holdcount is "  +  mlock.getholdcount ());            }         }    }

In the source note it is written like this: "If write-ahead logging is used, we had to take care of checkpoint." If you use the Wal mode, then checkpoint will be executed when MCONNECTIONP Ool! = NULL indicates the use of the Wal mode, and only if there is a database connection pool in the Wal mode. Execute pragma wal_checkpoint, i.e., synchronize the Wal log to the database file. That is, when we execute endtransaction, we submit checkpoint.

In Android, the default is truncate mode, please see the following source code:

Public static sqlitedatabase opendatabase (String path, cursorfactory factory,  int flags,             Databaseerrorhandler errorhandler)  {        sqlitedatabase  sqlitedatabase = opendatabase (path, factory, flags, errorhandler,                  (short)  0 /*  the main connection handle */);         //  set sqlite pagesize to mblocksize        if   (sblocksize == 0)  {             // TODO:  "/data"  should be a static final string constant  somewhere. it is hardcoded&Nbsp;           // in several places  right now.            sblocksize =  new statfs ("/data"). Getblocksize ();        }         sqlitedatabase.setpagesize (sblocksize);         sqlitedatabase.setjournalmode (path,  "TRUNCATE");         // add this database to the list of databases opened  in this process        synchronized (mActiveDatabases)  {            mactivedatabases.add (new  Weakreference<sqlitedatabase> (sqlitedatabase));        }         return sqlitedatabase;    } 

Set to TRUNCATE mode by Sqlitedatabase.setjournalmode (path, "TRUNCATE").

This article is from the "Itlife" blog, make sure to keep this source http://xwteacher.blog.51cto.com/9653444/1584558

Android using SQLITE3 wal mode