C#中SQLite数据库锁定异常求助：添加产品时报错

It’s frustrating when your product check feature works flawlessly but you hit a lock error the second you try to write to the database—let’s break down the most likely causes and fixes for your WinForms scenario.

First, let’s start with the most common culprit here: unclosed connections in your product add form. Your login code correctly uses using blocks to auto-dispose connections, but if your product add logic isn’t following the same strict pattern, that’s a prime suspect.

Key Fixes to Try: #

1. Enforce using Blocks for All Database Operations #

Every time you interact with SQLite (whether checking for existing products or adding new ones), wrap your SqliteConnection, SqliteCommand, and SqliteDataReader in using statements. This guarantees connections are closed immediately after use, even if an exception is thrown.

Here’s how your product add code should look to avoid locks:

try
{
    using (SqliteConnection db = new SqliteConnection("Filename=Magazyn.sqlite"))
    {
        db.Open();
        // First check if the product already exists
        string checkProductSql = "SELECT COUNT(*) FROM Products WHERE ProductName = @Name";
        using (SqliteCommand checkCmd = new SqliteCommand(checkProductSql, db))
        {
            checkCmd.Parameters.AddWithValue("@Name", productNameTextBox.Text);
            int productCount = Convert.ToInt32(checkCmd.ExecuteScalar());
            
            if (productCount == 0)
            {
                // Insert the new product
                string insertSql = "INSERT INTO Products (ProductName, Price, Stock) VALUES (@Name, @Price, @Stock)";
                using (SqliteCommand insertCmd = new SqliteCommand(insertSql, db))
                {
                    insertCmd.Parameters.AddWithValue("@Name", productNameTextBox.Text);
                    insertCmd.Parameters.AddWithValue("@Price", productPriceNumeric.Value);
                    insertCmd.Parameters.AddWithValue("@Stock", productStockNumeric.Value);
                    insertCmd.ExecuteNonQuery();
                }
            }
        }
    }
}
catch (SqliteException ex)
{
    MessageBox.Show($"Database Error: {ex.Message}");
}

2. Avoid Long-Running Transactions #

If your product add logic uses transactions, make sure you commit or roll them back immediately. A transaction that stays open (even accidentally, like if you forget to handle an error case) will hold a lock on the database. Never keep a transaction open while waiting for user input—complete it before returning control to the UI.

3. Rule Out External Process Locks #

Double-check if any other program is accessing Magazyn.sqlite (like a SQLite browser tool, or even another instance of your app running in the background). SQLite only allows one writer at a time, so external locks will trigger this error every time.

4. Optimize Your Connection String #

Add these settings to your connection string to reduce lock contention:

• Journal Mode=WAL: Enables write-ahead logging, which allows concurrent reads while a write is in progress (a game-changer for multi-form apps).
• Pooling=false: Sometimes connection pooling leaves stale connections open—disabling it ensures every connection is fresh and properly closed.

Updated connection string example:

"Filename=Magazyn.sqlite;Journal Mode=WAL;Pooling=false"

5. Never Share Connections Between Forms #

Never pass a SqliteConnection object from your login form to the product add form. Each form/operation should create its own connection (using using blocks) and dispose it right after use. Sharing connections across forms can lead to unexpected locks if one form holds the connection open longer than necessary.

Quick Debugging Tip: #

Add temporary logging (or message boxes) to track when connections are opened and closed in both forms. This will help you spot if a connection is staying open longer than it should.

内容的提问来源于stack exchange，提问作者Jakub Sobański