1175 Script continues after BeginTransaction/Commit

[MoDementia]

Script code after a BeginTransaction/Commit block continues before the commit is finished.

This is creating errors on new transactions and or new reads contain data not yet updated.

Please halt script execution until commit is complete
or
add a method to check if commit is still running

[EDIT]
If the script does
UpdateArtist
UpdateAlbum
UpdateDB
7 times
The next transaction I have to click retry between 20-30 times for it to go through (which is straight after the commit)

[EDIT AGAIN]
Even a way to automatically retry the transaction untill it stops erroring?

[Bex]

Do you have the code?

[Teknojnky]

I solved my transaction problems by putting a commit before every begintransaction, along with the closing commit.

so

commit (to close out any other pending transactions)
begin transaction (start of your transaction)
commit (close your transaction)

this is how last.fm nodes has been working for 2-3 months

[MoDementia]

Although attaching/detaching a database is unusual in a script it is a perfect example as detaching a database while a transaction is in progress will always create an error while it is a bit hit and miss to try and read/write a row that has/has not been committed yet.

The normal example would be refreshing a web control after the commit, you may or may not read the updated data

i.e. I don't know of any way to find out if the transaction/commit has actually completed.

The timer example here is ridicuclous for only 8 tracks
I have done some testing and even for 1 track I have to wait a minimum of 20secs.

Over the 20 secs I have set it to 3 secs per track so for 8 tracks it is 24 secs

As this is the end of the script 24 secs isn't too bad but if I want to use the updated data again I have to wait 24+ secs before updating a web control etc.

I haven't tested it with more tracks yet, it maybe only .005 secs per track after the minimum 20 secs?

Ideally there needs to be a command to make MM stop executing the script and show an hourglass until the commit has finished.

Code: Select all

  SDB.Database.Commit
  SDB.Database.WaitForCommit

A Timer would work if there is a way to check if a transaction is still active.

Code: Select all

SDB.Database.IsRunningTransaction

Code: Select all

    SDB.DataBase.ExecSQL("ATTACH " & Chr(34) & CopyFileName & Chr(34) & " AS ExportDB")
  Total = SDB.Database.OpenSQL("SELECT COUNT(*) FROM ExportDB.Songs").ValueByIndex(0)
  SDB.Database.BeginTransaction
  Set iter = SDB.DataBase.OpenSQL("SELECT * FROM ExportDB.Songs")
  Do While Not iter.EOF
    Set iter2 = SDB.Database.QuerySongs("Songs.ID=" & iter.StringByName("ID"))
    Do While Not iter2.EOF
      Set objSongData = iter2.Item
    iter2.Next
    Loop
    Set iter2 = Nothing
    If Not objSongData Is Nothing Then
      If objSongData.ID = iter.ValueByName("ID") Then
'       Update Stuff
      Else
'         Something Wrong
      End If
    Else
'       New Track
    End If
    If UpdateArtist = True Then
      objSongData.UpdateArtist
    End If
    If UpdateAlbum = True Then
      objSongData.UpdateAlbum
    End If
    objSongData.UpdateDB
  iter.Next
  Loop
  Set iter = Nothing
  SDB.Database.Commit
  WaitTime = Total*3000
  If WaitTime < 20000 Then
    WaitTime = 20000
  End If
  Set Tmr = SDB.CreateTimer(WaitTime)
  Script.RegisterEvent Tmr, "OnTimer", "TestTimer"
End Sub

Sub TestTimer(Tmr)
  SDB.DataBase.ExecSQL("DETACH ExportDB")
  Script.UnregisterEvents Tmr
End Sub

[MoDementia]

Additional Info

The only Error Number I can catch using ON ERROR is 450 of which the description isn't "Database Transaction in Progress"

I think because MM is trapping the error there is nothing I can do in the script to make it retry after 5 secs or something.

Of course for other examples it will not produce an error (unless you try to write it again) just non updated data

[Bex]

I have trouble to understand what you are trying to do but can't you:
1. Attach your Database do all the sql stuff
2. Update your Database
3. Detach your database
4. Update MM

[MoDementia]

Even though the name is ExportDB its actually an Import process

1. Attach your database
2. begin transaction
3. update MM from your database
4. commit
5. detach your database

