iOS Question SQLite : How to set WAL mode and avoid database lock

[luc-dev]

Hi,

I'm trying to use SQLite WAL mode to avoid locks while running several nested SQL statements like "Insert into MyDb from (select from MyattachedDb where...)". These queries need to be managed by a global transaction because several related tables are updated at the same time.

I didn't managed to avoid locks so far, except when I don't use transactions...

• Is anyone using WAL mode on iOS and willing to share a working example?
• Is it possible to set a manual transaction with iSQL like "BEGIN IMMEDIATE;"? In that case, how iSQL handles it? Does it also try to begin its own transaction?
  
• How can I track or know a transaction was already set "automatically" by the library iSQL?
  
• What kind of transaction is used by iSQL (DEFFERED, IMMEDIATE, EXCLUSIVE...)?

Here is an example of code to reproduce a lock

Sub SQLite_LockTest  
    Private MyDb1 As SQL
    Private MyDb2 As SQL
    MyDb1.Initialize(File.DirLibrary, "MyDb1", True)
    MyDb2.Initialize(File.DirLibrary, "MyDb2", True)
   
    Try
        ' This gives: ExecNonQuery error: not an error or cannot set WAL within a transaction
        'MyDb1.ExecNonQuery("PRAGMA journal_mode=WAL")
        'MyDb2.ExecNonQuery("PRAGMA journal_mode=WAL")
       
        ' This gives: ExecNonQuery error: not an error
        Dim no As NativeObject = MyDb1
        no.RunMethod("ExecNonQueryInsideTransaction::", Array("PRAGMA journal_mode=WAL", Null))
        Dim no As NativeObject = MyDb2
        no.RunMethod("ExecNonQueryInsideTransaction::", Array("PRAGMA journal_mode=WAL", Null))
       
    Catch
        Log("Unable to set WAL mode")
    End Try  
   
    MyDb1.ExecNonQuery("CREATE TABLE IF NOT EXISTS [MyTable] ([Id] INTEGER PRIMARY KEY, [Name] TEXT(50));")
    MyDb2.ExecNonQuery("CREATE TABLE IF NOT EXISTS [MyTable] ([Id] INTEGER PRIMARY KEY, [Name] TEXT(50));")
   
    Dim    SQLStmt = "ATTACH DATABASE '" & File.Combine(File.DirLibrary, "MyDb2") & "' AS [MyDb2];" As String
    Dim no As NativeObject = MyDb1
    no.RunMethod("ExecNonQueryInsideTransaction::", Array(SQLStmt, Null))
       
    Try
        ' Transaction # 1 -> Writing to both tables (MyDb1.MyTable and MyDb2.MyTable)
        Dim MyDb1_InTrans As Boolean = False
        MyDb1.BeginTransaction
        MyDb1_InTrans = True

        MyDb1.ExecNonQuery("DELETE FROM [MAIN].[MyTable];")

        For i = 1 To 10
            MyDb1.ExecNonQuery2("INSERT INTO [MAIN].[MyTable] ([Name]) VALUES (?);", Array("MyDb1 : " & i))
            MyDb1.ExecNonQuery2("INSERT INTO [MyDb2].[MyTable] ([Name]) VALUES (?);", Array("MyDb2 (Attached Insert) : " & i))
        Next      
       
        ' Transaction # 2 -> Also writing to MyDb2.MyTable: Intended to lock the Database MyDb2
        Dim MyDb2_InTrans As Boolean = False
        Try
            MyDb2.BeginTransaction
            MyDb2_InTrans = True
            ' The lock error is "normal" since we write to the db. It should also be the case in WAL mode which
            ' allows multiple readers but only one writer at the same time.
            ' Note : just calling BeginTransaction is enough to get the lock error
           
            'For i = 1 To 10
            '    MyDb2.ExecNonQuery2("INSERT INTO [MyTable] ([Name]) VALUES (?);", Array("MyDb2 (Direct Insert) : " & i))
            'Next
            MyDb2.TransactionSuccessful      
        Catch
            Log("MyDb2 is locked")
            Log(LastException)
            If MyDb2_InTrans Then
                MyDb2.Rollback          
            End If  
        End Try          
       
        MyDb1.TransactionSuccessful  
    Catch
        If (MyDb1_InTrans) Then
            MyDb1.Rollback
        End If  
    End Try  
   
    ' Read the inserted records from MyDb1 and MyDb2
    Dim Cur As ResultSet
    Cur = MyDb1.ExecQuery("SELECT [id], [Name] FROM [MAIN].[MyTable] UNION ALL SELECT [id], [Name] FROM [MyDb2].[MyTable] ORDER BY [Id];")
    Do While Cur.NextRow
        Log("Id = " & Cur.GetString2(0) & " Name = " & Cur.GetString2(1))
    Loop
   
    Dim no As NativeObject = MyDb1
    no.RunMethod("ExecNonQueryInsideTransaction::", Array("DETACH DATABASE [MyDb2]", Null))
   
    MyDb2.Close
    MyDb1.Close          
