使用INSERT OR IGNORE时SQLite的changes()函数返回值异常排查

表结构定义 #

创建的UniqueFile表结构如下：

CREATE TABLE UniqueFile (
    filePath TEXT NOT NULL UNIQUE,
    created INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
)

插入判断实现代码 #

通过INSERT OR IGNORE结合changes()判断文件是否首次插入的实现：

fun insertFilePath(filePath : String) : Boolean {
    val db = this.writableDatabase
    // INSERT OR IGNORE会忽略所有错误（不止唯一约束冲突），更好的替代是INSERT ... ON CONFLICT DO NOTHING，但需要SQLite 3.24.0+
    db.execSQL("INSERT OR IGNORE INTO UniqueFile (filePath) VALUES(?)", arrayOf(filePath))

    val cursor = db.rawQuery("select changes()", null)
    cursor.moveToFirst()
    val changes = cursor.getInt(0)
    cursor.close()

    if (changes > 0) {
        LogHelper.log(this, "插入了唯一文件路径: $filePath, 变更行数: $changes")
        return true;
    } else {
        return false;
    }
}

异常现象 #

有时明明文件条目已存在，changes()却返回1，日志显示同一文件路径被两次记录为插入，但created字段仅保留首次插入的时间，说明第二次并未实际插入数据。当前没有其他读写该表的函数，但存在多线程访问场景，且尝试用单例模式保证单一数据库连接。

单例实现代码 #

当前的DatabaseHandler单例代码：

class DatabaseHandler private constructor(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    private var instance: DatabaseHandler? = null
    fun getInstance(context: Context) : DatabaseHandler {
        if (instance == null) {
            instance = DatabaseHandler(context)             
        }

        return instance as DatabaseHandler
    }
}

异常原因分析 #

1. 单例实现完全错误，无法保证单一连接

当前的单例写法存在根本性问题：

• instance是类的成员变量而非静态变量，每个实例都会拥有独立的instance引用；
• getInstance是成员函数，但构造函数是private，外部无法创建实例来调用该方法，实际运行中必然存在其他非法创建实例的逻辑，导致多个DatabaseHandler实例被初始化，对应多个独立的SQLite连接。

2. 多线程下未同步，changes()结果被干扰

即使修复了单例实现，当前的insertFilePath方法也没有同步机制：

• 当多个线程同时调用该方法时，线程A执行execSQL后，线程B可能在A调用select changes()之前执行了另一个修改操作，导致A获取到的changes()结果是B的操作行数，而非自己的INSERT OR IGNORE的实际影响行数；
• SQLite的changes()函数仅返回当前连接最近一次SQL操作的修改行数，多线程并发操作同一连接时，这个值会被频繁覆盖，导致判断逻辑失效。

3. INSERT OR IGNORE的潜在副作用（非主因）

INSERT OR IGNORE会忽略所有错误（不止唯一约束冲突），可能隐藏其他异常，但这不是本次changes()误报的直接原因。

解决方案建议 #

1. 修复单例实现，确保单一连接
   改为线程安全的静态单例实现：

   class DatabaseHandler private constructor(context: Context) : SQLiteOpenHelper(context.applicationContext, DATABASE_NAME, null, DATABASE_VERSION) {
       companion object {
           @Volatile
           private var instance: DatabaseHandler? = null
   
           fun getInstance(context: Context): DatabaseHandler {
               return instance ?: synchronized(this) {
                   instance ?: DatabaseHandler(context).also { instance = it }
               }
           }
       }
   
       // ... 其他方法
   }
   

2. 同步插入方法，保证操作原子性
   对insertFilePath添加同步锁，确保execSQL和select changes()的操作是原子的：

   @Synchronized
   fun insertFilePath(filePath: String): Boolean {
       val db = this.writableDatabase
       db.execSQL("INSERT OR IGNORE INTO UniqueFile (filePath) VALUES(?)", arrayOf(filePath))
   
       db.rawQuery("select changes()", null).use { cursor ->
           cursor.moveToFirst()
           val changes = cursor.getInt(0)
           return if (changes > 0) {
               LogHelper.log(this, "插入了唯一文件路径: $filePath, 变更行数: $changes")
               true
           } else {
               false
           }
       }
   }
   

3. 兼容低版本SQLite的替代方案
   如果要兼容SQLite 3.24.0以下的设备，可以先查询再插入（需同步避免竞态）：

   @Synchronized
   fun insertFilePath(filePath: String): Boolean {
       val db = this.writableDatabase
       // 先检查记录是否存在
       db.rawQuery("SELECT 1 FROM UniqueFile WHERE filePath = ?", arrayOf(filePath)).use { cursor ->
           if (cursor.moveToFirst()) {
               return false
           }
       }
       // 不存在则插入
       db.execSQL("INSERT INTO UniqueFile (filePath) VALUES(?)", arrayOf(filePath))
       LogHelper.log(this, "插入了唯一文件路径: $filePath")
       return true
   }
   

内容的提问来源于stack exchange，提问作者Tom Raganowicz