I’ve searched by means of this web site and the overall consensus is that you simply can’t embed an UPDATE inside a SELECT assertion — UPDATEs don’t return information and SELECTs don’t modify information. That being mentioned, I’ve discovered 2 choices to repair my downside however neither is nice: ready assertion transactions or synchronizing the calls to the database in my code.
I’m on the lookout for alternate options for the easy case of standing code processing: I need to discover the report with the bottom ID quantity that has a code worth=0. I then need to atomically / thread safely retain that report’s ID quantity and set the code worth=1. Principally I desire a strategy to replace a single row and retain the rowID of what I up to date. I’ve a number of threads making an attempt to get the subsequent worth and I need to safeguard in opposition to 2 threads processing the identical report. It appears SQLite provides you with the rowID of the final row INSERTed however not UPDATEd, however I am undecided if it is thread secure or not. I am additionally not optimistic that sqlite3_changes() is thread secure.
I’ve a easy desk in SQLite3 (iOS 14.0, Swift, https://github.com/stephencelis/SQLite.swift package deal):
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;
// Learn the 'id' in code
UPDATE Transactions SET code=1 WHERE code=0 AND id='id';
COMMIT;
I want to discover a method to do that fully in SQL, if attainable. I’m utilizing SQLite as a light-weight DBMS and I do know it has its limitations, like no SELECT… FOR UPDATE. I additionally know there are tons of threads on the Web the place somebody means that SQLite is leaking reminiscence after which a half dozen individuals say that’s inconceivable. I do not need to go down that route, however I’ve present in my case that operating this on iOS in a synchronous DispatchQueue with out the ready assertion (simply assuming that just one thread is operating) will run for 96 hours straight with solely 82MB of RAM used, however utilizing the ready assertion like I described above ends in the app crashing with 1.81GB of RAM utilization in 56 hours (the code is on the backside of this query – I maintain a database connection open and I create an OpaquePointer for every Assertion in every operate name, put together with the _v2(), and finalize the Assertion with calls to sqlite3_finalize()).
So, to that finish I hoped some SQL guru may assist me out with another choice since I would prefer to not assume synchronization in my app. Thanks!
SQLite3 Ready Assertion Code:
// Retrieve the Subsequent Machine Transaction - Returns Clean String if No Information Stay
class func getNextDeviceTransaction() throws -> String {
// Database Assertion and Worth Buffers
var stmt: OpaquePointer?
var id = -1
var url = ""
// Put together the Start
if sqlite3_prepare_v2( db, "BEGIN", -1, &stmt, nil ) != SQLITE_OK {
let errorMessage = String( cString: sqlite3_errmsg( db )! )
sqlite3_finalize( stmt ) // Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "com.", code: 921, userInfo: [ "Error": "Error Preparing Begin Transaction: ( errorMessage )" ] )
}
// Start the Transaction
if sqlite3_step( stmt ) != SQLITE_DONE {
let errorMessage = String( cString: sqlite3_errmsg( db )! )
sqlite3_finalize( stmt ) // Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "com.", code: 922, userInfo: [ "Error": "Database Transaction Malfunction: ( errorMessage )" ] )
}
// Choose Question
var queryString = "SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1"
// Put together the Question
if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
let errorMessage = String( cString: sqlite3_errmsg( db )! )
sqlite3_finalize( stmt ) // Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "com.", code: 923, userInfo: [ "Error": "Error Preparing Select: ( errorMessage )" ] )
}
// Traverse Via Information
if sqlite3_step( stmt ) == SQLITE_ROW {
// Retrieve Worth and Return
id = Int( sqlite3_column_int( stmt, 0 ) )
url = String( cString: sqlite3_column_text( stmt, 1 ) )
}
// Consider if No Information Discovered
if id == -1 || url == "" {
// Rollback
sqlite3_prepare_v2( db, "ROLLBACK", -1, &stmt, nil )
sqlite3_step( stmt )
// Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
sqlite3_finalize( stmt )
// No Information Exist
return ""
}
// Choose Question
queryString = "UPDATE Transactions SET code=1 WHERE code=0 AND id=( id )"
// Put together the Replace Question
if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
let errorMessage = String( cString: sqlite3_errmsg( db )! )
sqlite3_finalize( stmt ) // Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "com.", code: 924, userInfo: [ "Error": "Error Preparing Update: ( errorMessage )" ] )
}
// Execute the Replace
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 Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "com.", code: 925, userInfo: [ "Error": "Transaction Update Malfunction: ( errorMessage )" ] )
}
// Put together 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 Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "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 Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "com.", code: 927, userInfo: [ "Error": "Database Commit Transaction Malfunction: ( errorMessage )" ] )
}
// Affirm a Single Row Touched
if sqlite3_changes( db ) != 1 {
let errorMessage = String( cString: sqlite3_errmsg( db )! )
sqlite3_finalize( stmt ) // Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
throw NSError( area: "com.", code: ALLOWABLE_DATABASE_COLLISION_ERROR, userInfo: [ "Error": "Database Update Count Malfunction or Simple Transaction Collision: ( errorMessage )" ] ) // 928
}
// Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
sqlite3_finalize( stmt )
// Return Subsequent Out there URL
return url
}
iOS DispatchQueue Synchronized Code:
// Retrieve the Subsequent Machine Transaction - Returns Clean String if No Information Stay - MUST BE CALLED FROM SYNCHRONIZED DISPATCH QUEUE
class func getNextDeviceTransaction() throws -> String {
// Database Assertion and Worth Buffers
var stmt: OpaquePointer?
var id: Int = -1
var url: String = ""
// Choose Question
var queryString = "SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1"
// Put together the Question
if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
// Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
sqlite3_finalize( stmt )
let errorMessage = String( cString: sqlite3_errmsg( db )! )
print( "Error Getting ready Choose: ( errorMessage )" )
throw NSError( area: "com.", code: 921, userInfo: [ "Error": "Error Querying Device Transactions: ( errorMessage )" ] )
}
// Traverse Via the Single Report
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 Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
sqlite3_finalize( stmt )
// Consider IDs and URLs
if id > 0 && url != "" {
// Replace Question to Synchronously Set the Information Standing Code
queryString = "UPDATE Transactions SET code=1 WHERE code=0 AND id=( id )"
// Put together the Replace Question
if sqlite3_exec( db, queryString, nil, nil, nil ) != SQLITE_OK {
let errorMessage = String( cString: sqlite3_errmsg( db )! )
print( "Error Getting ready Replace: ( errorMessage )" )
throw NSError( area: "com.", code: 922, userInfo: [ "Error": "Error Setting Transaction Status Code: ( errorMessage )" ] )
}
// Affirm a Single Row Touched
if sqlite3_changes( db ) == 1 {
// Success - Return the Subsequent Report's URL
return url
}
else {
let errorMessage = String( cString: sqlite3_errmsg( db )! )
print( "Machine Transaction Not Captured: ( errorMessage )" )
throw NSError( area: "com.", code: 922, userInfo: [ "Error": "Device Transaction Not Captured: ( errorMessage )" ] )
}
}
}
// Finalize the Ready Assertion to Keep away from Reminiscence Leaks - https://www.sqlite.org/malloc.html
sqlite3_finalize( stmt )
// No Information Exist
return ""
}
