java - 使用 Ormlite createOrUpdate() 方法获取 "UNIQUE constraint failed"异常

Question

我的应用程序中有多个调用通过调用 .createOrUpdate()

更新数据库中的表

documentation suggests从这个电话:

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.

我的理解是，当调用 createOrUpdate() 时，如果数据库不包含该行，则底层代码应调用“插入”，如果该行存在，则应调用“更新”。

似乎正在发生的事情是，当导致异常的行已经存在时，底层代码正在调用“插入”。

有人知道为什么会发生这种情况或如何避免这种情况吗？

异常(exception)情况如下:

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.

这是对的，但是该操作不是(也不可能)是原子的，因为要进行多个数据库调用才能完成它。没有在 DAO 方法中完成的锁定——尽管也许应该有。

如果我们看一下 BaseDaoImpl.createOrUpdate(...) code你可以看到它确实如此:

1. 从相关实体中提取 ID。
2. 在数据库中查找它是否存在。
3. 调用 update(...)，否则 create()。

因为有多个对数据库的调用正在进行，所以如果多个线程同时执行此方法，就会出现竞争条件。

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

是的，如果您将此 DAO 方法与线程一起使用(以及 createIfNotExists(...) 也执行 2 个数据库调用)，那么我会在 synchronized 阻止:

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