诊断SQLite锁定的原因 [英] Diagnosing cause of SQLite locking

问题描述

在 vb.net 应用程序中，我有一个案例，其中用户在表单的一部分中的操作正在创建一个 SQLite 锁，该锁会在应用程序后期导致问题(在这种情况下，将其关闭).

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

查询涉及的数据库中唯一的表是 tblImages.即便如此，连接一旦完成就会关闭.

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

关闭表单时，会调用一个函数，该函数在加载时捕获写入到名为 Settings 的 Dictionary 中的数据，并将其重新插入到数据库中.

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

此函数在 dbLocalQuery.ExecuteNonQuery() 阶段立即失败并出现数据库锁定错误，但我不明白为什么.我没有正确处理我的数据库关闭吗?我的印象是 SQLite 只在写入时创建锁，这在所描述的显式情况下从未发生过.

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.

推荐答案

dispose每个命令执行后应该可以解决问题

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

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

这篇关于诊断SQLite锁定的原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！