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

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

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）：

  1. 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

然后：

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

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

SQLite3准备语句：

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

iOS调度队列：

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

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):

  1. 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:

  1. BEGIN;
  2. SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1;
  3. // Read the 'id' in code
  4. UPDATE Transactions SET code=1 WHERE code=0 AND id='id';
  5. 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:

  1. // Retrieve the Next Device Transaction - Returns Blank String if No Records Remain
  2. class func getNextDeviceTransaction() throws -> String {
  4.     // Database Statement and Value Buffers
  5.     var stmt: OpaquePointer?
  6.     var id = -1
  7.     var url = ""
  8.     
  9.     // Prepare the Begin
  10.     if sqlite3_prepare_v2( db, "BEGIN", -1, &stmt, nil ) != SQLITE_OK {
  11.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  12.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  13.         throw NSError( domain: "com.", code: 921, userInfo: [ "Error": "Error Preparing Begin Transaction: \( errorMessage )" ] )
  14.     }
  15.     
  16.     // Begin the Transaction
  17.     if sqlite3_step( stmt ) != SQLITE_DONE {
  18.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  19.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  20.         throw NSError( domain: "com.", code: 922, userInfo: [ "Error": "Database Transaction Malfunction: \( errorMessage )" ] )
  21.     }
  22.     
  23.     // Select Query
  24.     var queryString = "SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1"
  25.     
  26.     // Prepare the Query
  27.     if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
  28.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  29.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  30.         throw NSError( domain: "com.", code: 923, userInfo: [ "Error": "Error Preparing Select: \( errorMessage )" ] )
  31.     }
  32.     
  33.     // Traverse Through Records
  34.     if sqlite3_step( stmt ) == SQLITE_ROW {
  35.         
  36.         // Retrieve Value and Return
  37.         id = Int( sqlite3_column_int( stmt, 0 ) )
  38.         url = String( cString: sqlite3_column_text( stmt, 1 ) )
  39.     }
  40.     
  41.     // Evaluate if No Records Found
  42.     if id == -1 || url == "" {
  43.         
  44.         // Rollback
  45.         sqlite3_prepare_v2( db, "ROLLBACK", -1, &stmt, nil )
  46.         sqlite3_step( stmt )
  47.         
  48.         // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  49.         sqlite3_finalize( stmt )
  50.         
  51.         // No Records Exist
  52.         return ""
  53.     }
  54.     
  55.     // Select Query
  56.     queryString = "UPDATE Transactions SET code=1 WHERE code=0 AND id=\( id )"
  57.     
  58.     // Prepare the Update Query
  59.     if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
  60.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  61.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  62.         throw NSError( domain: "com.", code: 924, userInfo: [ "Error": "Error Preparing Update: \( errorMessage )" ] )
  63.     }
  64.     
  65.     // Execute the Update
  66.     if sqlite3_step( stmt ) != SQLITE_DONE {
  67.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  68.         
  69.         // Rollback
  70.         sqlite3_prepare( db, "ROLLBACK", -1, &stmt, nil )
  71.         sqlite3_step( stmt )
  72.         
  73.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  74.         throw NSError( domain: "com.", code: 925, userInfo: [ "Error": "Transaction Update Malfunction: \( errorMessage )" ] )
  75.     }
  76.     
  77.     // Prepare the Commit
  78.     if sqlite3_prepare_v2( db, "COMMIT", -1, &stmt, nil ) != SQLITE_OK {
  79.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  80.         
  81.         // Rollback
  82.         sqlite3_prepare_v2( db, "ROLLBACK", -1, &stmt, nil )
  83.         sqlite3_step( stmt )
  84.         
  85.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  86.         throw NSError( domain: "com.", code: 926, userInfo: [ "Error": "Error Preparing Commit: \( errorMessage )" ] )
  87.     }
  88.     
  89.     // Commit the Transaction
  90.     if sqlite3_step( stmt ) != SQLITE_DONE {
  91.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  92.         
  93.         // Rollback
  94.         sqlite3_prepare_v2( db, "ROLLBACK", -1, &stmt, nil )
  95.         sqlite3_step( stmt )
  96.         
  97.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  98.         throw NSError( domain: "com.", code: 927, userInfo: [ "Error": "Database Commit Transaction Malfunction: \( errorMessage )" ] )
  99.     }
  100.     
  101.     // Confirm a Single Row Touched
  102.     if sqlite3_changes( db ) != 1 {
  103.         
  104.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  105.         
  106.         sqlite3_finalize( stmt )            // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  107.         throw NSError( domain: "com.", code: ALLOWABLE_DATABASE_COLLISION_ERROR, userInfo: [ "Error": "Database Update Count Malfunction or Simple Transaction Collision: \( errorMessage )" ] )                 // 928
  108.     }
  110.     // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  111.     sqlite3_finalize( stmt )
  112.     
  113.     // Return Next Available URL
  114.     return url
  115. }

