Alternatives to UPDATE within a SELECT statement for status code processing

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)

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

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.