Ios swift – Alternatives to embed an UPDATE within a SELECT statement for status code processing?

I have searched through this site and the general consensus is that you cannot embed an UPDATE within a SELECT statement — UPDATEs do not return data and SELECTs do not modify data. That being said, I have found 2 options to fix my problem but neither is great: prepared statement transactions or synchronizing the calls to the database in my code.

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

I have a simple table in SQLite3 (iOS 14.0, Swift, https://github.com/stephencelis/SQLite.swift package):

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 find a way to do this entirely in SQL, if possible. I am using SQLite as a lightweight DBMS and I know it has its limitations, like no SELECT… FOR UPDATE. I also know there are tons of threads on the Internet where someone suggests that SQLite is leaking memory and then a half dozen people say that is impossible. I don’t want to go down that route, but I have found in my case that running this on iOS in a synchronous DispatchQueue without the prepared statement (just assuming that only one thread is running) will run for 96 hours straight with only 82MB of RAM used, but using the prepared statement like I described above results in the app crashing with 1.81GB of RAM usage in 56 hours (the code is at the bottom of this question – I keep a database connection open and I create an OpaquePointer for each Statement in each function call, prepare with the _v2(), and finalize the Statement with calls to sqlite3_finalize()).

So, to that end I was hoping some SQL guru could help me out with another option since I’d like to not assume synchronization in my app. Thanks!

SQLite3 Prepared Statement Code:

    // 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 DispatchQueue Synchronized Code:

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