read old version of data and use multiple connections to db

cts tests are in Change-Id: Ifcc89b4ff484c7c810fd2d450ded212a43360dda
dependency on: Change-Id: I938c42afc3fb50f5296d01c55ffcf4a102d8b0cb

1. Use sqlite's work-in-progress writeahead logging feature to read old
     versions of data and thus increase concurrency of readers
     even when there is a writer on the database
2. New API executeQueriesInParallel() sets up a database connecion pool
     automatically created and managed by sqlite java layer
3. To increase reader concurrency, add an option to do BEGIN IMMEDIATE xaction
     instead of BEGIN EXCLUSIVE

Change-Id: I3ce55a8a7cba538f01f731736e7de8ae1e2a8a1f

5 files changed, 947 insertions, 53 deletions

diff --git a/api/current.xml b/api/current.xml
index 0457940..3931e9e 100644
--- a/api/current.xml
+++ b/api/current.xml
@@ -60294,6 +60294,17 @@
  visibility="public"
 >
 </method>
+<method name="beginTransactionNonExclusive"
+ return="void"
+ abstract="false"
+ native="false"
+ synchronized="false"
+ static="false"
+ final="false"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</method>
 <method name="beginTransactionWithListener"
  return="void"
  abstract="false"
@@ -60307,6 +60318,19 @@
 <parameter name="transactionListener" type="android.database.sqlite.SQLiteTransactionListener">
 </parameter>
 </method>
+<method name="beginTransactionWithListenerNonExclusive"
+ return="void"
+ abstract="false"
+ native="false"
+ synchronized="false"
+ static="false"
+ final="false"
+ deprecated="not deprecated"
+ visibility="public"
+>
+<parameter name="transactionListener" type="android.database.sqlite.SQLiteTransactionListener">
+</parameter>
+</method>
 <method name="close"
  return="void"
  abstract="false"
@@ -60363,6 +60387,17 @@
 <parameter name="whereArgs" type="java.lang.String[]">
 </parameter>
 </method>
+<method name="enableWriteAheadLogging"
+ return="void"
+ abstract="false"
+ native="false"
+ synchronized="true"
+ static="false"
+ final="false"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</method>
 <method name="endTransaction"
  return="void"
  abstract="false"
@@ -60938,6 +60973,19 @@
 <exception name="SQLException" type="android.database.SQLException">
 </exception>
 </method>
+<method name="setConnectionPoolSize"
+ return="void"
+ abstract="false"
+ native="false"
+ synchronized="true"
+ static="false"
+ final="false"
+ deprecated="not deprecated"
+ visibility="public"
+>
+<parameter name="size" type="int">
+</parameter>
+</method>
 <method name="setLocale"
  return="void"
  abstract="false"
