Getting "Database is Locked" when trying to move a list of records from one table to anoth-CodePudding

I have a Public Sub to move a collection of records from one table to another in the same SQLite database. First it reads a record from strFromTable, then writes it to strToTable, then deletes the record from strFromTable. To speed things up, I've loaded the entire collection of records into a transaction. When the list involves moving a lot of image blobs, the db gets backed up, and throws the exception "The Database is Locked". I think what is happening is that it's not finished writing one record before it starts trying to write the next record. Since SQLite only allows one write at a time, it thows the "Locked" exception.

Here is the code that triggers the error when moving a lot of image blobs:

    Using SQLconnect = New SQLiteConnection(strDbConnectionString)
        SQLconnect.Open()
        Using tr = SQLconnect.BeginTransaction()
            Using SQLcommand = SQLconnect.CreateCommand
            
                For Each itm As ListViewItem In lvcollection
                    SQLcommand.CommandText = $"INSERT INTO {strToTable} SELECT * FROM {strFromTable} WHERE id = {itm.Tag}; DELETE FROM {strFromTable} WHERE ID = {itm.Tag};"
                    SQLcommand.ExecuteNonQuery()
                Next

            End Using
        tr.Commit()
        End Using
    End Using

When I get rid of the transaction, it executes without error:

    Using SQLconnect = New SQLiteConnection(strDbConnectionString)
        SQLconnect.Open()
        Using SQLcommand = SQLconnect.CreateCommand

            For Each itm As ListViewItem In lvcollection
                SQLcommand.CommandText = $"INSERT INTO {strToTable} SELECT * FROM {strFromTable} WHERE id = {itm.Tag}; DELETE FROM {strFromTable} WHERE ID = {itm.Tag};"
                SQLcommand.ExecuteNonQuery()
            Next

        End Using
    End Using

I'm not very good with DB operations, so I'm sure there is something that needs improvement. Is there a way to make SQLite completely finish the previous INSERT before executing the next INSERT? How can I change my code to allow using a transaction?

Thank you for your help.

CodePudding user response：

Ok ... here is the solution that I decided to go with. I hope this helps someone finding this in a search:

Dim arrIds(lvcollection.Count - 1) As String
Dim i as Integer = 0

' Load the array with all the Tags in the listViewCollection
For Each itm As ListViewItem In lvcollection
    arrIds(i) = itm.Tag 'Each itm.Tag holds the Primary Key "id" field in the DB
    i  = 1
Next

'build a comma-space separated string of all ids from the array of ids.
Dim strIds as String = String.Join(", ", arrIds)  

Using SQLconnect = New SQLiteConnection(strDbConnectionString)
    SQLconnect.Open()
    Using tr = SQLconnect.BeginTransaction()
        Using SQLcommand = SQLconnect.CreateCommand            
            
            SQLcommand.CommandText = $"INSERT INTO {strToTable} SELECT * FROM {strFromTable} WHERE id IN ({strIds});"
            SQLcommand.ExecuteNonQuery()

            SQLcommand.CommandText = $"DELETE FROM {strFromTable} WHERE ID IN ({strIds});"
            SQLcommand.ExecuteNonQuery()

        End Using
    tr.Commit()
    End Using
End Using

The IN statement allows me to pass all of the "id" values to be deleted as a batch. This solution is faster and more secure than doing them one by one with no transaction.

Thanks for the comments, and best wishes to everyone in their coding.