Diagnosing cause of SQLite locking

Question

On a vb.net application, I have a case where a users actions in one portion of the form is creating a SQLite lock that causes problems later in the application (in this case, closing it down).

Here is the sub-routine called when users add data to a list of items to be printed:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
    ' Add item to printQueue => regenerate listPrint.items
    Dim queueItem As New Dictionary(Of String, String)
    queueItem("quantity") = inputQuantity.Value.ToString
    queueItem("description") = textDesc.Text
    queueItem("sizeUK") = inputSize.Value.ToString.Replace(".5", "½").Replace(".0", "")
    queueItem("sku") = listStyles.SelectedItem.ToString
    queueItem("colour") = textColour.Text
    queueItem("date") = textDateCode.Text
    queueItem("name") = textName.Text

    Try
        queueItem("sizeEU") = sizeEU(inputSize.Value).ToString.Replace(".5", "½")
    Catch ex As Exception
        queueItem("sizeEU") = "??"
    End Try

    ' US Size: M = UK + 1; F = UK + 1.5
    queueItem("sizeUS") = (inputSize.Value + 1.5 - (chkSex.CheckState * 0.5)).ToString.Replace(".5", "½")

    ' Add the image data as a string
    dbLocalQuery = New SQLiteCommand("SELECT * FROM tblImages WHERE id ='" & listStyles.SelectedItem.ToString & "'", dbLocal)

    If dbLocal.State = ConnectionState.Closed Then
        dbLocal.Open()
    End If

    Dim r As SQLiteDataReader = dbLocalQuery.ExecuteReader()

    Try
        r.Read()
        queueItem("image") = byte2string((r("image")))
    Catch ex As Exception
        queueItem("image") = settings("imgNotFound")
    Finally
        If dbLocal.State = ConnectionState.Open Then
            dbLocal.Close()
        End If
    End Try

    printQueue.Add(printQueue.Count + 1, queueItem)
    MsgFrame.Items.Add(printQueue(printQueue.Count)("sku") & " x" & printQueue(printQueue.Count)("quantity"))
    MsgFrame.SelectedIndex = MsgFrame.Items.Count - 1
    ' Update print queue list
    populateList()
End Sub

The only table in the database touched by the query is tblImages. Even then, the connection is closed once finished.

When closing the form down, a function is called that captures data that is written to the Dictionary called Settings on load and reinserts it into the database.

Public Function dbLocalSave() As Boolean
    'Recreates the tblSettings from current values stored in settings 
    Try
        If dbLocal.State = ConnectionState.Closed Then
            dbLocal.Open()
        End If

        If dbLocal.State = ConnectionState.Open Then
            MsgFrame.Items.Add("Flushing local settings table")
            MsgFrame.SelectedIndex = MsgFrame.Items.Count - 1

            For Each pair In settings
                Debug.Print("+ tblSettings: " & pair.Key & " = " & pair.Value)

                dbLocalQuery = New SQLiteCommand("DELETE FROM tblSettings where name = '" & pair.Key & "';", dbLocal)
                dbLocalQuery.ExecuteNonQuery()

                dbLocalQuery = New SQLiteCommand("INSERT INTO tblSettings (`name`,`value`) VALUES ('" & pair.Key & "','" & pair.Value & "');", dbLocal)
                dbLocalQuery.ExecuteNonQuery()
            Next
            Return True
        Else
            Return False
        End If
    Catch sqlex As SQLiteException
        MessageBox.Show(sqlex.Message, "SQL Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Finally
        If dbLocal.State = ConnectionState.Open Then
            dbLocal.Close()
        End If
    End Try

End Function

This Function immediately fails at the dbLocalQuery.ExecuteNonQuery() stage with a db lock error, but I cannot understand why. Am I not handling my db closing correctly? I was under the impression that SQLite only created locks on writes, which never happens in the explicit case described.

If I do not 'Add' an item to the print queue (fundamental functionality of the program), the program closes gracefully.

score 2 · Accepted Answer · answered Apr 05 '15 at 11:58

Dispose each command after executing should solve the problem

dbLocalQuery.ExecuteNonQuery()
dbLocalQuery.Dispose()

the problem is because you have delete and insert command concurrently executing which is causing the error

            dbLocalQuery = New SQLiteCommand("DELETE FROM tblSettings where name  = '" & pair.Key & "';", dbLocal)
            dbLocalQuery.ExecuteNonQuery()
            'insert here '
            dbLocalQuery.dispose()
            dbLocalQuery = New SQLiteCommand("INSERT INTO tblSettings (`name`,`value`) VALUES ('" & pair.Key & "','" & pair.Value & "');", dbLocal)
            dbLocalQuery.ExecuteNonQuery()
            'here also '
            dbLocalQuery.dispose()

Ref : http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

I'll give you the best answer credit because it solves the problem rather than throw it in the bin. :) — Optimaximal, Apr 15 '15 at 09:37

score 0 · Answer 2 · answered Mar 20 '15 at 10:52

I worked around the issue by replacing the database writing with local system registry storage. This has the benefit of making it user agnostic. The db settings are used as a 'default' for new users.

onload:

'Get settings from Registy - if key not present, get defaults from db.

    Dim regkey As RegistryKey = Registry.CurrentUser.OpenSubKey("Software\Foot Shop Ltd\" & Application.ProductName, True)

    If regkey Is Nothing Then
        'Key doesn't exist, populate settings from DB

        Try
            Debug.Print("Opening Database...")
            If dbLocal.State = ConnectionState.Closed Then
                dbLocal.Open()
            End If

            Debug.Print("Database Open - " & Application.StartupPath & "\boxLabels.db")

            dbLocalQuery = New SQLiteCommand("SELECT * FROM tblSettings", dbLocal)

            'Get default settings from SQLite DB, write to registry
            Dim r As SQLiteDataReader = dbLocalQuery.ExecuteReader()
            While r.Read()
                settings.Add(CStr(r("name")), CStr(r("value")))
                Debug.Print("Default Used: " & CStr(r("name")) & " = " & CStr(r("value")))
            End While
            r.Close()

        Catch sqlex As SQLiteException
            Debug.Print(sqlex.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Loading Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Application.Exit()
            Exit Sub
        Finally
            If dbLocal.State = ConnectionState.Open Then
                dbLocal.Close()
            End If
        End Try

    Else
        'build settings from registry

        Dim names As String() = regkey.GetValueNames

        ' Open the next subkey if any and call myself.
        Dim value As String
        Dim data As String
        For Each value In names
            data = regkey.GetValue(value, Nothing)
            settings.Add(value.ToString, data.ToString)
            Debug.Print("Reg Value Used: " & value.ToString & " = " & data.ToString)
        Next

    End If

    regkey.Close()

dbLocalSave:

    Public Function dbLocalSave() As Boolean
    'Recreates the tblSettings from current values stored in settings 
    Try
         MsgFrame.Items.Add("Flushing local settings table")
        MsgFrame.SelectedIndex = MsgFrame.Items.Count - 1

        For Each pair In settings
            Debug.Print("+ tblSettings: " & pair.Key & " = " & pair.Value)
            My.Computer.Registry.SetValue("HKEY_CURRENT_USER\Software\Foot Shop Ltd\" & Application.ProductName, pair.Key, pair.Value)
        Next

    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return False
    End Try

    Return True
End Function

Diagnosing cause of SQLite locking

2 Answers2

Linked