diff options
author | Vasu Nori <vnori@google.com> | 2010-07-22 13:57:13 -0700 |
---|---|---|
committer | Vasu Nori <vnori@google.com> | 2010-08-02 14:51:33 -0700 |
commit | ce38b98feb1e7c9c1799eb270c40798d833aa9ae (patch) | |
tree | 416f14f09c5ff44a35a2a90272bccf28672aafc2 /core | |
parent | b0289f62af77a2811b4528fe284aad871e3c6b31 (diff) | |
download | frameworks_base-ce38b98feb1e7c9c1799eb270c40798d833aa9ae.zip frameworks_base-ce38b98feb1e7c9c1799eb270c40798d833aa9ae.tar.gz frameworks_base-ce38b98feb1e7c9c1799eb270c40798d833aa9ae.tar.bz2 |
do begin-end transaction before standalone insert/update/delete sql
also fix bug# 2871037
Change-Id: I13325f8eabff4f218d3206905010803b61d8e2cd
Diffstat (limited to 'core')
7 files changed, 577 insertions, 135 deletions
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(); + } } |