do begin-end transaction before standalone insert/update/delete sql

also fix bug# 2871037
Change-Id: I13325f8eabff4f218d3206905010803b61d8e2cd

8 files changed, 667 insertions, 135 deletions

diff --git a/api/current.xml b/api/current.xml
index 942b120..acd1806 100644
--- a/api/current.xml
+++ b/api/current.xml
@@ -61622,6 +61622,19 @@
 <parameter name="name" type="java.lang.String">
 </parameter>
 </method>
+<method name="getSqlStatementType"
+ return="int"
+ abstract="false"
+ native="false"
+ synchronized="false"
+ static="true"
+ final="false"
+ deprecated="not deprecated"
+ visibility="public"
+>
+<parameter name="sql" type="java.lang.String">
+</parameter>
+</method>
 <method name="longForQuery"
  return="long"
  abstract="false"
@@ -61772,6 +61785,83 @@
 <parameter name="e" type="java.lang.Exception">
 </parameter>
 </method>
+<field name="STATEMENT_ABORT"
+ type="int"
+ transient="false"
+ volatile="false"
+ value="6"
+ static="true"
+ final="true"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</field>
+<field name="STATEMENT_ATTACH"
+ type="int"
+ transient="false"
+ volatile="false"
+ value="3"
+ static="true"
+ final="true"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</field>
+<field name="STATEMENT_BEGIN"
+ type="int"
+ transient="false"
+ volatile="false"
+ value="4"
+ static="true"
+ final="true"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</field>
+<field name="STATEMENT_COMMIT"
+ type="int"
+ transient="false"
+ volatile="false"
+ value="5"
+ static="true"
+ final="true"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</field>
+<field name="STATEMENT_OTHER"
+ type="int"
+ transient="false"
+ volatile="false"
+ value="7"
+ static="true"
+ final="true"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</field>
+<field name="STATEMENT_SELECT"
+ type="int"
+ transient="false"
+ volatile="false"
+ value="1"
+ static="true"
+ final="true"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</field>
+<field name="STATEMENT_UPDATE"
+ type="int"
+ transient="false"
+ volatile="false"
+ value="2"
+ static="true"
+ final="true"
+ deprecated="not deprecated"
+ visibility="public"
+>
+</field>
 </class>
 <class name="DatabaseUtils.InsertHelper"
  extends="java.lang.Object"
diff --git a/core/java/android/database/DatabaseUtils.java b/core/java/android/database/DatabaseUtils.java
index 4063534..0687659 100644
--- a/core/java/android/database/DatabaseUtils.java
+++ b/core/java/android/database/DatabaseUtils.java
@@ -52,6 +52,21 @@ public class DatabaseUtils {
 
     private static final String[] countProjection = new String[]{"count(*)"};
 
+    /** One of the values returned by {@link #getSqlStatementType(String)}. */
+    public static final int STATEMENT_SELECT = 1;
+    /** One of the values returned by {@link #getSqlStatementType(String)}. */
+    public static final int STATEMENT_UPDATE = 2;
+    /** One of the values returned by {@link #getSqlStatementType(String)}. */
+    public static final int STATEMENT_ATTACH = 3;
+    /** One of the values returned by {@link #getSqlStatementType(String)}. */
+    public static final int STATEMENT_BEGIN = 4;
+    /** One of the values returned by {@link #getSqlStatementType(String)}. */
+    public static final int STATEMENT_COMMIT = 5;
+    /** One of the values returned by {@link #getSqlStatementType(String)}. */
+    public static final int STATEMENT_ABORT = 6;
+    /** One of the values returned by {@link #getSqlStatementType(String)}. */
+    public static final int STATEMENT_OTHER = 7;
+
     /**
      * Special function for writing an exception result at the header of
      * a parcel, to be used when returning an exception from a transaction.
@@ -1159,4 +1174,45 @@ public class DatabaseUtils {
         db.setVersion(dbVersion);
         db.close();
     }
+
+    /**
+     * Returns one of the following which represent the type of the given SQL statement.
+     * <ol>
+     *   <li>{@link #STATEMENT_SELECT}</li>
+     *   <li>{@link #STATEMENT_UPDATE}</li>
+     *   <li>{@link #STATEMENT_ATTACH}</li>
+     *   <li>{@link #STATEMENT_BEGIN}</li>
+     *   <li>{@link #STATEMENT_COMMIT}</li>
+     *   <li>{@link #STATEMENT_ABORT}</li>
+     *   <li>{@link #STATEMENT_OTHER}</li>
+     * </ol>
+     * @param sql the SQL statement whose type is returned by this method
+     * @return one of the values listed above
+     */
+    public static int getSqlStatementType(String sql) {
+        sql = sql.trim();
+        if (sql.length() < 3) {
+            return STATEMENT_OTHER;
+        }
+        String prefixSql = sql.substring(0, 3).toUpperCase();
+        if (prefixSql.equals("SEL")) {
+            return STATEMENT_SELECT;
+        } else if (prefixSql.equals("INS") ||
+                prefixSql.equals("UPD") ||
+                prefixSql.equals("REP") ||
+                prefixSql.equals("DEL")) {
+            return STATEMENT_UPDATE;
+        } else if (prefixSql.equals("ATT")) {
+            return STATEMENT_ATTACH;
+        } else if (prefixSql.equals("COM")) {
+            return STATEMENT_COMMIT;
+        } else if (prefixSql.equals("END")) {
+            return STATEMENT_COMMIT;
+        } else if (prefixSql.equals("ROL")) {
+            return STATEMENT_ABORT;
+        } else if (prefixSql.equals("BEG")) {
+            return STATEMENT_BEGIN;
+        }
+        return STATEMENT_OTHER;
+    }
 }
