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

bakd9h0s  于 2023-08-06  发布在  SQLite
关注(0)|答案(1)|浏览(115)

不能在SELECT语句中嵌入UPDATE。UPDATE不返回数据，SELECT不修改数据。我有两个选择，但都不是很好：准备好的语句事务或在代码中同步对数据库的调用。
我正在寻找状态代码处理的替代方案：我想找到具有最低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)

字符串
| url|代码| code |
| --|--| ------------ |
| https：//www.example.com| 0x.com/?a=1个| 0 |
| https：//www.example.com| 0x.com/?a=2个| 0 |
| https：//www.example.com| 0x.com/?a=3个| 0 |
然后我：

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;

型
我想完全用SQL来做这件事。我知道SQLite有一些限制，比如没有SELECT... FOR UPDATE。我发现，在iOS上，在没有prepared语句的同步调度队列中运行此语句（假设只有一个线程）将运行96小时，仅使用82 MB的RAM，但prepared语句导致应用程序在56小时内崩溃，使用1.81GB的RAM（代码在这个问题的底部-我保持数据库连接打开，并为每个函数调用中的每个语句创建一个OpaquePointer，用_v2()准备并用sqlite3_finalize()完成语句）。请帮我解决另一个问题，因为我不想假设同步。
SQLite3准备语句：

// 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调度队列：

// 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 ""
}

型

sqlite

1条答案

按热度按时间
ffscu2ro

ffscu2ro1#

基本上，我希望有一种方法可以更新单个行，并保留我更新的行的rowID
自版本3.35.0起，SQLite支持RETURNING子句：

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

字符串
参见demo。

赞(0）分享  回复(0）举报  2023-08-06
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com