bah.database

I'm having hard time getting two processes to update the same SQLite database file at the same time. I know it's not possible for two processes to commit exactly at the same time, but I'd love to have executeQuery() and/or commit() to return an error when a conflict happens. Currently they seem not to do it.

Here's a snippet I've been trying to demonstrate this with:

SuperStrict

Framework bah.DBSQLite

Local db:TDBConnection = LoadDatabase("SQLITE", "test.db") 

If db.isOpen() Then
  db.executeQuery("Create TABLE tbl (id integer primary key AUTOINCREMENT, name varchar(30))") 
	
  For Local i:Int = 1 To 20
    Repeat
      db.executeQuery("INSERT INTO tbl values (NULL, 'Name " + i + "')") 
      If db.hasError() Then
        DebugLog db.error().ToString() 
        Delay(2000) 
      EndIf
    Until Not db.hasError() 
    Delay(2000) 
  Next
  db.Close() 
  DebugLog "Done"
End If

What it basically does is insert 20 records in 2 second intervals into the table, and if an update fails, it retries until it succeeds. The problem is that it never fails when the database is being updated by another process at the same time. It just happily moves on to the next record in the For-loop. Kinda makes it hard to create a foolproof record insertion method.

The following example program updates the first record 50 time per second and is guaranteed to cause update conflicts when the two programs are run at the same time:

SuperStrict

Framework bah.DBSQLite
Import brl.timer

Local db:TDBConnection = LoadDatabase("SQLITE", "test.db") 

If db.isOpen() Then
  Local timer:Int = MilliSecs() 
  Repeat
	db.executeQuery("UPDATE tbl SET name = 'Updated name' WHERE id = 1") 
	If db.hasError() Then DebugLog db.error().toString() 
	Delay(20) 
  Until MilliSecs() - timer > 40000
  
  db.Close() 
End If

The first program fails to insert on average every third record if the second program is running at the same time.

That would be OK if I had means to check whether or not the insertion succeeded. Any ideas?