UPDATE语句内用于状态码处理的SELECT语句的替代方法

huangapple
go评论78阅读模式
英文:

Alternatives to UPDATE within a SELECT statement for status code processing

问题

在SQL语句中，您不能在SELECT语句内嵌套UPDATE语句。UPDATE语句不返回数据，而SELECT语句不修改数据。我找到了2个选项，但都不是很好：准备好的语句事务或在我的代码中同步调用数据库。

我正在寻找用于处理状态代码的替代方法：我想找到具有最低id且code=0的记录，然后以原子方式和线程安全地保留该记录的id并将code设置为1。我希望有一种方法来更新单行并保留rowid。我有多个线程尝试获取下一个值，并希望防止2个线程处理相同的记录。似乎SQLite将提供最后插入但未更新的rowid。我不确定它是否线程安全，也不确定sqlite3_changes()是否线程安全。

我的SQLite3表（iOS 14.0，Swift）：

CREATE TABLE IF NOT EXISTS Transactions (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, url TEXT NOT NULL, code INTEGER NOT NULL)
id url code
1 https:/x.com/?a=1 0
2 https:/x.com/?a=2 0
3 https:/x.com/?a=3 0

然后：

BEGIN;
SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1;
// 读取 'id' 的值
UPDATE Transactions SET code=1 WHERE code=0 AND id='id';
COMMIT;

我希望完全在SQL中执行此操作。我知道SQLite有一些限制，比如没有SELECT... FOR UPDATE。我发现在iOS上在同步调度队列中运行这个操作（假设只有一个线程），不使用准备好的语句会运行96小时，只使用82MB的RAM，但是使用准备好的语句会导致应用程序在56小时内崩溃，RAM使用量为1.81GB（请参阅本问题底部的代码 - 我保持数据库连接打开，并为每个函数调用中的每个语句创建一个OpaquePointer，使用_v2()准备语句，并使用sqlite3_finalize()完成语句）。请帮助我找到另一种选择，因为我不想假设同步。

SQLite3准备语句：

// 检索下一个设备交易 - 如果没有记录则返回空字符串
class func getNextDeviceTransaction() throws -> String {
    // 代码已省略
}

iOS调度队列：

// 从同步调度队列调用 - 检索下一个设备交易 - 如果没有记录则返回空字符串
class func getNextDeviceTransaction() throws -> String {
    // 代码已省略
}
英文:

You cannot embed an UPDATE within a SELECT statement. UPDATE's do not return data and SELECT's do not modify data. I found 2 options but neither is great: prepared statement transactions or synchronizing the calls to the database in my code.

I am looking for alternatives for status code processing: I want to find the record with the lowest id that has code=0, then atomically and thread safely retain that record's id and set code to 1. I want a way to update a single row and retain the rowid. I have multiple threads attempting to get the next value and want to safeguard against 2 threads processing the same record. It seems SQLite will give rowid of the last row inserted but not updated. I'm not sure if it's thread safe or not. I'm also not positive that sqlite3_changes() is thread safe.

My table in SQLite3 (iOS 14.0, Swift):

CREATE TABLE IF NOT EXISTS Transactions (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, url TEXT NOT NULL, code INTEGER NOT NULL)
id url code
1 https:/x.com/?a=1 0
2 https:/x.com/?a=2 0
3 https:/x.com/?a=3 0

I then:

BEGIN;
SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1;
// Read the 'id' in code
UPDATE Transactions SET code=1 WHERE code=0 AND id='id';
COMMIT;

I would like to do this entirely in SQL. I know SQLite has limitations like no SELECT... FOR UPDATE. I found that running this on iOS in a synchronous dispatch queue without prepared statement (assuming only one thread) will run for 96 hours with only 82MB of RAM used, but the prepared statement results in the app crashing with 1.81GB of RAM usage in 56 hours (code at bottom of this question - I keep a database connection open and create an OpaquePointer for each statement in each function call, prepare with _v2() and finalize the statement with sqlite3_finalize()). Help me out with another option since I'd like to not assume synchronization.

SQLite3 prepared statement:

// Retrieve the Next Device Transaction - Returns Blank String if No Records Remain
class func getNextDeviceTransaction() throws -> String {

    // Database Statement and Value Buffers
    var stmt: OpaquePointer?
    var id = -1
    var url = ""
    