But your example will fail also
1. Attach your Database do all the sql stuff
2. begin transastion
3. Update your Database
4. Commit
5. Detach your database <----- fail
6. begin transaction
7. Update MM
8. commit

[Bex]

Then I really don't know...

[MoDementia]

Below is some correspondance in the SQLite users mailing list.

Basically I believe it confirms that MM allows scripts to continue even when the script has executed a commit.

"The commit is finished when it returns to your program."

Unfortunatly it is returning to MM not the script.

This is a serious bug as scripts expect the commit to be finalised before the next line is executed.

There is no suitable workaround scripters can use to avoid this problem which creates inconsistent errors and needless code checking.

Thanks for the clarification.
The error I am receiving must then be produced by the application rather than just reporting an SQLite error.
Or
The application is taking control of the commit wait time, allowing my script to continue.
Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error.

*****
There was a problem querying the database:
Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore
****

The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error.

I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts.

So the question remains:
Is there a way to check for open transactions / locks from the command line?


-----Original Message-----
From: sqlite-users-bounces@sqlite.org [mailto:sqlite-users-bounces@sqlite.org] On Behalf Of Dennis Cote
Sent: Tuesday, 27 May 2008 11:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Checking for open transactions attach/detach database

MoDementia wrote:
> The database file is ready to (copy) Detach as soon as the commit completes.
>

Yes.

> This is the problem.
> Detach: "This statement will fail if SQLite is in the middle of a
> transaction."

This is true. This is why you must commit your transaction before you can detach.

> If I commit 10k row updates I cannot detach the database until it is
> finished.
>

The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement.

Attach
Begin
Loop to insert many rows
Commit
Detach

> I need to be able to check some sort of table entry that will be clear
> once the commit is finished.

The commit is finished when it returns to your program.

[jiri]

The next release of MM will off ForceCommit method that won't defer commits unlike ordinary Commit method. It should help in your case.

Jiri

[MoDementia]

Thanks Jiri but this isn't just my case.

Refreshing a web control panel after a commit is problematic because if the commit is deffered, as you suggest, the panel can still contain NON commited changes.

Is there any SDB.DataBase.ExecSQL( we can use to check for deferred NON complete commits?

[MoDementia]

I think I have a solution for now

While a transaction is in progress SQLite creates a file called
DatabaseName-journal
In the same directory that the database is in
i.e.
MM.DB-journal
or in my case
ExportDB-journal

A loop checking for the existance of this file will halt script execution until the transaction is completed i.e. all new entries written.

Code: Select all

  SDB.Database.Commit
  Do While 1=1
    SDB.ProcessMessages    
    If FSO.FileExists(DATABASEPATHNAME & "-journal") Then
      SDB.Tools.Sleep(1000)
    Else
      Exit Do
    End If
  Loop
  SDB.ProcessMessages
  SDB.DataBase.ExecSQL("DETACH ExportDB")

[Teknojnky]

that is good code for an infinite loop

I suggest putting some kind of counter in there with an error dialog or log msg to indicate the loop timed out while waiting for db commit.

you might also want to include a sdb.processmessages command so that MM can update the UI and stuff while your looping.

[jiri]

Maybe I'm misunderstanding, but the new ForceCommit should really help you - after this function DB won't remain in locked state and you should be able to properly access it from other connections.

Jiri

[mistresso]

I've searched high and low for an answer to similar woes, but I can't quite *see* what is happening inside MM (or SQLite) to cause the errors that my particular hangup.

I'd like to see a working example of ForceCommit. When my app throws a particular type of DB error - 'Error executing SQL statement "COMMIT" : SQL logic error or missing database' when doing a <songlist>.UpdateAll() - trying to either Commit or ForceCommit (yes this is after a BeginTransaction) just does nothing but cause the process to hang indefinitely. I can trap for the exception itself, but recovery is impossible, after which the database is in a locked state.

I do wonder if in some sense the "SQL logic error" is being caused by the above reported "bug"/feature of SQLite (it only happens SOME of the time). Quite honestly at this point I would simply be deliriously happy to have a Rollback statement... unless that is part of what ForceCommit is suppose to accomplish.