Android Sqlite DB 事务、连接、串行执行

What are the best practices for SQLite on Android?  https://stackoverflow.com/questions/2493331/what-are-the-best-practices-for-sqlite-on-android/3689883#3689883

The SqliteOpenHelper object holds on to one database connection. It appears to offer you a read and write connection, but it really doesn't. Call the read-only, and you'll get the write database connection regardless.

So, one helper instance, one db connection. Even if you use it from multiple threads, one connection at a time. The SqliteDatabase object uses java locks to keep access serialized. So, if 100 threads have one db instance, calls to the actual on-disk database are serialized.

So, one helper, one db connection, which is serialized in java code. One thread, 1000 threads, if you use one helper instance shared between them, all of your db access code is serial. And life is good (ish).

If you try to write to the database from actual distinct connections at the same time, one will fail. It will not wait till the first is done and then write. It will simply not write your change. Worse, if you don’t call the right version of insert/update on the SQLiteDatabase, you won’t get an exception. You’ll just get a message in your LogCat, and that will be it.

So, multiple threads? Use one helper. Period. If you KNOW only one thread will be writing, you MAY be able to use multiple connections, and your reads will be faster, but buyer beware. I haven't tested that much.

---

Android Sqlite Locking   https://touchlabblog.tumblr.com/post/24474398246/android-sqlite-locking

Open source is great, by the way.  You can dig right into the code and see what’s going on.  From that and some testing, I’ve learned the following are true:

• Sqlite takes care of the file level locking.  Many threads can read, one can write.  The locks prevent more than one writing.
• Android implements some java locking in SQLiteDatabase to help keep things straight.
• If you go crazy and hammer the database from many threads, your database will (or should) not be corrupted.

Here’s what’s missing.  If you try to write to the database from actual distinct connections at the same time, one will fail.  It will not wait till the first is done and then write.  It will simply not write your change.  Worse, if you don’t call the right version of insert/update on the SQLiteDatabase, you won’t get an exception.  You’ll just get a message in your LogCat, and that will be it

The first problem, real, distinct connections.  The great thing about open source code is you can dig right in and see what’s going on.  The SQLiteOpenHelper class does some funny things.  Although there is a method to get a read-only database connection as well as a read-write connection, under the hood, its always the same connection.  Assuming there are no file write errors, even the read-only connection is really the single, read-write connection.  Pretty funny.  So, if you use one helper instance in your app, even from multiple threads, you never *really* using multiple connections.

Also, the SQLiteDatabase class, of which each helper has only one instance, implements java level locking on itself.  So, when you’re actually executing database operations, all other db operations will be locked out.  So, even if you have multiple threads doing stuff, if you’re doing it to maximize database performance, I have some bad news for you.  No benefit.

Interesting Observations

If you turn off one writing thread, so only one thread is writing to the db, but another reading, and both have their own connections, the read performance shoots WAY up and I don’t see any lock issues.  That’s something to pursue.  I have not tried that with write batching yet.

If you are going to perform more than one update of any kind, wrap it in a transaction.  It seems like the 50 updates I do in the transaction (this was in the original app version, not the new one) take the same amount of time as the 1 update outside of the transaction.  My guess is that outside of the transaction calls, each update attempts to write the db changes to disk.  Inside the transaction, the writes are done in one block, and the overhead of writing dwarfs the update logic itself.

Performance of database calls is all over the place at different times.  Any serious db calls should probably be in an async process, just in case there’s something else going on behind the scenes.

---

Single SQLite connection  https://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection

If you look back at my earlier post, android sqlite locking, I demonstrated that more than one sqlite connection was really bad. Bad-bad. OK, so now what? How do you keep one connection and manage it?

Back when I worked on some earlier versions of ORMLite, I coded this big, complex framework that would do reference counting, and close the connection when nobody had a reference to it, and that worked pretty good.  The downside?  You had to extend my base classes for Activity, Service, etc, and if you wanted to integrate my code with either your class hierarchy, or worse, a 3rd party, it was a huge pain.

It kind of dawned on me.  The way SQLite works, it would be basically impossible to corrupt your database, unless there’s a bug in the SQLite code, or a hardware issue.  What I’m going to say is controversial, but I’ve done some snooping and testing, and I’m 99.99% sure its the way to go.

Keep a single SQLiteOpenHelper instance in a static context.  Do lazy initialization, and synchronize that method.  When do you close it?  You don’t.  When the app shuts down, it’ll let go of the file reference, if its even holding on to it.

What?!?!?!?!?!?! You have to close your databases!!!!!!!

Settle down.  It works.  Chances are, if you have a db in your app, you’re probably using it in most/all of your Activities and Services.  The “connection” is just a file handle.  When you do writes, they are flushed to disk immediately.

“But Kevin, what about that ‘close() was never explicitly called on database’ exception?”

If you pay attention, you don’t get that exception when the connection is just “hanging out”.  You get it when you ALREADY have a connection that was opened, and you try to open another one.  If you’re doing it right, you only open the connection once.  Presumably, that error triggers when the system notices somebody else has a lock on the file (I’d guess a read lock, because even though you get the exception, you can still see the DB).

What would it look like?

public class DatabaseHelper extends OrmLiteSqliteOpenHelper
{
    private static DatabaseHelper instance;

    public static synchronized DatabaseHelper getHelper(Context context)
    {
        if (instance == null)
            instance = new DatabaseHelper(context);

        return instance;
    }
//Other stuff... 
}

Boom. That’s it. It’ll work.