    // Prepare the Begin
    if sqlite3_prepare_v2( db, "BEGIN", -1, &stmt, nil ) != SQLITE_OK {
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: 921, userInfo: [ "Error": "Error Preparing Begin Transaction: \( errorMessage )" ] )
    }
    
    // Begin the Transaction
    if sqlite3_step( stmt ) != SQLITE_DONE {
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: 922, userInfo: [ "Error": "Database Transaction Malfunction: \( errorMessage )" ] )
    }
    
    // Select Query
    var queryString = "SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1"
    
    // Prepare the Query
    if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: 923, userInfo: [ "Error": "Error Preparing Select: \( errorMessage )" ] )
    }
    
    // Traverse Through Records
    if sqlite3_step( stmt ) == SQLITE_ROW {
        
        // Retrieve Value and Return
        id = Int( sqlite3_column_int( stmt, 0 ) )
        url = String( cString: sqlite3_column_text( stmt, 1 ) )
    }
    
    // Evaluate if No Records Found
    if id == -1 || url == "" {
        
        // Rollback
        sqlite3_prepare_v2( db, "ROLLBACK", -1, &stmt, nil )
        sqlite3_step( stmt )
        
        // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        sqlite3_finalize( stmt )
        
        // No Records Exist
        return ""
    }
    
    // Select Query
    queryString = "UPDATE Transactions SET code=1 WHERE code=0 AND id=\( id )"
    
    // Prepare the Update Query
    if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: 924, userInfo: [ "Error": "Error Preparing Update: \( errorMessage )" ] )
    }
    
    // Execute the Update
    if sqlite3_step( stmt ) != SQLITE_DONE {
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        
        // Rollback
        sqlite3_prepare( db, "ROLLBACK", -1, &stmt, nil )
        sqlite3_step( stmt )
        
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: 925, userInfo: [ "Error": "Transaction Update Malfunction: \( errorMessage )" ] )
    }
    
    // Prepare the Commit
    if sqlite3_prepare_v2( db, "COMMIT", -1, &stmt, nil ) != SQLITE_OK {
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        
        // Rollback
        sqlite3_prepare_v2( db, "ROLLBACK", -1, &stmt, nil )
        sqlite3_step( stmt )
        
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: 926, userInfo: [ "Error": "Error Preparing Commit: \( errorMessage )" ] )
    }
    
    // Commit the Transaction
    if sqlite3_step( stmt ) != SQLITE_DONE {
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        
        // Rollback
        sqlite3_prepare_v2( db, "ROLLBACK", -1, &stmt, nil )
        sqlite3_step( stmt )
        
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: 927, userInfo: [ "Error": "Database Commit Transaction Malfunction: \( errorMessage )" ] )
    }
    
    // Confirm a Single Row Touched
    if sqlite3_changes( db ) != 1 {
        
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        
        sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        throw NSError( domain: "com.", code: ALLOWABLE_DATABASE_COLLISION_ERROR, userInfo: [ "Error": "Database Update Count Malfunction or Simple Transaction Collision: \( errorMessage )" ] )                 // 928
    }

    // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
    sqlite3_finalize( stmt )
    
    // Return Next Available URL
    return url
}

iOS dispatch queue:

// Retrieve the Next Device Transaction - Returns Blank String if No Records Remain - MUST BE CALLED FROM SYNCHRONIZED DISPATCH QUEUE
class func getNextDeviceTransaction() throws -> String {

    // Database Statement and Value Buffers
    var stmt: OpaquePointer?
    var id: Int = -1
    var url: String = ""
    
    // Select Query
    var queryString = "SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1"
    
    // Prepare the Query
    if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
        
        // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        sqlite3_finalize( stmt )
        
        let errorMessage = String( cString: sqlite3_errmsg( db )! )
        print( "Error Preparing Select: \( errorMessage )" )
        throw NSError( domain: "com.", code: 921, userInfo: [ "Error": "Error Querying Device Transactions: \( errorMessage )" ] )
    }
    
    // Traverse Through the Single Record
    if sqlite3_step( stmt ) == SQLITE_ROW {
        
        // Retrieve IDs and URLs
        id = Int( sqlite3_column_int( stmt, 0 ) )
        url = String( cString: sqlite3_column_text( stmt, 1 ) )
        
        // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
        sqlite3_finalize( stmt )
        
        // Evaluate IDs and URLs
        if id > 0 && url != "" {
            
            // Update Query to Synchronously Set the Records Status Code
            queryString = "UPDATE Transactions SET code=1 WHERE code=0 AND id=\( id )"
            
            // Prepare the Update Query
            if sqlite3_exec( db, queryString, nil, nil, nil ) != SQLITE_OK {
                
                let errorMessage = String( cString: sqlite3_errmsg( db )! )
                print( "Error Preparing Update: \( errorMessage )" )
                throw NSError( domain: "com.", code: 922, userInfo: [ "Error": "Error Setting Transaction Status Code: \( errorMessage )" ] )
            }
            
            // Confirm a Single Row Touched
            if sqlite3_changes( db ) == 1 {
                
                // Success - Return the Next Record's URL
                return url
            }
            else {

                let errorMessage = String( cString: sqlite3_errmsg( db )! )
                print( "Device Transaction Not Captured: \( errorMessage )" )
                throw NSError( domain: "com.", code: 922, userInfo: [ "Error": "Device Transaction Not Captured: \( errorMessage )" ] )
            }
        }
    }
    
    // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
    sqlite3_finalize( stmt )
    
    // No Records Exist
    return ""
}

答案1

得分: 0

自SQLite版本3.35.0开始，支持RETURNING子句：

UPDATE transactions
SET code = 1
WHERE id = (SELECT MIN(id) FROM transactions WHERE code = 0)
RETURNING id;

请参阅演示。

英文:

> Basically I want a way to update a single row and retain the rowID of
> what I updated

Since version 3.35.0 SQLite supports the RETURNING clause:

UPDATE transactions
SET code = 1
WHERE id = (SELECT MIN(id) FROM transactions WHERE code = 0)
RETURNING id;

See the demo.<br/>

huangapple
  • 本文由 发表于 2023年7月11日 06:23:18
  • 转载请务必保留本文链接：https://go.coder-hub.com/76657692.html
  • ios
  • sql
  • sql-returning
  • sqlite
  • swift
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定