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...
Here is an example of code to reproduce a lock
Thanks
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
B4X:
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 SubThanks
