Getting "UNIQUE constraint failed" exception using Ormlite createOrUpdate() method

Question

I have several calls throughout my app where tables in my database are updated through calls to .createOrUpdate()

The documentation suggests from this calls:

This is a convenience method for creating an item in the database if it does not exist. The id is extracted from the data parameter and a query-by-id is made on the database. If a row in the database with the same id exists then all of the columns in the database will be updated from the fields in the data parameter. If the id is null (or 0 or some other default value) or doesn't exist in the database then the object will be created in the database. This also means that your data item must have an id field defined.

My understanding is then that, when calling createOrUpdate(), the underlying code should be calling "insert" if the Database doesn't contain the row and "update" if the row exists.

What seems to be happening is that the underlying code is calling "insert" when the row already exists causing the exception.

Does anybody know why this is happening or how to avoid it?

Exception below:

java.lang.RuntimeException: java.sql.SQLException: Unable to run insert stmt on object
    Feed(id=126275579_organisations-email_58284484, type=organisations-email, category=1, msg=<p>Body</p>, regarding=Bobby Castle, subject=Subject, createdTs=2016-01-04T09:59:27+00:00, completed=0, read=0, starred=0, archived=0, remoteAttachments=[], localAttachments=null, authorId=250008275, authorName=Live Regression Test, authorImg=null): INSERT INTO `feed` (`id` ,`type` ,`category` ,`msg` ,`regarding` ,`subject` ,`createdTs` ,`completed` ,`read` ,`starred` ,`archived` ,`authorId` ,`authorName` ,`authorImg` ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
     at com.j256.ormlite.dao.RuntimeExceptionDao.createOrUpdate(RuntimeExceptionDao.java:252)
     at uk.co.test.test.data.orm.models.Feed.createOrUpdate(Feed.java:81)
     at uk.co.test.test.interactors.server.CategoryInteractor$6.onSuccessfulResponse(CategoryInteractor.java:308)
     at uk.co.test.test.interactors.server.CategoryInteractor$6.onSuccessfulResponse(CategoryInteractor.java:290)
     at uk.co.test.test.interactors.common.StandardCallbackRunnable.checkStandardResponse(StandardCallbackRunnable.java:27)
     at uk.co.test.test.interactors.common.StandardCallbackRunnable.checkStandardResponse(StandardCallbackRunnable.java:12)
     at uk.co.test.test.interactors.common.CallbackRunnable.success(CallbackRunnable.java:127)
     at retrofit.CallbackRunnable$1.run(CallbackRunnable.java:45)
     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
     at java.lang.Thread.run(Thread.java:818)
Caused by: java.sql.SQLException: Unable to run insert stmt on object Feed(id=126275579_organisations-email_58284484, type=organisations-email, category=1, msg=<p>Body</p>, regarding=Bobby Castle, subject=Subject, createdTs=2016-01-04T09:59:27+00:00, completed=0, read=0, starred=0, archived=0, remoteAttachments=[], localAttachments=null, authorId=250008275, authorName=Live Regression Test, authorImg=null): INSERT INTO `feed` (`id` ,`type` ,`category` ,`msg` ,`regarding` ,`subject` ,`createdTs` ,`completed` ,`read` ,`starred` ,`archived` ,`authorId` ,`authorName` ,`authorImg` ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
     at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22)
     at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:135)
     at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:450)
     at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:310)
     at com.j256.ormlite.dao.BaseDaoImpl.createOrUpdate(BaseDaoImpl.java:336)
     at com.j256.ormlite.dao.RuntimeExceptionDao.createOrUpdate(RuntimeExceptionDao.java:249)
     at uk.co.test.test.data.orm.models.Feed.createOrUpdate(Feed.java:81) 
     at uk.co.test.test.interactors.server.CategoryInteractor$6.onSuccessfulResponse(CategoryInteractor.java:308) 
     at uk.co.test.test.interactors.server.CategoryInteractor$6.onSuccessfulResponse(CategoryInteractor.java:290) 
     at uk.co.test.test.interactors.common.StandardCallbackRunnable.checkStandardResponse(StandardCallbackRunnable.java:27) 
     at uk.co.test.test.interactors.common.StandardCallbackRunnable.checkStandardResponse(StandardCallbackRunnable.java:12) 
     at uk.co.test.test.interactors.common.CallbackRunnable.success(CallbackRunnable.java:127) 
     at retrofit.CallbackRunnable$1.run(CallbackRunnable.java:45) 
     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112) 
     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587) 
     at java.lang.Thread.run(Thread.java:818) 
Caused by: java.sql.SQLException: inserting to database failed: INSERT INTO `feed` (`id` ,`type` ,`category` ,`msg` ,`regarding` ,`subject` ,`createdTs` ,`completed` ,`read` ,`starred` ,`archived` ,`authorId` ,`authorName` ,`authorImg` ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
     at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22)
     at com.j256.ormlite.android.AndroidDatabaseConnection.insert(AndroidDatabaseConnection.java:169)
     at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91)
     at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:450) 
     at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:310) 
     at com.j256.ormlite.dao.BaseDaoImpl.createOrUpdate(BaseDaoImpl.java:336) 
     at com.j256.ormlite.dao.RuntimeExceptionDao.createOrUpdate(RuntimeExceptionDao.java:249) 
     at uk.co.test.test.data.orm.models.Feed.createOrUpdate(Feed.java:81) 
     at uk.co.test.test.interactors.server.CategoryInteractor$6.onSuccessfulResponse(CategoryInteractor.java:308) 
     at uk.co.test.test.interactors.server.CategoryInteractor$6.onSuccessfulResponse(CategoryInteractor.java:290) 
     at uk.co.test.test.interactors.common.StandardCallbackRunnable.checkStandardResponse(StandardCallbackRunnable.java:27) 
     at uk.co.test.test.interactors.common.StandardCallbackRunnable.checkStandardResponse(StandardCallbackRunnable.java:12) 
     at uk.co.test.test.interactors.common.CallbackRunnable.success(CallbackRunnable.java:127) 
     at retrofit.CallbackRunnable$1.run(CallbackRunnable.java:45) 
     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112) 
     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587) 
     at java.lang.Thread.run(Thread.java:818) 
Caused by: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: feed.id (code 1555)
     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
     at android.

Answer 1

My understanding is then that, when calling createOrUpdate(), the underlying code should be calling "insert" if the Database doesn't contain the row and "update" if the row exists.

This is true however the operation is not (and cannot be) atomic because multiple database calls are being made to accomplish it. There is no locking that is done in the DAO method – although maybe there should be.

If we look at the BaseDaoImpl.createOrUpdate(...) code you can see that it does:

1. Extract the ID from the entity in question.
2. Looks it up in the database to see if it exists.
3. Calls update(...) if it exists otherwise create().

Because there are multiple calls to the database going on, then there are race conditions if multiple threads are executing this method at the same time.

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)

Right, if you are using this DAO method with threads (and createIfNotExists(...) which also does 2 database calls) then I would do so within a synchronized block:

synchronized (dao) {
    status = dao.createOrUpdate(...);
}