songdata object does not need or use explicit commit functions in script, they are only used for [url=http://www.mediamonkey.com/wiki/index.php/ISDBDatabase::OpenSQL]opensql queries[/url].

however songdata object may use commits 'behind the scenes', but all the script need worry about is using the update functions.

Nah, this is simply songdata updates - I am running some performance tests and keep running into database lock issues. I will post in a separate thread when I have more information. I'm using a .Net "wrapper" around the SQLite implementation, so the issues could be coming from a conflict between how THAT code works its connections to the MM database and how the internal MM API does it. SQLite has some... weirdness... to it, particularly in how it handles transactions/locks and connections. And while it's pretty obvious that the MM API's BeginTransaction and the .Net SQLite provider's BeginTransaction are basically calling the same pieces of SQLite (same with Commit, pretty normal stuff for talking to databases), it seems odd to me that we have no rollback functionality... or any way to 'unlock' the database (or check for a lock).

Are you using Attach/Detach?
If you post your code, I'll have a look.

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.

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

that is good code for an infinite loop :o

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.

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

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]
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")
[/code]

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?

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

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.

[b]"The commit is finished when it returns to your program."[/b]

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.

[quote]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.
[/quote]

Then I really don't know...

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

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

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

  SDB.Database.Commit
  SDB.Database.WaitForCommit

SDB.Database.IsRunningTransaction

    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

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] SDB.Database.Commit
SDB.Database.WaitForCommit
[/code]

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

[code]SDB.Database.IsRunningTransaction[/code]

[code] 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

[/code]