diff --git a/core/java/android/database/sqlite/DatabaseConnectionPool.java b/core/java/android/database/sqlite/DatabaseConnectionPool.java
new file mode 100644
index 0000000..3f7018f
--- /dev/null
+++ b/core/java/android/database/sqlite/DatabaseConnectionPool.java
@@ -0,0 +1,309 @@
+/*
+ * Copyright (C) 20010 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package android.database.sqlite;
+
+import android.os.SystemClock;
+import android.util.Log;
+
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.Random;
+
+/**
+ * A connection pool to be used by readers.
+ * Note that each connection can be used by only one reader at a time.
+ */
+/* package */ class DatabaseConnectionPool {
+
+    private static final String TAG = "DatabaseConnectionPool";
+
+    /** The default connection pool size. It is set based on the amount of memory the device has.
+     * TODO: set this with 'a system call' which returns the amount of memory the device has
+     */
+    private static final int DEFAULT_CONNECTION_POOL_SIZE = 1;
+
+    /** the pool size set for this {@link SQLiteDatabase} */
+    private volatile int mMaxPoolSize = DEFAULT_CONNECTION_POOL_SIZE;
+
+    /** The connection pool objects are stored in this member.
+     * TODO: revisit this data struct as the number of pooled connections increase beyond
+     * single-digit values.
+     */
+    private final ArrayList<PoolObj> mPool = new ArrayList<PoolObj>(mMaxPoolSize);
+
+    /** the main database connection to which this connection pool is attached */
+    private final SQLiteDatabase mParentDbObj;
+
+    /* package */ DatabaseConnectionPool(SQLiteDatabase db) {
+        this.mParentDbObj = db;
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            Log.d(TAG, "Max Pool Size: " + mMaxPoolSize);
+        }
+    }
+
+    /**
+     * close all database connections in the pool - even if they are in use!
+     */
+    /* package */ void close() {
+        synchronized(mParentDbObj) {
+            for (int i = mPool.size() - 1; i >= 0; i--) {
+                mPool.get(i).mDb.close();
+            }
+            mPool.clear();
+        }
+    }
+
+    /**
+     * get a free connection from the pool
+     *
+     * @param sql if not null, try to find a connection inthe pool which already has cached
+     * the compiled statement for this sql.
+     * @return the Database connection that the caller can use
+     */
+    /* package */ SQLiteDatabase get(String sql) {
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            doAsserts();
+        }
+
+        SQLiteDatabase db = null;
+        PoolObj poolObj = null;
+        synchronized(mParentDbObj) {
+            if (getFreePoolSize() == 0) {
+                if (mMaxPoolSize == mPool.size()) {
+                    // maxed out. can't open any more connections.
+                    // let the caller wait on one of the pooled connections
+                    if (mMaxPoolSize == 1) {
+                        poolObj = mPool.get(0);
+                    } else {
+                        // get a random number between 0 and (mMaxPoolSize-1)
+                        poolObj = mPool.get(
+                                new Random(SystemClock.elapsedRealtime()).nextInt(mMaxPoolSize-1));
+                    }
+                    db = poolObj.mDb;
+                } else {
+                    // create a new connection and add it to the pool, since we haven't reached
+                    // max pool size allowed
+                    int poolSize = getPoolSize();
+                    db = mParentDbObj.createPoolConnection((short)(poolSize + 1));
+                    poolObj = new PoolObj(db);
+                    mPool.add(poolSize, poolObj);
+                }
+            } else {
+                // there are free connections available. pick one
+                for (int i = mPool.size() - 1; i >= 0; i--) {
+                    poolObj = mPool.get(i);
+                    if (!poolObj.isFree()) {
+                        continue;
+                    }
+                    // it is free - but does its database object already have the given sql in its
+                    // statement-cache?
+                    db = poolObj.mDb;
+                    if (sql == null || db.isSqlInStatementCache(sql)) {
+                        // found a free connection we can use
+                        break;
+                    }
+                    // haven't found a database object which has the given sql in its
+                    // statement-cache
+                }
+            }
+
+            assert poolObj != null;
+            assert poolObj.mDb == db;
+
+            poolObj.acquire();
+        }
+
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            Log.d(TAG, "END get-connection: " + toString() + poolObj.toString());
+        }
+        return db;
+        // TODO if a thread acquires a connection and dies without releasing the connection, then
+        // there could be a connection leak.
+    }
+
+    /**
+     * release the given database connection back to the pool.
+     * @param db the connection to be released
+     */
+    /* package */ void release(SQLiteDatabase db) {
+        PoolObj poolObj;
+        synchronized(mParentDbObj) {
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                assert db.mConnectionNum > 0;
+                doAsserts();
+                assert mPool.get(db.mConnectionNum - 1).mDb == db;
+            }
+
+            poolObj = mPool.get(db.mConnectionNum - 1);
+
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                Log.d(TAG, "BEGIN release-conn: " + toString() + poolObj.toString());
+            }
+
+            if (poolObj.isFree()) {
+                throw new IllegalStateException("Releasing object already freed: " +
+                        db.mConnectionNum);
+            }
+
+            poolObj.release();
+        }
+
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            Log.d(TAG, "END release-conn: " + toString() + poolObj.toString());
+        }
+    }
+
+    /**
+     * Returns a list of all database connections in the pool (both free and busy connections).
+     * This method is used when "adb bugreport" is done.
+     */
+    /* package */ ArrayList<SQLiteDatabase> getConnectionList() {
+        ArrayList<SQLiteDatabase> list = new ArrayList<SQLiteDatabase>();
+        synchronized(mParentDbObj) {
+            for (int i = mPool.size() - 1; i >= 0; i--) {
+                list.add(mPool.get(i).mDb);
+            }
+        }
+        return list;
+    }
+
+    /* package */ int getPoolSize() {
+        synchronized(mParentDbObj) {
+            return mPool.size();
+        }
+    }
+
+    private int getFreePoolSize() {
+        int count = 0;
+        for (int i = mPool.size() - 1; i >= 0; i--) {
+            if (mPool.get(i).isFree()) {
+                count++;
+            }
+        }
+        return count++;
+    }
+
+    @Override
+    public String toString() {
+        return "db: " + mParentDbObj.getPath() +
+                ", threadid = " + Thread.currentThread().getId() +
+                ", totalsize = " + mPool.size() + ", #free = " + getFreePoolSize() +
+                ", maxpoolsize = " + mMaxPoolSize;
+    }
+
+    private void doAsserts() {
+        for (int i = 0; i < mPool.size(); i++) {
+            mPool.get(i).verify();
+            assert mPool.get(i).mDb.mConnectionNum == (i + 1);
+        }
+    }
+
+    /* package */ void setMaxPoolSize(int size) {
+        synchronized(mParentDbObj) {
+            mMaxPoolSize = size;
+        }
+    }
+
+    /* package */ int getMaxPoolSize() {
+        synchronized(mParentDbObj) {
+            return mMaxPoolSize;
+        }
+    }
+
+    /**
+     * represents objects in the connection pool.
+     */
+    private static class PoolObj {
+
+        private final SQLiteDatabase mDb;
+        private boolean mFreeBusyFlag = FREE;
+        private static final boolean FREE = true;
+        private static final boolean BUSY = false;
+
+        /** the number of threads holding this connection */
+        // @GuardedBy("this")
+        private int mNumHolders = 0;
+
+        /** contains the threadIds of the threads holding this connection.
+         * used for debugging purposes only.
+         */
+        // @GuardedBy("this")
+        private HashSet<Long> mHolderIds = new HashSet<Long>();
+
+        public PoolObj(SQLiteDatabase db) {
+            mDb = db;
+        }
+
+        private synchronized void acquire() {
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                assert isFree();
+                long id = Thread.currentThread().getId();
+                assert !mHolderIds.contains(id);
+                mHolderIds.add(id);
+            }
+
+            mNumHolders++;
+            mFreeBusyFlag = BUSY;
+        }
+
+        private synchronized void release() {
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                long id = Thread.currentThread().getId();
+                assert mHolderIds.size() == mNumHolders;
+                assert mHolderIds.contains(id);
+                mHolderIds.remove(id);
+            }
+
+            mNumHolders--;
+            if (mNumHolders == 0) {
+                mFreeBusyFlag = FREE;
+            }
+        }
+
+        private synchronized boolean isFree() {
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                verify();
+            }
+            return (mFreeBusyFlag == FREE);
+        }
+
+        private synchronized void verify() {
+            if (mFreeBusyFlag == FREE) {
+                assert mNumHolders == 0;
+            } else {
+                assert mNumHolders > 0;
+            }
+        }
+
+        @Override
+        public String toString() {
+            StringBuilder buff = new StringBuilder();
+            buff.append(", conn # ");
+            buff.append(mDb.mConnectionNum);
+            buff.append(", mCountHolders = ");
+            synchronized(this) {
+                buff.append(mNumHolders);
+                buff.append(", freeBusyFlag = ");
+                buff.append(mFreeBusyFlag);
+                for (Long l : mHolderIds) {
+                    buff.append(", id = " + l);
+                }
+            }
+            return buff.toString();
+        }
+    }
+}
diff --git a/core/java/android/database/sqlite/SQLiteDatabase.java b/core/java/android/database/sqlite/SQLiteDatabase.java
index 4fdc46d..2fa2e99 100644
--- a/core/java/android/database/sqlite/SQLiteDatabase.java
+++ b/core/java/android/database/sqlite/SQLiteDatabase.java
@@ -330,6 +330,17 @@ public class SQLiteDatabase extends SQLiteClosable {
      * */
     private final DatabaseErrorHandler mErrorHandler;
 
+    /** The Database connection pool {@link DatabaseConnectionPool}.
+     * Visibility is package-private for testing purposes. otherwise, private visibility is enough.
+     */
+    /* package */ volatile DatabaseConnectionPool mConnectionPool = null;
+
+    /** Each database connection handle in the pool is assigned a number 1..N, where N is the
+     * size of the connection pool.
+     * The main connection handle to which the pool is attached is assigned a value of 0.
+     */
+    /* package */ final short mConnectionNum;
+
     /**
      * @param closable
      */
@@ -504,7 +515,31 @@ public class SQLiteDatabase extends SQLiteClosable {
      * </pre>
      */
     public void beginTransaction() {
-        beginTransactionWithListener(null /* transactionStatusCallback */);
+        beginTransaction(null /* transactionStatusCallback */, true);
+    }
+
+    /**
+     * Begins a transaction in IMMEDIATE mode. Transactions can be nested. When
+     * the outer transaction is ended all of the work done in that transaction
+     * and all of the nested transactions will be committed or rolled back. The
+     * changes will be rolled back if any transaction is ended without being
+     * marked as clean (by calling setTransactionSuccessful). Otherwise they
+     * will be committed.
+     * <p>
+     * Here is the standard idiom for transactions:
+     *
+     * <pre>
+     *   db.beginTransactionNonExclusive();
+     *   try {
+     *     ...
+     *     db.setTransactionSuccessful();
+     *   } finally {
+     *     db.endTransaction();
+     *   }
+     * </pre>
+     */
+    public void beginTransactionNonExclusive() {
+        beginTransaction(null /* transactionStatusCallback */, false);
     }
 
     /**
@@ -533,6 +568,40 @@ public class SQLiteDatabase extends SQLiteClosable {
      * {@link #yieldIfContendedSafely}.
      */
     public void beginTransactionWithListener(SQLiteTransactionListener transactionListener) {
+        beginTransaction(transactionListener, true);
+    }
+
+    /**
+     * Begins a transaction in IMMEDIATE mode. Transactions can be nested. When
+     * the outer transaction is ended all of the work done in that transaction
+     * and all of the nested transactions will be committed or rolled back. The
+     * changes will be rolled back if any transaction is ended without being
+     * marked as clean (by calling setTransactionSuccessful). Otherwise they
+     * will be committed.
+     * <p>
+     * Here is the standard idiom for transactions:
+     *
+     * <pre>
+     *   db.beginTransactionWithListenerNonExclusive(listener);
+     *   try {
+     *     ...
+     *     db.setTransactionSuccessful();
+     *   } finally {
+     *     db.endTransaction();
+     *   }
+     * </pre>
+     *
+     * @param transactionListener listener that should be notified when the
+     *            transaction begins, commits, or is rolled back, either
+     *            explicitly or by a call to {@link #yieldIfContendedSafely}.
+     */
+    public void beginTransactionWithListenerNonExclusive(
+            SQLiteTransactionListener transactionListener) {
+        beginTransaction(transactionListener, false);
+    }
+
+    private void beginTransaction(SQLiteTransactionListener transactionListener,
+            boolean exclusive) {
         verifyDbIsOpen();
         lockForced();
         boolean ok = false;
@@ -552,7 +621,11 @@ public class SQLiteDatabase extends SQLiteClosable {
 
             // This thread didn't already have the lock, so begin a database
             // transaction now.
-            execSQL("BEGIN EXCLUSIVE;");
+            if (exclusive) {
+                execSQL("BEGIN EXCLUSIVE;");
+            } else {
+                execSQL("BEGIN IMMEDIATE;");
+            }
             mTransactionListener = transactionListener;
             mTransactionIsSuccessful = true;
             mInnerTransactionIsSuccessful = false;
@@ -604,6 +677,18 @@ public class SQLiteDatabase extends SQLiteClosable {
             }
             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");
+                    }
+                }
             } else {
                 try {
                     execSQL("ROLLBACK;");
@@ -859,22 +944,8 @@ public class SQLiteDatabase extends SQLiteClosable {
      */
     public static SQLiteDatabase openDatabase(String path, CursorFactory factory, int flags,
             DatabaseErrorHandler errorHandler) {
-        SQLiteDatabase sqliteDatabase = new SQLiteDatabase(path, factory, flags, errorHandler);
-
-        try {
-            // Open the database.
-            sqliteDatabase.openDatabase(path, flags);
-            if (SQLiteDebug.DEBUG_SQL_STATEMENTS) {
-                sqliteDatabase.enableSqlTracing(path);
-            }
-            if (SQLiteDebug.DEBUG_SQL_TIME) {
-                sqliteDatabase.enableSqlProfiling(path);
-            }
-        } catch (SQLiteDatabaseCorruptException e) {
-            // Database is not even openable.
-            errorHandler.onCorruption(sqliteDatabase);
-            sqliteDatabase = new SQLiteDatabase(path, factory, flags, errorHandler);
-        }
+        SQLiteDatabase sqliteDatabase = openDatabase(path, factory, flags, errorHandler,
+                (short) 0 /* the main connection handle */);
 
         // set sqlite pagesize to mBlockSize
         if (sBlockSize == 0) {
@@ -896,14 +967,26 @@ public class SQLiteDatabase extends SQLiteClosable {
         return sqliteDatabase;
     }
 
-    private void openDatabase(String path, int flags) {
-        // Open the database.
-        dbopen(path, flags);
+    private static SQLiteDatabase openDatabase(String path, CursorFactory factory, int flags,
+            DatabaseErrorHandler errorHandler, short connectionNum) {
+        SQLiteDatabase db = new SQLiteDatabase(path, factory, flags, errorHandler, connectionNum);
         try {
-            setLocale(Locale.getDefault());
-        } catch (RuntimeException e) {
-            Log.e(TAG, "Failed to setLocale(). closing the database", e);
-            dbclose();
+            // Open the database.
+            db.dbopen(path, flags);
+            db.setLocale(Locale.getDefault());
+            if (SQLiteDebug.DEBUG_SQL_STATEMENTS) {
+                db.enableSqlTracing(path, connectionNum);
+            }
+            if (SQLiteDebug.DEBUG_SQL_TIME) {
+                db.enableSqlProfiling(path, connectionNum);
+            }
+            return db;
+        } catch (SQLiteDatabaseCorruptException e) {
+            db.mErrorHandler.onCorruption(db);
+            return SQLiteDatabase.openDatabase(path, factory, flags, errorHandler);
+        } catch (SQLiteException e) {
+            Log.e(TAG, "Failed to open the database. closing it.", e);
+            db.close();
             throw e;
         }
     }
@@ -923,10 +1006,7 @@ public class SQLiteDatabase extends SQLiteClosable {
     }
 
     /**
-     * same as {@link #openOrCreateDatabase(String, CursorFactory)} except for an additional param
-     * errorHandler.
-     * @param errorHandler the {@link DatabaseErrorHandler} obj to be used when database
-     * corruption is detected on the database.
+     * Equivalent to openDatabase(path, factory, CREATE_IF_NECESSARY, errorHandler).
      */
     public static SQLiteDatabase openOrCreateDatabase(String path, CursorFactory factory,
             DatabaseErrorHandler errorHandler) {
@@ -963,6 +1043,9 @@ public class SQLiteDatabase extends SQLiteClosable {
             closePendingStatements();
             // close this database instance - regardless of its reference count value
             onAllReferencesReleased();
+            if (mConnectionPool != null) {
+                mConnectionPool.close();
+            }
         } finally {
             unlock();
         }
@@ -1175,11 +1258,18 @@ public class SQLiteDatabase extends SQLiteClosable {
      */
     public SQLiteStatement compileStatement(String sql) throws SQLException {
         verifyDbIsOpen();
-        lock();
+        String prefixSql = sql.trim().substring(0, 6);
+        SQLiteDatabase db = this;
+        // get a pooled database connection handle to use, if this is a query
+        if (prefixSql.equalsIgnoreCase("SELECT")) {
+            db = getDbConnection(sql);
+        }
+        db.lock();
         try {
-            return new SQLiteStatement(this, sql);
+            return new SQLiteStatement(db, sql);
         } finally {
-            unlock();
+            releaseDbConnection(db);
+            db.unlock();
         }
     }
 
@@ -1376,7 +1466,8 @@ public class SQLiteDatabase extends SQLiteClosable {
             timeStart = System.currentTimeMillis();
         }
 
-        SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(this, sql, editTable);
+        SQLiteDatabase db = getDbConnection(sql);
+        SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(db, sql, editTable);
 
         Cursor cursor = null;
         try {
@@ -1402,6 +1493,7 @@ public class SQLiteDatabase extends SQLiteClosable {
                                   : "<null>")  + ", count is " + count);
                 }
             }
+            releaseDbConnection(db);
         }
         return cursor;
     }
@@ -1872,9 +1964,11 @@ public class SQLiteDatabase extends SQLiteClosable {
      *              exists, mFlags will be updated appropriately.
      * @param errorHandler The {@link DatabaseErrorHandler} to be used when sqlite reports database
      * corruption. may be NULL.
+     * @param connectionNum 0 for main database connection handle. 1..N for pooled database
+     * connection handles.
      */
     private SQLiteDatabase(String path, CursorFactory factory, int flags,
-            DatabaseErrorHandler errorHandler) {
+            DatabaseErrorHandler errorHandler, short connectionNum) {
         if (path == null) {
             throw new IllegalArgumentException("path should not be null");
         }
@@ -1887,6 +1981,7 @@ public class SQLiteDatabase extends SQLiteClosable {
         // Set the DatabaseErrorHandler to be used when SQLite reports corruption.
         // If the caller sets errorHandler = null, then use default errorhandler.
         mErrorHandler = (errorHandler == null) ? new DefaultDatabaseErrorHandler() : errorHandler;
+        mConnectionNum = connectionNum;
     }
 
     /**
@@ -2129,6 +2224,12 @@ public class SQLiteDatabase extends SQLiteClosable {
         mMaxSqlCacheSize = cacheSize;
     }
 
+    /* package */ boolean isSqlInStatementCache(String sql) {
+        synchronized (mCompiledQueries) {
+            return mCompiledQueries.containsKey(sql);
+        }
+    }
+
     /* package */ void finalizeStatementLater(int id) {
         if (!isOpen()) {
             // database already closed. this statement will already have been finalized.
@@ -2175,6 +2276,145 @@ public class SQLiteDatabase extends SQLiteClosable {
         return mClosedStatementIds;
     }
 
+    /**
+     * This method enables parallel execution of queries from multiple threads on the same database.
+     * It does this by opening multiple handles to the database and using a different
+     * database handle for each query.
+     * <p>
+     * If a transaction is in progress on one connection handle and say, a table is updated in the
+     * transaction, then query on the same table on another connection handle will block for the
+     * transaction to complete. But this method enables such queries to execute by having them
+     * return old version of the data from the table. Most often it is the data that existed in the
+     * table prior to the above transaction updates on that table.
+     * <p>
+     * Maximum number of simultaneous handles used to execute queries in parallel is
+     * dependent upon the device memory and possibly other properties.
+     * <p>
+     * After calling this method, execution of queries in parallel is enabled as long as this
+     * database handle is open. To disable execution of queries in parallel, database should
+     * be closed and reopened.
+     * <p>
+     * If a query is part of a transaction, then it is executed on the same database handle the
+     * transaction was begun.
+     *
+     * <p>
+     * If the database has any attached databases, then execution of queries in paralel is NOT
+     * possible. In such cases, {@link IllegalStateException} is thrown.
+     * <p>
+     * A typical way to use this method is the following:
+     * <pre>
+     *     SQLiteDatabase db = SQLiteDatabase.openDatabase("db_filename", cursorFactory,
+     *             CREATE_IF_NECESSARY, myDatabaseErrorHandler);
+     *     db.enableWriteAheadLogging();
+     * </pre>
+     * <p>
+     * Writers should use {@link #beginTransactionNonExclusive()} or
+     * {@link #beginTransactionWithListenerNonExclusive(SQLiteTransactionListener)}
+     * to start a trsnsaction.
+     * Non-exclusive mode allows database file to be in readable by threads executing queries.
+     * </p>
+     *
+     * @throws IllegalStateException thrown if the database has any attached databases.
+     */
+    public synchronized void enableWriteAheadLogging() {
+        if (mConnectionPool != null) {
+            // connection pool already setup.
+            return;
+        }
+
+        // make sure this database has NO attached databases because sqlite's write-ahead-logging
+        // doesn't work for databases with attached databases
+        if (getAttachedDbs().size() > 1) {
+            throw new IllegalStateException("this database: " + mPath +
+                    " has attached databases. can't do execution of of queries in parallel.");
+        }
+        mConnectionPool = new DatabaseConnectionPool(this);
+
+        // set journal_mode to WAL
+        String s = DatabaseUtils.stringForQuery(this, "PRAGMA journal_mode=WAL", null);
+        if (!s.equalsIgnoreCase("WAL")) {
+            Log.e(TAG, "setting journal_mode to WAL failed");
+        }
+    }
+
+    /**
+     * Sets the database connection handle pool size to the given value.
+     * Database connection handle pool is enabled when the app calls
+     * {@link #enableWriteAheadLogging()}.
+     * <p>
+     * The default connection handle pool is set by the system by taking into account various
+     * aspects of the device, such as memory, number of cores etc. It is recommended that
+     * applications use the default pool size set by the system.
+     *
+     * @param size the value the connection handle pool size should be set to.
+     */
+    public synchronized void setConnectionPoolSize(int size) {
+        if (mConnectionPool == null) {
+            throw new IllegalStateException("connection pool not enabled");
+        }
+        int i = mConnectionPool.getMaxPoolSize();
+        if (size < i) {
+            throw new IllegalStateException(
+                    "cannot set max pool size to a value less than the current max value(=" +
+                    i + ")");
+        }
+        mConnectionPool.setMaxPoolSize(size);
+    }
+
+    /* package */ SQLiteDatabase createPoolConnection(short connectionNum) {
+        return openDatabase(mPath, mFactory, mFlags, mErrorHandler, connectionNum);
+    }
+
+    private boolean isPooledConnection() {
+        return this.mConnectionNum > 0;
+    }
+
+    private SQLiteDatabase getDbConnection(String sql) {
+        verifyDbIsOpen();
+
+        // use the current connection handle if
+        // 1. this is a pooled connection handle
+        // 2. OR, if this thread is in a transaction
+        // 3. OR, if there is NO connection handle pool setup
+        SQLiteDatabase db = null;
+        if (isPooledConnection() ||
+                (inTransaction() && mLock.isHeldByCurrentThread()) ||
+                (this.mConnectionPool == null)) {
+            db = this;
+        } else {
+            // get a connection handle from the pool
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                assert mConnectionPool != null;
+            }
+            db = mConnectionPool.get(sql);
+        }
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            Log.d(TAG, "getDbConnection threadid = " + Thread.currentThread().getId() +
+                    ", request on # " + mConnectionNum +
+                    ", assigned # " + db.mConnectionNum + ", " + getPath());
+        }
+        return db;
+    }
+
+    private void releaseDbConnection(SQLiteDatabase db) {
+        // ignore this release call if
+        // 1. the database is closed
+        // 2. OR, if db is NOT a pooled connection handle
+        // 3. OR, if the database being released is same as 'this' (this condition means
+        //     that we should always be releasing a pooled connection handle by calling this method
+        //     from the 'main' connection handle
+        if (!isOpen() || !db.isPooledConnection() || (db == this)) {
+            return;
+        }
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            assert isPooledConnection();
+            assert mConnectionPool != null;
+            Log.d(TAG, "releaseDbConnection threadid = " + Thread.currentThread().getId() +
+                    ", releasing # " + db.mConnectionNum + ", " + getPath());
+        }
+        mConnectionPool.release(db);
+    }
+
     static class ActiveDatabases {
         private static final ActiveDatabases activeDatabases = new ActiveDatabases();
         private HashSet<WeakReference<SQLiteDatabase>> mActiveDatabases =
@@ -2240,6 +2480,14 @@ public class SQLiteDatabase extends SQLiteClosable {
                                 db.mCompiledQueries.size()));
                     }
                 }
+                // if there are pooled connections, return the cache stats for them also.
+                if (db.mConnectionPool != null) {
+                    for (SQLiteDatabase pDb : db.mConnectionPool.getConnectionList()) {
+                        dbStatsList.add(new DbStats("(pooled # " + pDb.mConnectionNum + ") "
+                                + lastnode, 0, 0, 0, pDb.mNumCacheHits, pDb.mNumCacheMisses,
+                                pDb.mCompiledQueries.size()));
+                    }
+                }
             } catch (SQLiteException e) {
                 // ignore. we don't care about exceptions when we are taking adb
                 // bugreport!
@@ -2329,8 +2577,11 @@ public class SQLiteDatabase extends SQLiteClosable {
      * Native call to setup tracing of all SQL statements
      *
      * @param path the full path to the database
+     * @param connectionNum connection number: 0 - N, where the main database
+     *            connection handle is numbered 0 and the connection handles in the connection
+     *            pool are numbered 1..N.
      */
-    private native void enableSqlTracing(String path);
+    private native void enableSqlTracing(String path, short connectionNum);
 
     /**
      * Native call to setup profiling of all SQL statements.
@@ -2339,8 +2590,11 @@ public class SQLiteDatabase extends SQLiteClosable {
      * are executed.
      *
      * @param path the full path to the database
+     * @param connectionNum connection number: 0 - N, where the main database
+     *            connection handle is numbered 0 and the connection handles in the connection
+     *            pool are numbered 1..N.
      */
-    private native void enableSqlProfiling(String path);
+    private native void enableSqlProfiling(String path, short connectionNum);
 
     /**
      * Native call to execute a raw SQL statement. {@link #lock} must be held
diff --git a/core/jni/android_database_SQLiteDatabase.cpp b/core/jni/android_database_SQLiteDatabase.cpp
index e4a050d..5a92193 100644
--- a/core/jni/android_database_SQLiteDatabase.cpp
+++ b/core/jni/android_database_SQLiteDatabase.cpp
@@ -63,8 +63,8 @@ enum {
 
 static jfieldID offset_db_handle;
 
-static char *createStr(const char *path) {
-    int len = strlen(path);
+static char *createStr(const char *path, short extra) {
+    int len = strlen(path) + extra;
     char *str = (char *)malloc(len + 1);
     strncpy(str, path, len);
     str[len] = NULL;
@@ -85,7 +85,7 @@ static void registerLoggingFunc(const char *path) {
     }
 
     LOGV("Registering sqlite logging func \n");
-    int err = sqlite3_config(SQLITE_CONFIG_LOG, &sqlLogger, (void *)createStr(path));
+    int err = sqlite3_config(SQLITE_CONFIG_LOG, &sqlLogger, (void *)createStr(path, 0));
     if (err != SQLITE_OK) {
         LOGE("sqlite_config failed error_code = %d. THIS SHOULD NEVER occur.\n", err);
         return;
@@ -176,13 +176,17 @@ done:
     if (handle != NULL) sqlite3_close(handle);
 }
 
-static char *getDatabaseName(JNIEnv* env, sqlite3 * handle, jstring databaseName) {
+static char *getDatabaseName(JNIEnv* env, sqlite3 * handle, jstring databaseName, short connNum) {
     char const *path = env->GetStringUTFChars(databaseName, NULL);
     if (path == NULL) {
         LOGE("Failure in getDatabaseName(). VM ran out of memory?\n");
         return NULL; // VM would have thrown OutOfMemoryError
     }
-    char *dbNameStr = createStr(path);
+    char *dbNameStr = createStr(path, 4);
+    if (connNum > 999) { // TODO: if number of pooled connections > 999, fix this line.
+      connNum = -1;
+    }
+    sprintf(dbNameStr + strlen(path), "|%03d", connNum);
     env->ReleaseStringUTFChars(databaseName, path);
     return dbNameStr;
 }
@@ -192,10 +196,10 @@ static void sqlTrace(void *databaseName, const char *sql) {
 }
 
 /* public native void enableSqlTracing(); */
-static void enableSqlTracing(JNIEnv* env, jobject object, jstring databaseName)
+static void enableSqlTracing(JNIEnv* env, jobject object, jstring databaseName, jshort connType)
 {
     sqlite3 * handle = (sqlite3 *)env->GetIntField(object, offset_db_handle);
-    sqlite3_trace(handle, &sqlTrace, (void *)getDatabaseName(env, handle, databaseName));
+    sqlite3_trace(handle, &sqlTrace, (void *)getDatabaseName(env, handle, databaseName, connType));
 }
 
 static void sqlProfile(void *databaseName, const char *sql, sqlite3_uint64 tm) {
@@ -204,13 +208,13 @@ static void sqlProfile(void *databaseName, const char *sql, sqlite3_uint64 tm) {
 }
 
 /* public native void enableSqlProfiling(); */
-static void enableSqlProfiling(JNIEnv* env, jobject object, jstring databaseName)
+static void enableSqlProfiling(JNIEnv* env, jobject object, jstring databaseName, jshort connType)
 {
     sqlite3 * handle = (sqlite3 *)env->GetIntField(object, offset_db_handle);
-    sqlite3_profile(handle, &sqlProfile, (void *)getDatabaseName(env, handle, databaseName));
+    sqlite3_profile(handle, &sqlProfile, (void *)getDatabaseName(env, handle, databaseName,
+            connType));
 }
 
-
 /* public native void close(); */
 static void dbclose(JNIEnv* env, jobject object)
 {
@@ -251,7 +255,8 @@ static void native_execSQL(JNIEnv* env, jobject object, jstring sqlString)
     jsize sqlLen = env->GetStringLength(sqlString);
 
     if (sql == NULL || sqlLen == 0) {
-        jniThrowException(env, "java/lang/IllegalArgumentException", "You must supply an SQL string");
+        jniThrowException(env, "java/lang/IllegalArgumentException",
+                "You must supply an SQL string");
         return;
     }
 
@@ -261,7 +266,8 @@ static void native_execSQL(JNIEnv* env, jobject object, jstring sqlString)
 
     if (err != SQLITE_OK) {
         char const * sql8 = env->GetStringUTFChars(sqlString, NULL);
-        LOGE("Failure %d (%s) on %p when preparing '%s'.\n", err, sqlite3_errmsg(handle), handle, sql8);
+        LOGE("Failure %d (%s) on %p when preparing '%s'.\n", err, sqlite3_errmsg(handle),
+                handle, sql8);
         throw_sqlite3_exception(env, handle, sql8);
         env->ReleaseStringUTFChars(sqlString, sql8);
         return;
@@ -272,10 +278,12 @@ static void native_execSQL(JNIEnv* env, jobject object, jstring sqlString)
 
     if (stepErr != SQLITE_DONE) {
         if (stepErr == SQLITE_ROW) {
-            throw_sqlite3_exception(env, "Queries cannot be performed using execSQL(), use query() instead.");
+            throw_sqlite3_exception(env,
+                    "Queries cannot be performed using execSQL(), use query() instead.");
         } else {
             char const * sql8 = env->GetStringUTFChars(sqlString, NULL);
-            LOGE("Failure %d (%s) on %p when executing '%s'\n", err, sqlite3_errmsg(handle), handle, sql8);
+            LOGE("Failure %d (%s) on %p when executing '%s'\n", err, sqlite3_errmsg(handle),
+                    handle, sql8);
             throw_sqlite3_exception(env, handle, sql8);
             env->ReleaseStringUTFChars(sqlString, sql8);
 
@@ -455,8 +463,8 @@ static JNINativeMethod sMethods[] =
     /* name, signature, funcPtr */
     {"dbopen", "(Ljava/lang/String;I)V", (void *)dbopen},
     {"dbclose", "()V", (void *)dbclose},
-    {"enableSqlTracing", "(Ljava/lang/String;)V", (void *)enableSqlTracing},
-    {"enableSqlProfiling", "(Ljava/lang/String;)V", (void *)enableSqlProfiling},
+    {"enableSqlTracing", "(Ljava/lang/String;S)V", (void *)enableSqlTracing},
+    {"enableSqlProfiling", "(Ljava/lang/String;S)V", (void *)enableSqlProfiling},
     {"native_execSQL", "(Ljava/lang/String;)V", (void *)native_execSQL},
     {"lastInsertRow", "()J", (void *)lastInsertRow},
     {"lastChangeCount", "()I", (void *)lastChangeCount},
@@ -482,7 +490,8 @@ int register_android_database_SQLiteDatabase(JNIEnv *env)
         return -1;
     }
 
-    return AndroidRuntime::registerNativeMethods(env, "android/database/sqlite/SQLiteDatabase", sMethods, NELEM(sMethods));
+    return AndroidRuntime::registerNativeMethods(env, "android/database/sqlite/SQLiteDatabase",
+            sMethods, NELEM(sMethods));
 }
 
 /* throw a SQLiteException with a message appropriate for the error in handle */
diff --git a/tests/framework-tests/src/android/database/sqlite/SQLiteDatabaseTest.java b/tests/framework-tests/src/android/database/sqlite/SQLiteDatabaseTest.java
new file mode 100644
index 0000000..4d228c4
--- /dev/null
+++ b/tests/framework-tests/src/android/database/sqlite/SQLiteDatabaseTest.java
@@ -0,0 +1,274 @@
+/*
+ * Copyright (C) 2006 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package android.database.sqlite;
+
+import android.content.Context;
+import android.database.DatabaseUtils;
+import android.test.AndroidTestCase;
+import android.test.suitebuilder.annotation.LargeTest;
+import android.test.suitebuilder.annotation.SmallTest;
+import android.util.Log;
+
+import java.io.File;
+
+public class SQLiteDatabaseTest extends AndroidTestCase {
+    private static final String TAG = "DatabaseGeneralTest";
+
+    private static final int CURRENT_DATABASE_VERSION = 42;
+    private SQLiteDatabase mDatabase;
+    private File mDatabaseFile;
+
+    @Override
+    protected void setUp() throws Exception {
+        super.setUp();
+        dbSetUp();
+    }
+
+    @Override
+    protected void tearDown() throws Exception {
+        dbTeardown();
+        super.tearDown();
+    }
+
+    private void dbTeardown() throws Exception {
+        mDatabase.close();
+        mDatabaseFile.delete();
+    }
+
+    private void dbSetUp() throws Exception {
+        File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE);
+        mDatabaseFile = new File(dbDir, "database_test.db");
+        if (mDatabaseFile.exists()) {
+            mDatabaseFile.delete();
+        }
+        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null);
+        assertNotNull(mDatabase);
+        mDatabase.setVersion(CURRENT_DATABASE_VERSION);
+    }
+
+    @SmallTest
+    public void testEnableWriteAheadLogging() {
+        assertNull(mDatabase.mConnectionPool);
+        mDatabase.enableWriteAheadLogging();
+        DatabaseConnectionPool pool = mDatabase.mConnectionPool;
+        assertNotNull(pool);
+        // make the same call again and make sure the pool already setup is not re-created
+        mDatabase.enableWriteAheadLogging();
+        assertEquals(pool, mDatabase.mConnectionPool);
+    }
+
+    @SmallTest
+    public void testSetConnectionPoolSize() {
+        mDatabase.enableWriteAheadLogging();
+        // can't set pool size to zero
+        try {
+            mDatabase.setConnectionPoolSize(0);
+            fail("IllegalStateException expected");
+        } catch (IllegalStateException e) {
+            assertTrue(e.getMessage().contains("less than the current max value"));
+        }
+        // set pool size to a valid value
+        mDatabase.setConnectionPoolSize(10);
+        assertEquals(10, mDatabase.mConnectionPool.getMaxPoolSize());
+        // can't set pool size to < the value above
+        try {
+            mDatabase.setConnectionPoolSize(1);
+            fail("IllegalStateException expected");
+        } catch (IllegalStateException e) {
+            assertTrue(e.getMessage().contains("less than the current max value"));
+        }
+    }
+
+    /**
+     * Test to ensure that readers are able to read the database data (old versions)
+     * EVEN WHEN the writer is in a transaction on the same database.
+     *<p>
+     * This test starts 1 Writer and 2 Readers and sets up connection pool for readers
+     * by calling the method {@link SQLiteDatabase#enableWriteAheadLogging()}.
+     * <p>
+     * Writer does the following in a tight loop
+     * <pre>
+     *     begin transaction
+     *     insert into table_1
+     *     insert into table_2
+     *     commit
+     * </pre>
+     * <p>
+     * As long a the writer is alive, Readers do the following in a tight loop at the same time
+     * <pre>
+     *     Reader_K does "select count(*) from table_K"  where K = 1 or 2
+     * </pre>
+     * <p>
+     * The test is run for TIME_TO_RUN_WAL_TEST_FOR sec.
+     * <p>
+     * The test is repeated for different connection-pool-sizes (1..3)
+     * <p>
+     * And at the end of of each test, the following statistics are printed
+     * <ul>
+     *    <li>connection-pool-size</li>
+     *    <li>number-of-transactions by writer</li>
+     *    <li>number of reads by reader_K while the writer is IN or NOT-IN xaction</li>
+     * </ul>
+     */
+    @LargeTest
+    public void testConcurrencyEffectsOfConnPool() throws Exception {
+        // run the test with sqlite WAL enable
+        runConnectionPoolTest(true);
+
+        // run the same test WITHOUT sqlite WAL enabled
+        runConnectionPoolTest(false);
+    }
+
+    private void runConnectionPoolTest(boolean useWal) throws Exception {
+        int M = 3;
+        StringBuilder[] buff = new StringBuilder[M];
+        for (int i = 0; i < M; i++) {
+            if (useWal) {
+                // set up connection pool
+                mDatabase.enableWriteAheadLogging();
+                mDatabase.setConnectionPoolSize(i + 1);
+            }
+            mDatabase.execSQL("CREATE TABLE t1 (i int, j int);");
+            mDatabase.execSQL("CREATE TABLE t2 (i int, j int);");
+            mDatabase.beginTransaction();
+            for (int k = 0; k < 5; k++) {
+                mDatabase.execSQL("insert into t1 values(?,?);", new String[] {k+"", k+""});
+                mDatabase.execSQL("insert into t2 values(?,?);", new String[] {k+"", k+""});
+            }
+            mDatabase.setTransactionSuccessful();
+            mDatabase.endTransaction();
+
+            // start a writer
+            Writer w = new Writer(mDatabase);
+
+            // initialize an array of counters to be passed to the readers
+            Reader r1 = new Reader(mDatabase, "t1", w, 0);
+            Reader r2 = new Reader(mDatabase, "t2", w, 1);
+            w.start();
+            r1.start();
+            r2.start();
+
+            // wait for all threads to die
+            w.join();
+            r1.join();
+            r2.join();
+
+            // print the stats
+            int[][] counts = getCounts();
+            buff[i] = new StringBuilder();
+            buff[i].append("connpool-size = ");
+            buff[i].append(i + 1);
+            buff[i].append(", num xacts by writer = ");
+            buff[i].append(getNumXacts());
+            buff[i].append(", num-reads-in-xact/NOT-in-xact by reader1 = ");
+            buff[i].append(counts[0][1] + "/" + counts[0][0]);
+            buff[i].append(", by reader2 = ");
+            buff[i].append(counts[1][1] + "/" + counts[1][0]);
+
+            Log.i(TAG, "done testing for conn-pool-size of " + (i+1));
+
+            dbTeardown();
+            dbSetUp();
+        }
+        Log.i(TAG, "duration of test " + TIME_TO_RUN_WAL_TEST_FOR + " sec");
+        for (int i = 0; i < M; i++) {
+            Log.i(TAG, buff[i].toString());
+        }
+    }
+
+    private boolean inXact = false;
+    private int numXacts;
+    private static final int TIME_TO_RUN_WAL_TEST_FOR = 15; // num sec this test shoudl run
+    private int[][] counts = new int[2][2];
+
+    private synchronized boolean inXact() {
+        return inXact;
+    }
+
+    private synchronized void setInXactFlag(boolean flag) {
+        inXact = flag;
+    }
+
+    private synchronized void setCounts(int readerNum, int[] numReads) {
+        counts[readerNum][0] = numReads[0];
+        counts[readerNum][1] = numReads[1];
+    }
+
+    private synchronized int[][] getCounts() {
+        return counts;
+    }
+
+    private synchronized void setNumXacts(int num) {
+        numXacts = num;
+    }
+
+    private synchronized int getNumXacts() {
+        return numXacts;
+    }
+
+    private class Writer extends Thread {
+        private SQLiteDatabase db = null;
+        public Writer(SQLiteDatabase db) {
+            this.db = db;
+        }
+        @Override public void run() {
+            // in a loop, for N sec, do the following
+            //    BEGIN transaction
+            //    insert into table t1, t2
+            //    Commit
+            long now = System.currentTimeMillis();
+            int k;
+            for (k = 0;(System.currentTimeMillis() - now) / 1000 < TIME_TO_RUN_WAL_TEST_FOR; k++) {
+                db.beginTransactionNonExclusive();
+                setInXactFlag(true);
+                for (int i = 0; i < 10; i++) {
+                    db.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""});
+                    db.execSQL("insert into t2 values(?,?);", new String[] {i+"", i+""});
+                }
+                db.setTransactionSuccessful();
+                setInXactFlag(false);
+                db.endTransaction();
+            }
+            setNumXacts(k);
+        }
+    }
+
+    private class Reader extends Thread {
+        private SQLiteDatabase db = null;
+        private String table = null;
+        private Writer w = null;
+        private int readerNum;
+        private int[] numReads = new int[2];
+        public Reader(SQLiteDatabase db, String table, Writer w, int readerNum) {
+            this.db = db;
+            this.table = table;
+            this.w = w;
+            this.readerNum = readerNum;
+        }
+        @Override public void run() {
+            // while the write is alive, in a loop do the query on a table
+            while (w.isAlive()) {
+                for (int i = 0; i < 10; i++) {
+                    DatabaseUtils.longForQuery(db, "select count(*) from " + this.table, null);
+                    // update count of reads
+                    numReads[inXact() ? 1 : 0] += 1;
+                }
+            }
+            setCounts(readerNum, numReads);
+        }
+    }
+}