Does it allow to create many transactions at a time in Android SQLite

Reading this document, it says that SQLite supports transaction and nested transaction. I wonder that it is possible to create many different transactions.

For example: I have 2 threads access to a database connection, is it posible to create a transaction on each thread and they are not nested?

Answers


Sqlite does not support nested transactions:

Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands.

I myself have experienced deadlocks when using nested transactions with the SQLiteDatabase class, so I strongly encourage you to stay away from nested transactions. Use your own locks to prevent your transactions from nesting.


Creating two separate transactions on two separate threads is not considered "nesting transactions".

Each thread maintains its own db session, which maps to 0 or 1 DB connections (a DB connections can be picked from a connection pool and returned to it if the connection is no longer needed by the session, so at any time, the session has a connection or not, depending on if its needed).

Therefore, each thread maintains its own trasaction state indepdnent of other threads. The sources confirm this:

../Android/sdk/sources/android-23/android/database/sqlite/SQLiteDatabase.java:

// Thread-local for database sessions that belong to this database.
// Each thread has its own database session.
// INVARIANT: Immutable.
private final ThreadLocal<SQLiteSession> mThreadSession = new ThreadLocal<SQLiteSession>() {
    @Override
    protected SQLiteSession initialValue() {
        return createSession();
    }
};

../Android/sdk/sources/android-23/android/database/sqlite/SQLiteSession.java:

 * <h2>About database sessions</h2>
 * <p>
 * Database access is always performed using a session.  The session
 * manages the lifecycle of transactions and database connections.
 * </p><p>

...

* Consequently, each thread
 * has its own session object and therefore its own transaction state independent
 * of other threads.

So if you have two transactions going on in seperate threads, they are not "nested" but completely independent and will run in parallel or be serialized depending on the modes and types of the transactions involved (ie. whether they are deferred, immediate, exclusive and whether they are read or write).

However, the Android SQLite libraries must be used properly to achieve this.

I have 2 threads access to a database connection

If this means both threads are using the same SQLiteSession, (although I'm not sure how you can accomplish this if its even possible), then that could very well lead to a deadlock.

My setup:

  1. Single content provider
  2. Content Provider initializes member variable mDbHelper for SQLiteOpenHelper in its onCreate()
  3. Call mHelper.getReadableDatabase / getWriteableDatabase as needed in the Content Provider's query/insert/ ... methods
  4. Android will handle ensuring separate transaction state on each thread that uses the Content Provider

If a long running transaction is going to be execute on a thread (eg. data import from sync on background thread), consider using db.yieldIfContended() or db.yieldIfContendedSafely() to allow transactions from other threads (say or record update on UI thread) to squeeze in so that they do not have wait for the long-running transactions to finish and thus block those threads.


Need Your Help

Best HTML CSS color tool

html colors

Does anyone have a good link to an HTML color chart or tool where I can click on exactly the color I want and it shows me the HTML color for it?

How to Change Highchart Column Color Dynamicly

javascript highcharts

I want to set color of highchart column according to average or limit value. For example, limit value is %20, columns value that over %20 will have red color, or under 20% will have green color.