Android Sqlite multi threading save data with DBFlow + Retrofit

Question

I'm using DBFlow to save into the database and Retrofit to call my web service. My retrofit class is the same class as my Database table. But I have a problem when 2 threads or more are launched at the same time to save my data into the table. The insert into my table duplicate my data and the primary key is duplicated too. Then my thread is stopped because it crashes.

Have you a solution for that ?

Class for Retrofit and DBFlow

@Table(database = LocalDB.class)
@Root(name = "picture_infos")
public class PictureInfos extends BaseModel {

@PrimaryKey
@Element(name = "id_picture")
private int idPicture;

@Column
@Element(name = "id_account")
private String idAccount;

@Column
@Element(name = "folder_path")
private String folderPath;

@Column
@Element(name = "filename")
private String filename;

@Column
@Element(name = "legend", required = false)
private String legend;

public int getIdPicture() {
    return idPicture;
}

public void setIdPicture(int idPicture) {
    this.idPicture = idPicture;
}

public String getIdAccount() {
    return idAccount;
}

public void setIdAccount(String idAccount) {
    this.idAccount = idAccount;
}

public String getFolderPath() {
    return folderPath;
}

public void setFolderPath(String folderPath) {
    this.folderPath = folderPath;
}

public String getFilename() {
    return filename;
}

public void setFilename(String filename) {
    this.filename = filename;
}

public String getLegend() {
    return legend;
}

public void setLegend(String legend) {
    this.legend = legend;
}
}

Thread in retrofit response

