SQLite "Database is Locked" when moving a list of records from one table to another

MontanaVbMan

New member
Joined
Mar 5, 2022
Messages
3
Programming Experience
5-10
I have a Public Sub to move a collection of records from one table to another in the same SQLite database. First it reads the first 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 multiple image blobs (more than 3 or 4), 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 multiple image blobs (I get no error when only moving 3 or 4):

VB.NET:
    Using SQLconnect = New SQLiteConnection(strDbConnectionString)
        SQLconnect.Open()
        Using SQLcommand = SQLconnect.CreateCommand
            Using tr = SQLconnect.BeginTransaction()

                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

            tr.Commit()
            End Using
        End Using
    End Using

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

VB.NET:
    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.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,746
Location
Sydney, Australia
Programming Experience
10+
Why not do all the insertions in one go and all the deletions in one go?
VB.NET:
Dim ids = lvcollection.Cast(Of ListViewItem)().Select(Function(lvi) lvi.Tag)
Dim insertSql = $"INSERT INTO {strToTable} SELECT * FROM {strFromTable} WHERE id IN ({String.Join(", ", ids)})"
You can then start the transaction, do all the inserts in one go, do all the deletes in one go and then commit the transaction. Also, you ought to be catching an exception and rolling back the transaction.
 

MontanaVbMan

New member
Joined
Mar 5, 2022
Messages
3
Programming Experience
5-10
Why not do all the insertions in one go and all the deletions in one go?
VB.NET:
Dim ids = lvcollection.Cast(Of ListViewItem)().Select(Function(lvi) lvi.Tag)
Dim insertSql = $"INSERT INTO {strToTable} SELECT * FROM {strFromTable} WHERE id IN ({String.Join(", ", ids)})"
You can then start the transaction, do all the inserts in one go, do all the deletes in one go and then commit the transaction. Also, you ought to be catching an exception and rolling back the transaction.
Hi: Thank you very much for your answer. I'm not a great programmer, and I'm afraid I don't understand line 1 in your code. And the last part of line 2 is a bit fuzzy to me also. It looks to me that in line 1, you are building a sort of list of id numbers, then using that to append to the SQL Insert statement. Would you please explain this for dummies? Thanks!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,746
Location
Sydney, Australia
Programming Experience
10+
The first line is a LINQ query that creates an enumerable list of the Tag property values from the items in the list. It's basically a flattening of your existing loop. It may be less opaque using query syntax instead of function syntax:
VB.NET:
Dim ids = From lvi As ListViewItem
          In lvcollection
          Select lvi.Tag
LINQ queries work very similarly to SQL queries. You can see the cast as type ListViewItem in there, which maps to the Cast method, and you can see the Select clause, which projects each item in the collection.

The String.Join method then joins that list of values into a single String with delimiters between them. If the Tags of the items were 1, 2 and 3 then this:
VB.NET:
String.Join(", ", ids)
would produce this:
VB.NET:
"1, 2, 3"
and so the value of insertSql might be something like this:
VB.NET:
"INSERT INTO Table1 SELECT * FROM Table2 WHERE id IN (1, 2, 3)"
 

MontanaVbMan

New member
Joined
Mar 5, 2022
Messages
3
Programming Experience
5-10
Wow ... first I want to emphasize how much I appreciate your willingness to take the time for the careful explanation. I now understand exactly what you posted.

Question 1: To build the ids list, is the LINQ query faster than "For Each" looping through the listviewCollection and building a list that way?

Question 2: Is there a limit to the number of entries you can put into the IN operator parentheses? I think I read somewhere that the limit is 999 for inserting multiple records using the VALUE statement, so I wonder if there is a limit also for the IN operator.

And another point: I built the SQLCommand.CommandText with the IN operator in the SELECT statement as you showed me. One command for the INSERT and one for the DELETE, and put them both in a Transaction. That one transaction handles all of the records without error.

But ... the performance is basically the same as if I loop through the listViewCollection and build (and comit) a separate Transaction for each record (without the IN operator). I think the IN operator forces the DB to wait for the previous INSERT to finish before attempting the next SELECT.

What do you think?

Again ... thanks.
 
Last edited:

Latest posts

Top Bottom