Lock a database or table in sqlite (Android)

Thats my problem:

I have a table with columns: ID, VALUE and SYNCHRONICED. That last is a flag that indicates if a row has been sent to the server since the last update. I have some thread running that can access to the database

And my problem comes in that use case (with 2 threads T1 and T2:

T1-->Start_Send-->Query_Values-->Send_to_server-->wait_answer-->sync=1_for_sent_rows
T2------------------------->Update_a_row_sent

At this point T1 has tagged with sync=1 the value updated by T2.

Is there any way of avoid that problem?

• The methods for query and update are diferent so cant use synchron开发者_如何学Goized at the method.
• There is no problem if T2 gets blocked until T1 ends

Thanks

---

Since you asked how to lock a database/table on android, you can use SQLiteDatabase.beginTransaction() to achieve that:

From android docs http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html:

public void beginTransaction ()

Since: API Level 1

Begins a transaction in EXCLUSIVE mode.

And from sqlite 3 http://www.sqlite.org/lang_transaction.html

After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.

beginTransaction starts a transaction and puts your database in exclusive lock, so when T1 select runs it blocks T2 from updating rows until T1 calls db.endTransaction() .

public yourMethod() {
    db.beginTransaction();

    //do whatever you want with db;

    db.setTransactionSuccessful(); 
    db.endTransaction();
}

---

Let's say SYNCHRONICED is 0 when the record is inserted or updated, 1 when the record is sent to the server, and 2 when the server has acknowledged the sync.

The T1 thread should do:

BEGIN;
SELECT ID, VALUE FROM TAB WHERE SYNCHRONICED = 0;
UPDATE TAB SET SYNCHRONICED = 1 WHERE SYNCHRONICED = 0;
COMMIT;

The select statement gives the records to send to the server.

Now any insert or update to TAB should set SYNCHRONICED = 0;

When the server responds with ack,

UPDATE TAB SET SYNCHRONICED = 2 WHERE SYNCHRONICED = 1;

This will not affect any records updated or inserted since their SYNCHRONICED is 0.