diff --git a/core/java/android/database/sqlite/DatabaseConnectionPool.java b/core/java/android/database/sqlite/DatabaseConnectionPool.java
index 50b2919..54b0605 100644
--- a/core/java/android/database/sqlite/DatabaseConnectionPool.java
+++ b/core/java/android/database/sqlite/DatabaseConnectionPool.java
@@ -63,6 +63,9 @@ import java.util.Random;
      */
     /* package */ void close() {
         synchronized(mParentDbObj) {
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                Log.d(TAG, "Closing the connection pool on " + mParentDbObj.getPath() + toString());
+            }
             for (int i = mPool.size() - 1; i >= 0; i--) {
                 mPool.get(i).mDb.close();
             }
diff --git a/core/java/android/database/sqlite/SQLiteDatabase.java b/core/java/android/database/sqlite/SQLiteDatabase.java
index 623821b..a2fff73 100644
--- a/core/java/android/database/sqlite/SQLiteDatabase.java
+++ b/core/java/android/database/sqlite/SQLiteDatabase.java
@@ -192,6 +192,11 @@ public class SQLiteDatabase extends SQLiteClosable {
      */
     private SQLiteTransactionListener mTransactionListener;
 
+    /**
+     * this member is set if {@link #execSQL(String)} is used to begin and end transactions.
+     */
+    private boolean mTransactionUsingExecSql;
+
     /** Synchronize on this when accessing the database */
     private final ReentrantLock mLock = new ReentrantLock(true);
 
@@ -236,9 +241,6 @@ public class SQLiteDatabase extends SQLiteClosable {
     /** Used by native code, do not rename. make it volatile, so it is thread-safe. */
     /* package */ volatile int mNativeHandle = 0;
 
-    /** Used to make temp table names unique */
-    /* package */ int mTempTableSequence = 0;
-
     /**
      * The size, in bytes, of a block on "/data". This corresponds to the Unix
      * statfs.f_bsize field. note that this field is lazily initialized.
@@ -621,9 +623,6 @@ public class SQLiteDatabase extends SQLiteClosable {
 
             // This thread didn't already have the lock, so begin a database
             // transaction now.
-            // STOPSHIP - uncomment the following 1 line
-            // if (exclusive) {
-            // STOPSHIP - remove the following 1 line
             if (exclusive && mConnectionPool == null) {
                 execSQL("BEGIN EXCLUSIVE;");
             } else {
@@ -740,7 +739,50 @@ public class SQLiteDatabase extends SQLiteClosable {
      * return true if there is a transaction pending
      */
     public boolean inTransaction() {
-        return mLock.getHoldCount() > 0;
+        return mLock.getHoldCount() > 0 || mTransactionUsingExecSql;
+    }
+
+    /* package */ synchronized void setTransactionUsingExecSqlFlag() {
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            Log.i(TAG, "found execSQL('begin transaction')");
+        }
+        mTransactionUsingExecSql = true;
+    }
+
+    /* package */ synchronized void resetTransactionUsingExecSqlFlag() {
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            if (mTransactionUsingExecSql) {
+                Log.i(TAG, "found execSQL('commit or end or rollback')");
+            }
+        }
+        mTransactionUsingExecSql = false;
+    }
+
+    /**
+     * Returns true if the caller is considered part of the current transaction, if any.
+     * <p>
+     * Caller is part of the current transaction if either of the following is true
+     * <ol>
+     *   <li>If transaction is started by calling beginTransaction() methods AND if the caller is
+     *   in the same thread as the thread that started the transaction.
+     *   </li>
+     *   <li>If the transaction is started by calling {@link #execSQL(String)} like this:
+     *   execSQL("BEGIN transaction"). In this case, every thread in the process is considered
+     *   part of the current transaction.</li>
+     * </ol>
+     *
+     * @return true if the caller is considered part of the current transaction, if any.
+     */
+    /* package */ synchronized boolean amIInTransaction() {
+        // always do this test on the main database connection - NOT on pooled database connection
+        // since transactions always occur on the main database connections only.
+        SQLiteDatabase db = (isPooledConnection()) ? mParentConnObj : this;
+        boolean b = (!db.inTransaction()) ? false :
+                db.mTransactionUsingExecSql || db.mLock.isHeldByCurrentThread();
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
+            Log.i(TAG, "amIinTransaction: " + b);
+        }
+        return b;
     }
 
     /**
@@ -932,6 +974,9 @@ public class SQLiteDatabase extends SQLiteClosable {
             DatabaseErrorHandler errorHandler, short connectionNum) {
         SQLiteDatabase db = new SQLiteDatabase(path, factory, flags, errorHandler, connectionNum);
         try {
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                Log.i(TAG, "opening the db : " + path);
+            }
             // Open the database.
             db.dbopen(path, flags);
             db.setLocale(Locale.getDefault());
@@ -1008,7 +1053,7 @@ public class SQLiteDatabase extends SQLiteClosable {
         if (!isOpen()) {
             return; // already closed
         }
-        if (SQLiteDebug.DEBUG_SQL_STATEMENTS) {
+        if (Log.isLoggable(TAG, Log.DEBUG)) {
             Log.i(TAG, "closing db: " + mPath + " (connection # " + mConnectionNum);
         }
         lock();
@@ -1020,6 +1065,10 @@ public class SQLiteDatabase extends SQLiteClosable {
             // close this database instance - regardless of its reference count value
             dbclose();
             if (mConnectionPool != null) {
+                if (Log.isLoggable(TAG, Log.DEBUG)) {
+                    assert mConnectionPool != null;
+                    Log.i(TAG, mConnectionPool.toString());
+                }
                 mConnectionPool.close();
             }
         } finally {
@@ -1586,7 +1635,6 @@ public class SQLiteDatabase extends SQLiteClosable {
     public long insertWithOnConflict(String table, String nullColumnHack,
             ContentValues initialValues, int conflictAlgorithm) {
         verifyDbIsOpen();
-        BlockGuard.getThreadPolicy().onWriteToDisk();
 
         // Measurements show most sql lengths <= 152
         StringBuilder sql = new StringBuilder(152);
@@ -1625,7 +1673,6 @@ public class SQLiteDatabase extends SQLiteClosable {
         sql.append(values);
         sql.append(");");
 
-        lock();
         SQLiteStatement statement = null;
         try {
             statement = compileStatement(sql.toString());
@@ -1649,7 +1696,6 @@ public class SQLiteDatabase extends SQLiteClosable {
             if (statement != null) {
                 statement.close();
             }
-            unlock();
         }
     }
 
@@ -1665,8 +1711,6 @@ public class SQLiteDatabase extends SQLiteClosable {
      */
     public int delete(String table, String whereClause, String[] whereArgs) {
         verifyDbIsOpen();
-        BlockGuard.getThreadPolicy().onWriteToDisk();
-        lock();
         SQLiteStatement statement = null;
         try {
             statement = compileStatement("DELETE FROM " + table
@@ -1686,7 +1730,6 @@ public class SQLiteDatabase extends SQLiteClosable {
             if (statement != null) {
                 statement.close();
             }
-            unlock();
         }
     }
 
@@ -1717,7 +1760,6 @@ public class SQLiteDatabase extends SQLiteClosable {
      */
     public int updateWithOnConflict(String table, ContentValues values,
             String whereClause, String[] whereArgs, int conflictAlgorithm) {
-        BlockGuard.getThreadPolicy().onWriteToDisk();
         if (values == null || values.size() == 0) {
             throw new IllegalArgumentException("Empty values");
         }
@@ -1746,7 +1788,6 @@ public class SQLiteDatabase extends SQLiteClosable {
         }
 
         verifyDbIsOpen();
-        lock();
         SQLiteStatement statement = null;
         try {
             statement = compileStatement(sql.toString());
@@ -1781,7 +1822,6 @@ public class SQLiteDatabase extends SQLiteClosable {
             if (statement != null) {
                 statement.close();
             }
-            unlock();
         }
     }
 
@@ -1789,9 +1829,7 @@ public class SQLiteDatabase extends SQLiteClosable {
      * Execute a single SQL statement that is NOT a SELECT
      * or any other SQL statement that returns data.
      * <p>
-     * Use of this method is discouraged as it doesn't perform well when issuing the same SQL
-     * statement repeatedly (see {@link #compileStatement(String)} to prepare statements for
-     * repeated use), and it has no means to return any data (such as the number of affected rows).
+     * It has no means to return any data (such as the number of affected rows).
      * Instead, you're encouraged to use {@link #insert(String, String, ContentValues)},
      * {@link #update(String, ContentValues, String, String[])}, et al, when possible.
      * </p>
@@ -1807,35 +1845,17 @@ public class SQLiteDatabase extends SQLiteClosable {
      * @throws SQLException If the SQL string is invalid for some reason
      */
     public void execSQL(String sql) throws SQLException {
-        sql = sql.trim();
-        String prefix = sql.substring(0, 6);
-        if (prefix.equalsIgnoreCase("ATTACH")) {
+        int stmtType = DatabaseUtils.getSqlStatementType(sql);
+        if (stmtType == DatabaseUtils.STATEMENT_ATTACH) {
             disableWriteAheadLogging();
         }
-        verifyDbIsOpen();
-        BlockGuard.getThreadPolicy().onWriteToDisk();
         long timeStart = SystemClock.uptimeMillis();
-        lock();
         logTimeStat(mLastSqlStatement, timeStart, GET_LOCK_LOG_PREFIX);
-        SQLiteStatement stmt = null;
-        try {
-            closePendingStatements();
-            stmt = compileStatement(sql);
-            stmt.execute();
-        } catch (SQLiteDatabaseCorruptException e) {
-            onCorruption();
-            throw e;
-        } finally {
-            if (stmt != null) {
-                stmt.close();
-            }
-            unlock();
-        }
+        executeSql(sql, null);
 
         // Log commit statements along with the most recently executed
-        // SQL statement for disambiguation.  Note that instance
-        // equality to COMMIT_SQL is safe here.
-        if (sql == COMMIT_SQL) {
+        // SQL statement for disambiguation.
+        if (stmtType == DatabaseUtils.STATEMENT_COMMIT) {
             logTimeStat(mLastSqlStatement, timeStart, COMMIT_SQL);
         } else {
             logTimeStat(sql, timeStart, null);
@@ -1886,13 +1906,15 @@ public class SQLiteDatabase extends SQLiteClosable {
      * @throws SQLException If the SQL string is invalid for some reason
      */
     public void execSQL(String sql, Object[] bindArgs) throws SQLException {
-        BlockGuard.getThreadPolicy().onWriteToDisk();
         if (bindArgs == null) {
             throw new IllegalArgumentException("Empty bindArgs");
         }
+        executeSql(sql, bindArgs);
+    }
+
+    private void executeSql(String sql, Object[] bindArgs) throws SQLException {
         verifyDbIsOpen();
         long timeStart = SystemClock.uptimeMillis();
-        lock();
         SQLiteStatement statement = null;
         try {
             statement = compileStatement(sql);
@@ -1902,7 +1924,7 @@ public class SQLiteDatabase extends SQLiteClosable {
                     DatabaseUtils.bindObjectToProgram(statement, i + 1, bindArgs[i]);
                 }
             }
-            statement.executeUpdateDelete();
+            statement.execute();
         } catch (SQLiteDatabaseCorruptException e) {
             onCorruption();
             throw e;
@@ -1910,7 +1932,6 @@ public class SQLiteDatabase extends SQLiteClosable {
             if (statement != null) {
                 statement.close();
             }
-            unlock();
         }
         logTimeStat(sql, timeStart);
     }
@@ -2142,7 +2163,8 @@ public class SQLiteDatabase extends SQLiteClosable {
         }
     }
 
-    private void deallocCachedSqlStatements() {
+    /** package-level access for testing purposes */
+    /* package */ void deallocCachedSqlStatements() {
         synchronized (mCompiledQueries) {
             for (SQLiteCompiledSql compiledSql : mCompiledQueries.values()) {
                 compiledSql.releaseSqlStatement();
@@ -2220,11 +2242,7 @@ public class SQLiteDatabase extends SQLiteClosable {
         }
     }
 
-    /**
-     * public visibility only for testing. otherwise, package visibility is sufficient
-     * @hide
-     */
-    public void closePendingStatements() {
+    /* package */ void closePendingStatements() {
         if (!isOpen()) {
             // since this database is already closed, no need to finalize anything.
             mClosedStatementIds.clear();
@@ -2246,9 +2264,8 @@ public class SQLiteDatabase extends SQLiteClosable {
 
     /**
      * for testing only
-     * @hide
      */
-    public ArrayList<Integer> getQueuedUpStmtList() {
+    /* package */ ArrayList<Integer> getQueuedUpStmtList() {
         return mClosedStatementIds;
     }
 
@@ -2303,7 +2320,10 @@ public class SQLiteDatabase extends SQLiteClosable {
         // 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) {
-            Log.i(TAG, "this database: " + mPath + " has attached databases. can't  enable WAL.");
+            if (Log.isLoggable(TAG, Log.DEBUG)) {
+                Log.d(TAG,
+                        "this database: " + mPath + " has attached databases. can't  enable WAL.");
+            }
             return false;
         }
         if (mConnectionPool == null) {
@@ -2331,7 +2351,8 @@ public class SQLiteDatabase extends SQLiteClosable {
     /* package */ SQLiteDatabase getDatabaseHandle(String sql) {
         if (isPooledConnection()) {
             // this is a pooled database connection
-            if (isOpen()) {
+            // use it if it is open AND if I am not currently part of a transaction
+            if (isOpen() && !amIInTransaction()) {
                 // TODO: use another connection from the pool
                 // if this connection is currently in use by some other thread
                 // AND if there are free connections in the pool
@@ -2394,22 +2415,17 @@ public class SQLiteDatabase extends SQLiteClosable {
         if (isPooledConnection()) {
             throw new IllegalStateException("incorrect database connection handle");
         }
-        if (Log.isLoggable(TAG, Log.DEBUG)) {
-            // this method shoudl never be called with anything other than SELECT
-            if (sql.substring(0, 6).equalsIgnoreCase("SELECT")) {
-                throw new IllegalStateException("unexpected SQL statement: " + sql);
-            }
-        }
 
         // use the current connection handle if
-        // 1. if this thread is in a transaction
+        // 1. if the caller is part of the ongoing transaction, if any
         // 2. OR, if there is NO connection handle pool setup
-        if ((inTransaction() && mLock.isHeldByCurrentThread()) || mConnectionPool == null) {
+        if (amIInTransaction() || mConnectionPool == null) {
             return this;
         } else {
             // get a connection handle from the pool
             if (Log.isLoggable(TAG, Log.DEBUG)) {
                 assert mConnectionPool != null;
+                Log.i(TAG, mConnectionPool.toString());
             }
             return mConnectionPool.get(sql);
         }
diff --git a/core/java/android/database/sqlite/SQLiteProgram.java b/core/java/android/database/sqlite/SQLiteProgram.java
index 017b65f..a4ebe5a 100644
--- a/core/java/android/database/sqlite/SQLiteProgram.java
+++ b/core/java/android/database/sqlite/SQLiteProgram.java
@@ -16,6 +16,7 @@
 
 package android.database.sqlite;
 
+import android.database.DatabaseUtils;
 import android.util.Log;
 import android.util.Pair;
 
@@ -31,11 +32,6 @@ public abstract class SQLiteProgram extends SQLiteClosable {
 
     private static final String TAG = "SQLiteProgram";
 
-    /** the type of sql statement being processed by this object */
-    /* package */ static final int SELECT_STMT = 1;
-    private static final int UPDATE_STMT = 2;
-    private static final int OTHER_STMT = 3;
-
     /** The database this program is compiled against.
      * @deprecated do not use this
      */
@@ -88,7 +84,9 @@ public abstract class SQLiteProgram extends SQLiteClosable {
      * <p>
      * It is protected (in multi-threaded environment) by {@link SQLiteProgram}.this
      */
-    private ArrayList<Pair<Integer, Object>> bindArgs = null;
+    private ArrayList<Pair<Integer, Object>> mBindArgs = null;
+
+    /* package */ final int mStatementType;
 
     /* package */ SQLiteProgram(SQLiteDatabase db, String sql) {
         this(db, sql, true);
@@ -96,6 +94,7 @@ public abstract class SQLiteProgram extends SQLiteClosable {
 
     /* package */ SQLiteProgram(SQLiteDatabase db, String sql, boolean compileFlag) {
         mSql = sql.trim();
+        mStatementType = DatabaseUtils.getSqlStatementType(mSql);
         db.acquireReference();
         db.addSQLiteClosable(this);
         mDatabase = db;
@@ -107,7 +106,8 @@ public abstract class SQLiteProgram extends SQLiteClosable {
 
     private void compileSql() {
         // only cache CRUD statements
-        if (getSqlStatementType(mSql) == OTHER_STMT) {
+        if (mStatementType != DatabaseUtils.STATEMENT_SELECT &&
+                mStatementType != DatabaseUtils.STATEMENT_UPDATE) {
             mCompiledSql = new SQLiteCompiledSql(mDatabase, mSql);
             nStatement = mCompiledSql.nStatement;
             // since it is not in the cache, no need to acquire() it.
@@ -150,22 +150,6 @@ public abstract class SQLiteProgram extends SQLiteClosable {
         nStatement = mCompiledSql.nStatement;
     }
 
-    /* package */ int getSqlStatementType(String sql) {
-        if (mSql.length() < 6) {
-            return OTHER_STMT;
-        }
-        String prefixSql = mSql.substring(0, 6);
-        if (prefixSql.equalsIgnoreCase("SELECT")) {
-            return SELECT_STMT;
-        } else if (prefixSql.equalsIgnoreCase("INSERT") ||
-                prefixSql.equalsIgnoreCase("UPDATE") ||
-                prefixSql.equalsIgnoreCase("REPLAC") ||
-                prefixSql.equalsIgnoreCase("DELETE")) {
-            return UPDATE_STMT;
-        }
-        return OTHER_STMT;
-    }
-
     @Override
     protected void onAllReferencesReleased() {
         releaseCompiledSqlIfNotInCache();
@@ -361,7 +345,7 @@ public abstract class SQLiteProgram extends SQLiteClosable {
      */
     public void clearBindings() {
         synchronized (this) {
-            bindArgs = null;
+            mBindArgs = null;
             if (this.nStatement == 0) {
                 return;
             }
@@ -380,7 +364,7 @@ public abstract class SQLiteProgram extends SQLiteClosable {
      */
     public void close() {
         synchronized (this) {
-            bindArgs = null;
+            mBindArgs = null;
             if (nHandle == 0 || !mDatabase.isOpen()) {
                 return;
             }
@@ -389,19 +373,19 @@ public abstract class SQLiteProgram extends SQLiteClosable {
     }
 
     private synchronized void addToBindArgs(int index, Object value) {
-        if (bindArgs == null) {
-            bindArgs = new ArrayList<Pair<Integer, Object>>();
+        if (mBindArgs == null) {
+            mBindArgs = new ArrayList<Pair<Integer, Object>>();
         }
-        bindArgs.add(new Pair<Integer, Object>(index, value));
+        mBindArgs.add(new Pair<Integer, Object>(index, value));
     }
 
     /* package */ synchronized void compileAndbindAllArgs() {
         assert nStatement == 0;
         compileSql();
-        if (bindArgs == null) {
+        if (mBindArgs == null) {
             return;
         }
-        for (Pair<Integer, Object> p : bindArgs) {
+        for (Pair<Integer, Object> p : mBindArgs) {
             if (p.second == null) {
                 native_bind_null(p.first);
             } else if (p.second instanceof Long) {
diff --git a/core/java/android/database/sqlite/SQLiteStatement.java b/core/java/android/database/sqlite/SQLiteStatement.java
index 7a683e4..619764a 100644
--- a/core/java/android/database/sqlite/SQLiteStatement.java
+++ b/core/java/android/database/sqlite/SQLiteStatement.java
@@ -16,6 +16,7 @@
 
 package android.database.sqlite;
 
+import android.database.DatabaseUtils;
 import android.os.SystemClock;
 
 import dalvik.system.BlockGuard;
@@ -36,6 +37,11 @@ public class SQLiteStatement extends SQLiteProgram
     private static final boolean WRITE = false;
 
     private SQLiteDatabase mOrigDb;
+    private int state;
+    /** possible value for {@link #state}. indicates that a transaction is started.} */
+    private static final int TRANS_STARTED = 1;
+    /** possible value for {@link #state}. indicates that a lock is acquired.} */
+    private static final int LOCK_ACQUIRED = 2;
 
     /**
      * Don't use SQLiteStatement constructor directly, please use
@@ -150,18 +156,20 @@ public class SQLiteStatement extends SQLiteProgram
      *   <li>make sure the database is open</li>
      *   <li>get a database connection from the connection pool,if possible</li>
      *   <li>notifies {@link BlockGuard} of read/write</li>
-     *   <li>get lock on the database</li>
+     *   <li>if the SQL statement is an update, start transaction if not already in one.
+     *   otherwise, get lock on the database</li>
      *   <li>acquire reference on this object</li>
      *   <li>and then return the current time _before_ the database lock was acquired</li>
      * </ul>
      * <p>
-     * This method removes the duplcate code from the other public
+     * This method removes the duplicate code from the other public
      * methods in this class.
      */
     private long acquireAndLock(boolean rwFlag) {
+        state = 0;
         // use pooled database connection handles for SELECT SQL statements
         mDatabase.verifyDbIsOpen();
-        SQLiteDatabase db = (getSqlStatementType(mSql) != SELECT_STMT) ? mDatabase
+        SQLiteDatabase db = (mStatementType != DatabaseUtils.STATEMENT_SELECT) ? mDatabase
                 : mDatabase.getDbConnection(mSql);
         // use the database connection obtained above
         mOrigDb = mDatabase;
@@ -172,20 +180,57 @@ public class SQLiteStatement extends SQLiteProgram
         } else {
             BlockGuard.getThreadPolicy().onReadFromDisk();
         }
-        long startTime = SystemClock.uptimeMillis();
-        mDatabase.lock();
+
+        /*
+         * Special case handling of SQLiteDatabase.execSQL("BEGIN transaction").
+         * we know it is execSQL("BEGIN transaction") from the caller IF there is no lock held.
+         * beginTransaction() methods in SQLiteDatabase call lockForced() before
+         * calling execSQL("BEGIN transaction").
+         */
+        if (mStatementType == DatabaseUtils.STATEMENT_BEGIN) {
+            if (!mDatabase.isDbLockedByCurrentThread()) {
+                // transaction is  NOT started by calling beginTransaction() methods in
+                // SQLiteDatabase
+                mDatabase.setTransactionUsingExecSqlFlag();
+            }
+        } else if (mStatementType == DatabaseUtils.STATEMENT_UPDATE) {
+            // got update SQL statement. if there is NO pending transaction, start one
+            if (!mDatabase.inTransaction()) {
+                mDatabase.beginTransactionNonExclusive();
+                state = TRANS_STARTED;
+            }
+        }
+        // do I have database lock? if not, grab it.
+        if (!mDatabase.isDbLockedByCurrentThread()) {
+            mDatabase.lock();
+            state = LOCK_ACQUIRED;
+        }
+
         acquireReference();
+        long startTime = SystemClock.uptimeMillis();
         mDatabase.closePendingStatements();
         compileAndbindAllArgs();
         return startTime;
     }
 
     /**
-     * this method releases locks and references acquired in {@link #acquireAndLock(boolean)}.
+     * this method releases locks and references acquired in {@link #acquireAndLock(boolean)}
      */
     private void releaseAndUnlock() {
         releaseReference();
-        mDatabase.unlock();
+        if (state == TRANS_STARTED) {
+            try {
+                mDatabase.setTransactionSuccessful();
+            } finally {
+                mDatabase.endTransaction();
+            }
+        } else if (state == LOCK_ACQUIRED) {
+            mDatabase.unlock();
+        }
+        if (mStatementType == DatabaseUtils.STATEMENT_COMMIT ||
+                mStatementType == DatabaseUtils.STATEMENT_ABORT) {
+            mDatabase.resetTransactionUsingExecSqlFlag();
+        }
         clearBindings();
         // release the compiled sql statement so that the caller's SQLiteStatement no longer
         // has a hard reference to a database object that may get deallocated at any point.
diff --git a/core/tests/coretests/src/android/database/DatabaseCursorTest.java b/core/tests/coretests/src/android/database/DatabaseCursorTest.java
index 0265c87..0733229 100644
--- a/core/tests/coretests/src/android/database/DatabaseCursorTest.java
+++ b/core/tests/coretests/src/android/database/DatabaseCursorTest.java
@@ -33,7 +33,6 @@ import android.test.AndroidTestCase;
 import android.test.PerformanceTestCase;
 import android.test.suitebuilder.annotation.LargeTest;
 import android.test.suitebuilder.annotation.MediumTest;
-import android.test.suitebuilder.annotation.SmallTest;
 import android.test.suitebuilder.annotation.Suppress;
 import android.util.Log;
 
@@ -41,8 +40,6 @@ import java.io.File;
 import java.util.Arrays;
 import java.util.Random;
 
-import junit.framework.TestCase;
-
 public class DatabaseCursorTest extends AndroidTestCase implements PerformanceTestCase {
 
     private static final String sString1 = "this is a test";
diff --git a/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java b/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java
index 662ba97..dc5613e 100644
--- a/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java
+++ b/core/tests/coretests/src/android/database/sqlite/SQLiteDatabaseTest.java
@@ -16,6 +16,7 @@
 
 package android.database.sqlite;
 
+import android.content.ContentValues;
 import android.content.Context;
 import android.database.Cursor;
 import android.database.DatabaseUtils;
@@ -33,10 +34,13 @@ import java.util.ArrayList;
 
 public class SQLiteDatabaseTest extends AndroidTestCase {
     private static final String TAG = "DatabaseGeneralTest";
-
+    private static final String TEST_TABLE = "test";
     private static final int CURRENT_DATABASE_VERSION = 42;
     private SQLiteDatabase mDatabase;
     private File mDatabaseFile;
+    private static final int INSERT = 1;
+    private static final int UPDATE = 2;
+    private static final int DELETE = 3;
 
     @Override
     protected void setUp() throws Exception {
@@ -150,6 +154,100 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
     }
 
     /**
+     * a transaction should be started before a standalone-update/insert/delete statement
+     */
+    @SmallTest
+    public void testStartXactBeforeUpdateSql() throws InterruptedException {
+        runTestForStartXactBeforeUpdateSql(INSERT);
+        runTestForStartXactBeforeUpdateSql(UPDATE);
+        runTestForStartXactBeforeUpdateSql(DELETE);
+    }
+    private void runTestForStartXactBeforeUpdateSql(int stmtType) throws InterruptedException {
+        createTableAndClearCache();
+
+        ContentValues values = new ContentValues();
+        // make some changes to data in TEST_TABLE
+        for (int i = 0; i < 5; i++) {
+            values.put("i", i);
+            values.put("j", "i" + System.currentTimeMillis());
+            mDatabase.insert(TEST_TABLE, null, values);
+            switch (stmtType) {
+                case UPDATE:
+                    values.put("j", "u" + System.currentTimeMillis());
+                    mDatabase.update(TEST_TABLE, values, "i = " + i, null);
+                    break;
+                case DELETE:
+                    mDatabase.delete(TEST_TABLE, "i = 1", null);
+                    break;
+            }
+        }
+        // do a query. even though query uses a different database connection,
+        // it should still see the above changes to data because the above standalone
+        // insert/update/deletes are done in transactions automatically.
+        String sql = "select count(*) from " + TEST_TABLE;
+        SQLiteStatement stmt = mDatabase.compileStatement(sql);
+        final int expectedValue = (stmtType == DELETE) ? 4 : 5;
+        assertEquals(expectedValue, stmt.simpleQueryForLong());
+        stmt.close();
+        Cursor c = mDatabase.rawQuery(sql, null);
+        assertEquals(1, c.getCount());
+        c.moveToFirst();
+        assertEquals(expectedValue, c.getLong(0));
+        c.close();
+
+        // do 5 more changes in a transaction but do a query before and after the commit
+        mDatabase.beginTransaction();
+        for (int i = 10; i < 15; i++) {
+            values.put("i", i);
+            values.put("j", "i" + System.currentTimeMillis());
+            mDatabase.insert(TEST_TABLE, null, values);
+            switch (stmtType) {
+                case UPDATE:
+                    values.put("j", "u" + System.currentTimeMillis());
+                    mDatabase.update(TEST_TABLE, values, "i = " + i, null);
+                    break;
+                case DELETE:
+                    mDatabase.delete(TEST_TABLE, "i = 1", null);
+                    break;
+            }
+        }
+        mDatabase.setTransactionSuccessful();
+        // do a query before commit - should still have 5 rows
+        // this query should run in a different thread to force it to use a different database
+        // connection
+        Thread t = new Thread() {
+            @Override public void run() {
+                String sql = "select count(*) from " + TEST_TABLE;
+                SQLiteStatement stmt = getDb().compileStatement(sql);
+                assertEquals(expectedValue, stmt.simpleQueryForLong());
+                stmt.close();
+                Cursor c = getDb().rawQuery(sql, null);
+                assertEquals(1, c.getCount());
+                c.moveToFirst();
+                assertEquals(expectedValue, c.getLong(0));
+                c.close();
+            }
+        };
+        t.start();
+        // wait until the above thread is done
+        t.join();
+        // commit and then query. should see changes from the transaction
+        mDatabase.endTransaction();
+        stmt = mDatabase.compileStatement(sql);
+        final int expectedValue2 = (stmtType == DELETE) ? 9 : 10;
+        assertEquals(expectedValue2, stmt.simpleQueryForLong());
+        stmt.close();
+        c = mDatabase.rawQuery(sql, null);
+        assertEquals(1, c.getCount());
+        c.moveToFirst();
+        assertEquals(expectedValue2, c.getLong(0));
+        c.close();
+    }
+    private synchronized SQLiteDatabase getDb() {
+        return mDatabase;
+    }
+
+    /**
      * 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>
@@ -198,6 +296,8 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
                 // set up connection pool
                 mDatabase.enableWriteAheadLogging();
                 mDatabase.setConnectionPoolSize(i + 1);
+            } else {
+                mDatabase.disableWriteAheadLogging();
             }
             mDatabase.execSQL("CREATE TABLE t1 (i int, j int);");
             mDatabase.execSQL("CREATE TABLE t2 (i int, j int);");
@@ -345,8 +445,7 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
 
     @SmallTest
     public void testLruCachingOfSqliteCompiledSqlObjs() {
-        mDatabase.disableWriteAheadLogging();
-        mDatabase.execSQL("CREATE TABLE test (i int, j int);");
+        createTableAndClearCache();
         // set cache size
         int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
         mDatabase.setMaxSqlCacheSize(N);
@@ -393,14 +492,27 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
          */
     }
 
+    private void createTableAndClearCache() {
+        mDatabase.disableWriteAheadLogging();
+        mDatabase.execSQL("DROP TABLE IF EXISTS " + TEST_TABLE);
+        mDatabase.execSQL("CREATE TABLE " + TEST_TABLE + " (i int, j int);");
+        mDatabase.enableWriteAheadLogging();
+        mDatabase.lock();
+        // flush the above statement from cache and close all the pending statements to be released
+        mDatabase.deallocCachedSqlStatements();
+        mDatabase.closePendingStatements();
+        mDatabase.unlock();
+        assertEquals(0, mDatabase.getQueuedUpStmtList().size());
+    }
+
     /**
      * test to make sure the statement finalizations are not done right away but
      * piggy-backed onto the next sql statement execution on the same database.
      */
     @SmallTest
     public void testStatementClose() {
-        mDatabase.execSQL("CREATE TABLE test (i int, j int);");
-        // fill up statement cache in mDatabase\
+        createTableAndClearCache();
+        // fill up statement cache in mDatabase
         int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
         mDatabase.setMaxSqlCacheSize(N);
         SQLiteStatement stmt;
@@ -429,7 +541,7 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         // execute something to see if this statement gets finalized
         mDatabase.execSQL("delete from test where i = 10;");
         statementIds = mDatabase.getQueuedUpStmtList();
-        assertEquals(0, statementIds.size());
+        assertFalse(statementIds.contains(stmt0Id));
     }
 
     /**
@@ -439,16 +551,16 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
      */
     @LargeTest
     public void testStatementCloseDiffThread() throws InterruptedException {
-        mDatabase.execSQL("CREATE TABLE test (i int, j int);");
+        createTableAndClearCache();
+        final int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
+        mDatabase.setMaxSqlCacheSize(N);
         // fill up statement cache in mDatabase in a thread
         Thread t1 = new Thread() {
             @Override public void run() {
-                int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
-                mDatabase.setMaxSqlCacheSize(N);
                 SQLiteStatement stmt;
-                for (int i = 0; i < N; i ++) {
+                for (int i = 0; i < N; i++) {
                     ClassToTestSqlCompilationAndCaching c =
-                        ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                        ClassToTestSqlCompilationAndCaching.create(getDb(),
                                 "insert into test values(" + i + ", ?);");
                     // keep track of 0th entry
                     if (i == 0) {
@@ -461,45 +573,44 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         t1.start();
         // wait for the thread to finish
         t1.join();
+        // mDatabase shouldn't have any statements to be released
+        assertEquals(0, mDatabase.getQueuedUpStmtList().size());
 
         // add one more to the cache - and the above 'stmt0Id' should fall out of cache
         // just for the heck of it, do it in a separate thread
         Thread t2 = new Thread() {
             @Override public void run() {
                 ClassToTestSqlCompilationAndCaching stmt1 =
-                        ClassToTestSqlCompilationAndCaching.create(mDatabase,
-                                "insert into test values(100, ?);");
+                    ClassToTestSqlCompilationAndCaching.create(getDb(),
+                            "insert into test values(100, ?);");
+                stmt1.bindLong(1, 1);
                 stmt1.close();
             }
         };
         t2.start();
         t2.join();
 
-        // close() in the above thread should have queuedUp the statement for finalization
-        ArrayList<Integer> statementIds = mDatabase.getQueuedUpStmtList();
-        assertTrue(getStmt0Id() > 0);
-        assertTrue(statementIds.contains(stmt0Id));
+        // close() in the above thread should have queuedUp the stmt0Id for finalization
+        ArrayList<Integer> statementIds = getDb().getQueuedUpStmtList();
+        assertTrue(statementIds.contains(getStmt0Id()));
         assertEquals(1, statementIds.size());
 
         // execute something to see if this statement gets finalized
         // again do it in a separate thread
         Thread t3 = new Thread() {
             @Override public void run() {
-                mDatabase.execSQL("delete from test where i = 10;");
+                getDb().execSQL("delete from test where i = 10;");
             }
         };
         t3.start();
         t3.join();
 
         // is the statement finalized?
-        statementIds = mDatabase.getQueuedUpStmtList();
-        assertEquals(0, statementIds.size());
+        statementIds = getDb().getQueuedUpStmtList();
+        assertFalse(statementIds.contains(getStmt0Id()));
     }
 
     private volatile int stmt0Id = 0;
-    private synchronized void setStmt0Id(int stmt0Id) {
-        this.stmt0Id = stmt0Id;
-    }
     private synchronized int getStmt0Id() {
         return this.stmt0Id;
     }
@@ -510,16 +621,16 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
      */
     @LargeTest
     public void testStatementCloseByDbClose() throws InterruptedException {
-        mDatabase.execSQL("CREATE TABLE test (i int, j int);");
+        createTableAndClearCache();
         // fill up statement cache in mDatabase in a thread
         Thread t1 = new Thread() {
             @Override public void run() {
                 int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
-                mDatabase.setMaxSqlCacheSize(N);
+                getDb().setMaxSqlCacheSize(N);
                 SQLiteStatement stmt;
                 for (int i = 0; i < N; i ++) {
                     ClassToTestSqlCompilationAndCaching c =
-                            ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                            ClassToTestSqlCompilationAndCaching.create(getDb(),
                                     "insert into test values(" + i + ", ?);");
                     // keep track of 0th entry
                     if (i == 0) {
@@ -538,7 +649,7 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         Thread t2 = new Thread() {
             @Override public void run() {
                 ClassToTestSqlCompilationAndCaching stmt1 =
-                        ClassToTestSqlCompilationAndCaching.create(mDatabase,
+                        ClassToTestSqlCompilationAndCaching.create(getDb(),
                                 "insert into test values(100, ?);");
                 stmt1.bindLong(1, 1);
                 stmt1.close();
@@ -548,7 +659,7 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         t2.join();
 
         // close() in the above thread should have queuedUp the statement for finalization
-        ArrayList<Integer> statementIds = mDatabase.getQueuedUpStmtList();
+        ArrayList<Integer> statementIds = getDb().getQueuedUpStmtList();
         assertTrue(getStmt0Id() > 0);
         assertTrue(statementIds.contains(stmt0Id));
         assertEquals(1, statementIds.size());
@@ -558,14 +669,244 @@ public class SQLiteDatabaseTest extends AndroidTestCase {
         // again do it in a separate thread
         Thread t3 = new Thread() {
             @Override public void run() {
-                mDatabase.close();
+                getDb().close();
             }
         };
         t3.start();
         t3.join();
 
         // check mClosedStatementIds in mDatabase. it should be empty
-        statementIds = mDatabase.getQueuedUpStmtList();
+        statementIds = getDb().getQueuedUpStmtList();
         assertEquals(0, statementIds.size());
     }
+
+    /**
+     * This test tests usage execSQL() to begin transaction works in the following way
+     *   Thread #1 does
+     *       execSQL("begin transaction");
+     *       insert()
+     *   Thread # 2
+     *       query()
+     *   Thread#1 ("end transaction")
+     * Thread # 2 query will execute - because java layer will not have locked the SQLiteDatabase
+     * object and sqlite will consider this query to be part of the transaction.
+     *
+     * but if thread # 1 uses beginTransaction() instead of execSQL() to start transaction,
+     * then Thread # 2's query will have been blocked by java layer
+     * until Thread#1 ends transaction.
+     *
+     * @throws InterruptedException
+     */
+    @SmallTest
+    public void testExecSqlToStartAndEndTransaction() throws InterruptedException {
+        runExecSqlToStartAndEndTransaction("END");
+        // same as above, instead now do "COMMIT" or "ROLLBACK" instead of "END" transaction
+        runExecSqlToStartAndEndTransaction("COMMIT");
+        runExecSqlToStartAndEndTransaction("ROLLBACK");
+    }
+    private void runExecSqlToStartAndEndTransaction(String str) throws InterruptedException {
+        createTableAndClearCache();
+        // disable WAL just so queries and updates use the same database connection
+        mDatabase.disableWriteAheadLogging();
+        mDatabase.execSQL("BEGIN transaction");
+        // even though mDatabase.beginTransaction() is not called to start transaction,
+        // mDatabase connection should now be in transaction as a result of
+        // mDatabase.execSQL("BEGIN transaction")
+        // but mDatabase.mLock should not be held by any thread
+        assertTrue(mDatabase.inTransaction());
+        assertFalse(mDatabase.isDbLockedByCurrentThread());
+        assertFalse(mDatabase.isDbLockedByOtherThreads());
+        assertTrue(mDatabase.amIInTransaction());
+        mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);");
+        assertTrue(mDatabase.inTransaction());
+        assertFalse(mDatabase.isDbLockedByCurrentThread());
+        assertFalse(mDatabase.isDbLockedByOtherThreads());
+        assertTrue(mDatabase.amIInTransaction());
+        Thread t = new Thread() {
+            @Override public void run() {
+                assertTrue(mDatabase.amIInTransaction());
+                assertEquals(999, DatabaseUtils.longForQuery(getDb(),
+                        "select j from " + TEST_TABLE + " WHERE i = 10", null));
+                assertTrue(getDb().inTransaction());
+                assertFalse(getDb().isDbLockedByCurrentThread());
+                assertFalse(getDb().isDbLockedByOtherThreads());
+                assertTrue(mDatabase.amIInTransaction());
+            }
+        };
+        t.start();
+        t.join();
+        assertTrue(mDatabase.amIInTransaction());
+        assertTrue(mDatabase.inTransaction());
+        assertFalse(mDatabase.isDbLockedByCurrentThread());
+        assertFalse(mDatabase.isDbLockedByOtherThreads());
+        mDatabase.execSQL(str);
+        assertFalse(mDatabase.amIInTransaction());
+        assertFalse(mDatabase.inTransaction());
+        assertFalse(mDatabase.isDbLockedByCurrentThread());
+        assertFalse(mDatabase.isDbLockedByOtherThreads());
+    }
+
+    /**
+     * test the following
+     * http://b/issue?id=2871037
+     *          Cursor cursor = db.query(...);
+     *          // with WAL enabled, the above uses a pooled database connection
+     *          db.beginTransaction()
+     *          try {
+     *            db.insert(......);
+     *            cursor.requery();
+     *            // since the cursor uses pooled database connection, the above requery
+     *            // will not return the results that were inserted above since the insert is
+     *            // done using main database connection AND the transaction is not committed yet.
+     *            // fix is to make the above cursor use the main database connection - and NOT
+     *            // the pooled database connection
+     *            db.setTransactionSuccessful()
+     *          } finally {
+     *            db.endTransaction()
+     *          }
+     *
+     * @throws InterruptedException
+     */
+    @SmallTest
+    public void testTransactionAndWalInterplay1() throws InterruptedException {
+        createTableAndClearCache();
+        mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);");
+        String sql = "select * from " + TEST_TABLE;
+        Cursor c = mDatabase.rawQuery(sql, null);
+        // should have 1 row in the table
+        assertEquals(1, c.getCount());
+        mDatabase.beginTransactionNonExclusive();
+        try {
+            mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);");
+            assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
+                    "select count(*) from " + TEST_TABLE, null));
+            // requery on the previously opened cursor
+            // cursor should now use the main database connection and see 2 rows
+            c.requery();
+            assertEquals(2, c.getCount());
+            mDatabase.setTransactionSuccessful();
+        } finally {
+            mDatabase.endTransaction();
+        }
+        c.close();
+
+        // do the same test but now do the requery in a separate thread.
+        createTableAndClearCache();
+        mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);");
+        final Cursor c1 = mDatabase.rawQuery("select count(*) from " + TEST_TABLE, null);
+        // should have 1 row in the table
+        assertEquals(1, c1.getCount());
+        mDatabase.beginTransactionNonExclusive();
+        try {
+            mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);");
+            assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
+                    "select count(*) from " + TEST_TABLE, null));
+            // query in a different thread. that causes the cursor to use a pooled connection
+            // and since this thread hasn't committed its changes, the cursor should still see only
+            // 1 row
+            Thread t = new Thread() {
+                @Override public void run() {
+                    c1.requery();
+                    assertEquals(1, c1.getCount());
+                }
+            };
+            t.start();
+            t.join();
+            // should be 2 rows now - including the the row inserted above
+            mDatabase.setTransactionSuccessful();
+        } finally {
+            mDatabase.endTransaction();
+        }
+        c1.close();
+    }
+
+    /**
+     * This test is same as {@link #testTransactionAndWalInterplay1()} except the following:
+     * instead of mDatabase.beginTransactionNonExclusive(), use execSQL("BEGIN transaction")
+     * and instead of mDatabase.endTransaction(), use execSQL("END");
+     */
+    @SmallTest
+    public void testTransactionAndWalInterplay2() throws InterruptedException {
+        createTableAndClearCache();
+        mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);");
+        String sql = "select * from " + TEST_TABLE;
+        Cursor c = mDatabase.rawQuery(sql, null);
+        // should have 1 row in the table
+        assertEquals(1, c.getCount());
+        mDatabase.execSQL("BEGIN transaction");
+        try {
+            mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);");
+            assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
+                    "select count(*) from " + TEST_TABLE, null));
+            // requery on the previously opened cursor
+            // cursor should now use the main database connection and see 2 rows
+            c.requery();
+            assertEquals(2, c.getCount());
+        } finally {
+            mDatabase.execSQL("commit;");
+        }
+        c.close();
+
+        // do the same test but now do the requery in a separate thread.
+        createTableAndClearCache();
+        mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);");
+        final Cursor c1 = mDatabase.rawQuery("select count(*) from " + TEST_TABLE, null);
+        // should have 1 row in the table
+        assertEquals(1, c1.getCount());
+        mDatabase.execSQL("BEGIN transaction");
+        try {
+            mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);");
+            assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
+                    "select count(*) from " + TEST_TABLE, null));
+            // query in a different thread. but since the transaction is started using
+            // execSQ() instead of beginTransaction(), cursor's query is considered part of
+            // the same ransaction - and hence it should see the above inserted row
+            Thread t = new Thread() {
+                @Override public void run() {
+                    c1.requery();
+                    assertEquals(1, c1.getCount());
+                }
+            };
+            t.start();
+            t.join();
+            // should be 2 rows now - including the the row inserted above
+        } finally {
+            mDatabase.execSQL("commit");
+        }
+        c1.close();
+    }
+
+    /**
+     * This test is same as {@link #testTransactionAndWalInterplay2()} except the following:
+     * instead of commiting the data, do rollback and make sure the data seen by the query
+     * within the transaction is now gone.
+     */
+    @SmallTest
+    public void testTransactionAndWalInterplay3() throws InterruptedException {
+        createTableAndClearCache();
+        mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(10, 999);");
+        String sql = "select * from " + TEST_TABLE;
+        Cursor c = mDatabase.rawQuery(sql, null);
+        // should have 1 row in the table
+        assertEquals(1, c.getCount());
+        mDatabase.execSQL("BEGIN transaction");
+        try {
+            mDatabase.execSQL("INSERT into " + TEST_TABLE + " values(100, 9909);");
+            assertEquals(2, DatabaseUtils.longForQuery(mDatabase,
+                    "select count(*) from " + TEST_TABLE, null));
+            // requery on the previously opened cursor
+            // cursor should now use the main database connection and see 2 rows
+            c.requery();
+            assertEquals(2, c.getCount());
+        } finally {
+            // rollback the change
+            mDatabase.execSQL("rollback;");
+        }
+        // since the change is rolled back, do the same query again and should now find only 1 row
+        c.requery();
+        assertEquals(1, c.getCount());
+        assertEquals(1, DatabaseUtils.longForQuery(mDatabase,
+                "select count(*) from " + TEST_TABLE, null));
+        c.close();
+    }
 }