iOS dispatch queue:

  1. // Retrieve the Next Device Transaction - Returns Blank String if No Records Remain - MUST BE CALLED FROM SYNCHRONIZED DISPATCH QUEUE
  2. class func getNextDeviceTransaction() throws -> String {
  4.     // Database Statement and Value Buffers
  5.     var stmt: OpaquePointer?
  6.     var id: Int = -1
  7.     var url: String = ""
  8.     
  9.     // Select Query
  10.     var queryString = "SELECT id,url FROM Transactions WHERE code=0 ORDER BY id ASC LIMIT 1"
  11.     
  12.     // Prepare the Query
  13.     if sqlite3_prepare_v2( db, queryString, -1, &stmt, nil ) != SQLITE_OK {
  14.         
  15.         // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  16.         sqlite3_finalize( stmt )
  17.         
  18.         let errorMessage = String( cString: sqlite3_errmsg( db )! )
  19.         print( "Error Preparing Select: \( errorMessage )" )
  20.         throw NSError( domain: "com.", code: 921, userInfo: [ "Error": "Error Querying Device Transactions: \( errorMessage )" ] )
  21.     }
  22.     
  23.     // Traverse Through the Single Record
  24.     if sqlite3_step( stmt ) == SQLITE_ROW {
  25.         
  26.         // Retrieve IDs and URLs
  27.         id = Int( sqlite3_column_int( stmt, 0 ) )
  28.         url = String( cString: sqlite3_column_text( stmt, 1 ) )
  29.         
  30.         // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  31.         sqlite3_finalize( stmt )
  32.         
  33.         // Evaluate IDs and URLs
  34.         if id > 0 && url != "" {
  35.             
  36.             // Update Query to Synchronously Set the Records Status Code
  37.             queryString = "UPDATE Transactions SET code=1 WHERE code=0 AND id=\( id )"
  38.             
  39.             // Prepare the Update Query
  40.             if sqlite3_exec( db, queryString, nil, nil, nil ) != SQLITE_OK {
  41.                 
  42.                 let errorMessage = String( cString: sqlite3_errmsg( db )! )
  43.                 print( "Error Preparing Update: \( errorMessage )" )
  44.                 throw NSError( domain: "com.", code: 922, userInfo: [ "Error": "Error Setting Transaction Status Code: \( errorMessage )" ] )
  45.             }
  46.             
  47.             // Confirm a Single Row Touched
  48.             if sqlite3_changes( db ) == 1 {
  49.                 
  50.                 // Success - Return the Next Record's URL
  51.                 return url
  52.             }
  53.             else {
  55.                 let errorMessage = String( cString: sqlite3_errmsg( db )! )
  56.                 print( "Device Transaction Not Captured: \( errorMessage )" )
  57.                 throw NSError( domain: "com.", code: 922, userInfo: [ "Error": "Device Transaction Not Captured: \( errorMessage )" ] )
  58.             }
  59.         }
  60.     }
  61.     
  62.     // Finalize the Prepared Statement to Avoid Memory Leaks - https://www.sqlite.org/malloc.html
  63.     sqlite3_finalize( stmt )
  64.     
  65.     // No Records Exist
  66.     return ""
  67. }

答案1

得分: 0

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

  1. UPDATE transactions
  2. SET code = 1
  3. WHERE id = (SELECT MIN(id) FROM transactions WHERE code = 0)
  4. 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:

  1. UPDATE transactions
  2. SET code = 1
  3. WHERE id = (SELECT MIN(id) FROM transactions WHERE code = 0)
  4. 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:

确定