0

I am working on an Android application which has most of the code in C. I have compiled SQLite in C and using it.

I have following code:

void func_foo(){

Select X from table1

Update X in table1

}

I want that func_foo behaves atomically and this function is getting called from multiple threads.

My concern is that once I ran select statement from thread1, thread2 should not modify the data of the tables in between.

So will wrapping the whole DB operation inside a transaction make it atomic?

Or do I need to use mutex over the function so that only one instance can access the DB?

Pankaj Goyal
  • 103
  • 1
  • 11

3 Answers3

0

I've refereed several stack answers and blog posts about how exactly to do that:

1. What are the best practices for SQLite on Android?

2. http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection

Number 1 is best, Number 2 is a lot of extra work and unnecessary. ContentProvider exists so you can share data with other apps.

Jay Patel
  • 2,341
  • 2
  • 22
  • 43
  • I am not using the SQLite in java provided by Android. But I am using the sqlite in my c code which is taking to java using JNI and I have compiled sqlite3 in my c code. – Pankaj Goyal Jun 07 '17 at 07:52
0

For the function to be atomic, it must be wrapped in a transaction. (Or you must ensure that the code calling it uses a transaction. The Android database framework has recursive transactions; in C, you'd have to implement that yourself.)

Additionally, you have to ensure that multiple threads do not end up using the same transaction, so you have to

  • give each thread its own database connection, or
  • use some kind of lock (e.g., a mutex) to ensure that no two threads run a transaction at the same time.
CL.
  • 173,858
  • 17
  • 217
  • 259
  • My question is that if a transaction contains select statements followed by update statement, since there would not be any write to db till the update is executed, would be read lock only till the write to db is required. Or will the sqlite lock the db as soon as transaction starts irrespective of whether I am executing select statement inside the transaction ? – Pankaj Goyal Jun 07 '17 at 11:06
  • It's [more complex](http://www.sqlite.org/atomiccommit.html), but to summarize, SQLite works correctly. – CL. Jun 07 '17 at 11:23
0

The solution to the problem is to either Immediate or Exclusive transaction. By default Transaction are deferred transaction so they allow other thread to read the db till we start modifying the db. The following link explains it. http://www.sqlite.org/lang_transaction.html

Pankaj Goyal
  • 103
  • 1
  • 11