End Sub

Thanks

 

[OliverA]

From the SQLite documentation (https://www.sqlite.org/wal.html): "However, since there is only one WAL file, there can only be one writer at a time." Your first writer is the MyDb1 object (since you now attached the physical MyDb2 file to it). Then you try to open another writer (via MyDb2 object and use of transaction) and get the lock as expected (the first writer locks out all following until done). So, WAL works as intended. Please note that the documentation also says that WAL and ATTACH may produce unintended side effects, since transactions with attached databases and the usage of WAL make the transactions atomic to each database, not the whole transaction itself (in other works, a "glitch" may leave one database updated (transaction successful), but another not (transaction was aborted)). That may not be what you are looking for (you may think that the transaction should succeed or fail across all databases, attached or not).

 

[luc-dev]

Indeed, I will need to be carefull with the transaction and the attached databases but this should'nt be a problem because only one database needs one "atomic" transaction among several update of tables.

Did you succeed setting WAL mode on iOS with iSQL?

    Try
        ' This gives: ExecNonQuery error: not an error or cannot set WAL within a transaction
        'MyDb1.ExecNonQuery("PRAGMA journal_mode=WAL")
        'MyDb2.ExecNonQuery("PRAGMA journal_mode=WAL")
      
        ' This gives: ExecNonQuery error: not an error
        Dim no As NativeObject = MyDb1
        no.RunMethod("ExecNonQueryInsideTransaction::", Array("PRAGMA journal_mode=WAL", Null))
        Dim no As NativeObject = MyDb2
        no.RunMethod("ExecNonQueryInsideTransaction::", Array("PRAGMA journal_mode=WAL", Null))
      
    Catch
        Log("Unable to set WAL mode")
    End Try

 

[OliverA]

I have not tried. Why would you need WAL? Are you building a server app on iOS?

 

[luc-dev]

No, SQLLite was not designed to build server db with many concurrency but in some case you need to allow read operations while you are updating the db, for exemple when you run read queries reguralery triggered by a timer and write statements also triggrered by one or more timers.

iSQL doesn't seem to have 'callback' to let the calling process oause when the db is busy.

Do you have some feedback with manual transactions ?

 

[OliverA]

Looks like WAL mode is enabled by default in iOS. (http://pinkstone.co.uk/how-to-remove-wal-files-in-core-data/)(https://stackoverflow.com/questions/20969996/is-it-safe-to-delete-sqlites-wal-file). Doing transactions across DB's and them being synchronized is going to be difficult on iOS as is stands (for SQLite, ATTACH would seem to be the way to go, but not with WAL mode). Trying to coordinate transactions involving several DBs with their own BEGIN/END transactions is just not going to be reliable. Even if you experience no errors in your first TransactionSuccessful call, nothing keeps the second DBs TransactionSuccessful (the COMMIT) from failing and then the second would be out of sync with the first. Then you would have to write your own rollback mechanism for the first one (and that could fail). And that's with just two DBs involved.

 

[OliverA]

As to nesting transactions, you cannot (limited by SQLite). You could try using SAVEPOINT (https://www.sqlite.org/lang_savepoint.html).

 

[luc-dev]

Yes I agree but I don't think I will be going with SAVEPOINT here

I did some more test with WAL. The way to go is:

The first time you run the app (you need a clean and fresh install), journal mode is "Delete" which is the "classic" journal mode.
Then, after running the following code, journal mode is set to "WAL" and should stay like this as WAL is "permanent" once it is set.

The strange thing is you must trap the errors thrown while changing the journal mode but the change is applied :

Try
        Dim JState As String = MyDb1.ExecQuerySingleResult("PRAGMA Journal_mode")
        Log("Journal Mode MyDb1 = " & JState)
      
        ' This gives: ExecNonQuery error: not an error but WAL is applied...
        Dim no As NativeObject = MyDb1
        no.RunMethod("ExecNonQueryInsideTransaction::", Array("PRAGMA journal_mode=WAL", Null))
      
        Dim JState As String = MyDb1.ExecQuerySingleResult("PRAGMA Journal_mode")
        Log("Journal Mode MyDb1 = " & JState)
    Catch
        Dim JState As String = MyDb1.ExecQuerySingleResult("PRAGMA Journal_mode")
        Log("Journal Mode = " & JState)
    End Try

    Try
        Dim JState As String = MyDb2.ExecQuerySingleResult("PRAGMA Journal_mode")
        Log("Journal Mode MyDb2 = " & JState)
      
        ' This gives: ExecNonQuery error: not an error
        Dim no As NativeObject = MyDb2
        no.RunMethod("ExecNonQueryInsideTransaction::", Array("PRAGMA journal_mode=WAL", Null))     
    Catch
        Dim JState As String = MyDb2.ExecQuerySingleResult("PRAGMA Journal_mode")
        Log("Journal Mode MyDb2 = " & JState)
    End Try

Here is the log:

Application_Start
Journal Mode MyDb1 = delete
Unknown error calling sqlite3_step (100: unknown error) eu
DB Query: PRAGMA journal_mode=WAL
Error occurred on line: 53 (Main)
ExecNonQuery error: not an error
Stack Trace: (
CoreFoundation <redacted> + 152
libobjc.A.dylib objc_exception_throw + 38
CoreFoundation <redacted> + 0
B4i Example -[B4ISQL ExecNonQueryInsideTransaction::] + 600
CoreFoundation <redacted> + 68
CoreFoundation <redacted> + 300
B4i Example +[B4I runDynamicMethod:method:throwErrorIfMissing:args:] + 1784
B4i Example -[B4INativeObject RunMethod::] + 158
CoreFoundation <redacted> + 68
CoreFoundation <redacted> + 300
B4i Example +[B4I runDynamicMethod:method:throwErrorIfMissing:args:] + 1784
B4i Example -[B4IShell runVoidMethod] + 204
B4i Example -[B4IShell raiseEventImpl:method:args::] + 2146
B4i Example -[B4IShellBI raiseEvent:eventarams:] + 1578
B4i Example +[B4IDebug delegate:::] + 52
B4i Example -[b4i_main _sqlite_locktest] + 528
B4i Example -[b4i_main _application_start:] + 1988
CoreFoundation <redacted> + 68
CoreFoundation <redacted> + 300
B4i Example +[B4I runDynamicMethod:method:throwErrorIfMissing:args:] + 1784
B4i Example -[B4IShell runMethod:] + 590
B4i Example -[B4IShell raiseEventImpl:method:args::] + 2238
B4i Example -[B4IShellBI raiseEvent:eventarams:] + 1578
B4i Example __33-[B4I raiseUIEvent:eventarams:]_block_invoke + 74
libdispatch.dylib <redacted> + 10
libdispatch.dylib <redacted> + 22
libdispatch.dylib _dispatch_main_queue_callback_4CF + 902
CoreFoundation <redacted> + 8
CoreFoundation <redacted> + 848
CoreFoundation CFRunLoopRunSpecific + 470
CoreFoundation CFRunLoopRunInMode + 104
GraphicsServices GSEventRunModal + 80
UIKit UIApplicationMain + 150
B4i Example main + 106
libdyld.dylib <redacted> + 2
)
Journal Mode = wal
Journal Mode MyDb2 = delete
Unknown error calling sqlite3_step (100: unknown error) eu
DB Query: PRAGMA journal_mode=WAL
Journal Mode MyDb2 = wal

Still, I have to figure out how to manage the transactions "by hand"

Do you have any idea how to access
int sqlite3_stmt_busy(sqlite3_stmt*);
via native object? It could be useful to debug database locks

 

[Erel]

ExecNonQuery will not create a new transaction if there is currently an open transaction.

As said above, SQLite doesn't support nested transactions. You can check whether there is an existing transaction with:

Dim no As NativeObject = sql
Dim inTransaction As Boolean = no.GetField("db").RunMethod("inTransaction", Null).AsBoolean

 

[luc-dev]

Great, I didn't know how to access the exposed sub-objects and methods of a native object!
This will be very helpfull.

I guess we can do the same for any of the other methods exposed.

Now, is it possible to change one of those properties? Let's say I need to change the

_maxBusyRetryTimeInterval

. How can I do that?

This doesn't work

    Dim no As NativeObject = MyDb1
    no.RunMethod("db:maxBusyRetryTimeInterval:", Array(5))

Method not found: db:maxBusyRetryTimeInterval:, target: <B4ISQL: 0x16daa360>

Regarding the locks, would it be possible to access "sqlite3_stmt_busy(sqlite3_stmt*)" via the native object to help identify the currently running SQL statement?
Another possibility would be to let the Application set/get the nonQueryStatementsList or the nonQueryStatementsList.Description with a SQL job id/info to identify the current running statement causing the lock.

Could you give a way to do this?

 

[Erel]

Not everything can be accessed.

You can access setMaxBusyRetryTimeInterval with:

no.GetField("db").RunMethod("setMaxBusyRetryTimeInterval:", Array(TimeoutInSeconds))

Regarding the locks, would it be possible to access "sqlite3_stmt_busy(sqlite3_stmt*)"

Not with NativeObject. It might be possible with inline OBJC code however it is not so simple.

You can access nonQueryStatementsList however I don't see how it can help you. It doesn't track the current executing statement.

 

[luc-dev]

Yes, nonQueryStatementsList doesn't track the current statement, according to the debugger output.

I will have to carefully track every SQL Exec sent/retrieved to/from the databases then.

Thank you all for the information provided here!

Cheers