public void onResponse(Call<AdminPictures> call, Response<AdminPictures> response) {
            AdminPictures apResponse = response.body();
            final List<PictureInfos> pictureInfos = apResponse.getPicturesList();
            new Thread(new Runnable() {
                @Override
                    public void run() {
                        try {
                            for (PictureInfos infos : pictureInfos) {
                    // This save duplicate when I've 2 or more threads
                              synchronized (infos){
                                if(!infos.exists()){
                                   infos.save();
                                 }
                              }
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }).start();

Stacktrace

    03-18 12:01:18.950 15696-19086/com.vigizen.client.kiosqueadmin E/SQLiteLog: (1555) abort at 12 in [INSERT INTO `PictureInfos`(`idPicture`,`idAccount`,`folderPath`,`filename`,`legend`) VALUES (?,?,?,?,?)]: UNIQUE constraint failed: PictureInfos.idPicture
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: PictureInfos.idPicture (code 1555)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:788)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at com.raizlabs.android.dbflow.structure.database.AndroidDatabaseStatement.executeInsert(AndroidDatabaseStatement.java:77)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at com.raizlabs.android.dbflow.sql.SqlUtils.insert(SqlUtils.java:370)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at com.raizlabs.android.dbflow.sql.SqlUtils.save(SqlUtils.java:327)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at com.raizlabs.android.dbflow.structure.ModelAdapter.save(ModelAdapter.java:60)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at com.raizlabs.android.dbflow.structure.BaseModel.save(BaseModel.java:52)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at com.vigizen.client.kiosqueadmin.GalleryActivity$1$1.run(GalleryActivity.java:188)
    03-18 12:01:18.951 15696-19086/com.vigizen.client.kiosqueadmin W/System.err:     at java.lang.Thread.run(Thread.java:818)

`Synchronize` the method using save call, so that only one thread is updating in to the database at a time — Viswanath Lekshmanan, Mar 18 '16 at 09:51
I think the attributes that can be accessed in several threads at a time should be volatile to prevent concurrent write/read ? (I'm not specialist but that makes sens to me) EDIT : @ViswanathLekshmanan response is better I think — An-droid, Mar 18 '16 at 09:54
I've try volatile members but there is no changes. @ViswanathLekshmanan I don't understand what you mean. — John, Mar 18 '16 at 10:04

NiThDi · Answer 1 · 2016-12-08T11:06:57.490

What you are doing here is the first "dont' do" in the docs of DBFlow. What you should use here is a transaction to store the data. This locks the database for an exclusive batch operation and should be a lot faster than iterating over all you models and save them one by one.

Basically, you want to use FastStoreModelTransaction.saveBuilder() here which basically does a INSERT OR UPDATE.

Usage would be like that:

public void onResponse(Call<AdminPictures> call, Response<AdminPictures> response) {
    AdminPictures apResponse = response.body();
    final List<PictureInfos> pictureInfos = apResponse.getPicturesList();

    FastStoreModelTransaction transaction = FastStoreModelTransaction.saveBuilder(FlowManager.getModelAdapter(PictureInfos.class))
        .addAll(pictureInfos)
        .build();

    database.executeTransaction(transaction);
}

Please note that this would automatically runs in the background, so no need to wrap it in an extra thread. If you need a listener on the storing process, use the ProcessModelTransaction instead.

Viswanath Lekshmanan · Answer 2 · 2016-03-18T10:17:29.313

-2

Reading database can be done in parallel without interrupting each other, but writing in to database should be done in a synchronized way if you have keys.

You should write the save within a synchronized method. Synchronize will allow only one thread to do changes at a time.

Note: By default android SQLiteDatabase Object is safe for multithreading. DBFlow is also. Make sure you are using the same instances of helpers in multiple threads.

Details

edited Mar 18 '16 at 10:17

answered Mar 18 '16 at 10:11

Viswanath Lekshmanan

9,945
1
40
64

Ok thanks I don't know the synchronized. I tried your method but I think I do a mistake. `synchronized (infos){ if (!infos.exists()) { infos.save(); } }` – John Mar 18 '16 at 10:20
@John Primary key should not get duplicated in database at any cost. That is a real issue. Log the execution including the keys and please post before you synchronize it – Viswanath Lekshmanan Mar 18 '16 at 10:25
When I execute the thread after the first was end, I don't have the primary key issue. I have the good value for each primary key. The problem comes when I launch 2 threads in same time and I have this error E/SQLiteLog: (1555) abort at 12 in [INSERT INTO `PictureInfos`(`idPicture`,`idAccount`,`folderPath`,`filename`,`legend`) VALUES (?,?,?,?,?)]: UNIQUE constraint failed: PictureInfos.idPicture – John Mar 18 '16 at 10:39
How same primary key is assigned to both objects ? That is not a multithread issue . How you are creating objects to insert ? Show the whole code including creation and execution – Viswanath Lekshmanan Mar 18 '16 at 10:50
I don't know why but the problem comes when this thread is called twice – John Mar 18 '16 at 10:52
there is the whole code here. Retrofit fill my object during the data parsing. And with the .save() of DBFlow I insert my data. Maybe Retrofit + DBFlow doesn't work ? Or I should create one class for Retrofit and one class for DBflow ? – John Mar 18 '16 at 10:54
No thats fine. Check how many times you send request. Log that as well. Issue may be requesting same multiple times – Viswanath Lekshmanan Mar 18 '16 at 10:56
First retrofit call : 265 insert in the database and second call 303 insert. And when I call them at the same time, there is the crash so the thread is done. – John Mar 18 '16 at 11:01
please post the stacktrace of the crash – Viswanath Lekshmanan Mar 18 '16 at 11:03
Maybe this line ` final List pictureInfos = apResponse.getPicturesList();` can be a problem ? Because this variable is final ? – John Mar 18 '16 at 11:03
Absolutely you are correct. Since you have 2 different primary key, you dont have the uniques constraint exception. remove the final one or create different instance of interfaces – Viswanath Lekshmanan Mar 18 '16 at 11:05
Stacktrace in my question – John Mar 18 '16 at 11:05
Saw that, If you log the primary key just above the save call , you will get the same value for both. Its because of the final List – Viswanath Lekshmanan Mar 18 '16 at 11:06
Ok I'll try to make a private variable instead of final – John Mar 18 '16 at 11:07
No that will not make any difference. – Viswanath Lekshmanan Mar 18 '16 at 11:08
You are requesting multiple times using same interface. Use a different instance – Viswanath Lekshmanan Mar 18 '16 at 11:09
I don't know how I can do that if I can't use final or a private member. And I can't send a parameter to the run function – John Mar 18 '16 at 11:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/106712/discussion-between-john-and-viswanath-lekshmanan). – John Mar 18 '16 at 12:01
I tried to set 2 different list in two threads. And I don't know why my list are mixed ... see thread n°1 : 3068 thread n°1 : 3067 thread n°1 : 3066 thread n°2 : 3068 thread n°2 : 3067 thread n°2 : 3066 – John Mar 18 '16 at 14:43
There is no threading issue in this. Only mistake on some part. You just debug the execution step by step after receiving the request – Viswanath Lekshmanan Mar 20 '16 at 17:54

Android Sqlite multi threading save data with DBFlow + Retrofit

2 Answers2

Linked