C# to solve the problem of concurrency exceptions in SQlite (using read and write locks)

This article describes the method of C# to solve the problem of concurrent SQlite. Share it for your reference, as follows:

When accessing SQLite using C#, you often encounter the problem of multi-threaded concurrency causing SQLITE database corruption.。

SQLite is a file-level database, and its locks are also file-level.: Multiple threads can read at the same time, but only one thread can write at the same time. Android provides the SqliteOpenHelper class, which adds Java lock mechanism for calling. But no similar functionality is provided in C#.

The author uses read and write locks (ReaderWriterLock) to achieve the goal of multi-threaded safe access.

using System;
using ;
using ;
using ;
using ;
using ;
namespace DataAccess
{
/////////////////
public sealed class SqliteConn
{
  private bool m_disposed;
  private static Dictionary&lt;String, SQLiteConnection&gt; connPool =
    new Dictionary&lt;string, SQLiteConnection&gt;();
  private static Dictionary&lt;String, ReaderWriterLock&gt; rwl =
    new Dictionary&lt;String, ReaderWriterLock&gt;();
  private static readonly SqliteConn instance = new SqliteConn();
  private static string DEFAULT_NAME = "LOCAL";
  #region Init
  // Use singletons to solve problems during initialization and destruction  private SqliteConn()
  {
    ("LOCAL", new ReaderWriterLock());
    ("DB1", new ReaderWriterLock());
    ("LOCAL", CreateConn("\\"));
    ("DB1", CreateConn("\\"));
    ("INIT FINISHED");
  }
  private static SQLiteConnection CreateConn(string dbName)
  {
    SQLiteConnection _conn = new SQLiteConnection();
    try
    {
      string pstr = "pwd";
      SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
       =  + dbName;
      _conn.ConnectionString = ();
      _conn.SetPassword(pstr);
      _conn.Open();
      return _conn;
    }
    catch (Exception exp)
    {
      ("===CONN CREATE ERR====\r\n{0}", ());
      return null;
    }
  }
  #endregion
  #region Destory
  // Manually control destruction to ensure data integrity  public void Dispose()
  {
    Dispose(true);
    (this);
  }
  protected void Dispose(bool disposing)
  {
    if (!m_disposed)
    {
      if (disposing)
      {
        // Release managed resources
        ("Close the local DB connection...");
        CloseConn();
      }
      // Release unmanaged resources
      m_disposed = true;
    }
  }
  ~SqliteConn()
  {
    Dispose(false);
  }
  public void CloseConn()
  {
    foreach (KeyValuePair&lt;string, SQLiteConnection&gt; item in connPool)
    {
      SQLiteConnection _conn = ;
      String _connName = ;
      if (_conn != null &amp;&amp; _conn.State != )
      {
        try
        {
          _conn.Close();
          _conn.Dispose();
          _conn = null;
          ("Connection {0} Closed.", _connName);
        }
        catch (Exception exp)
        {
          ("Serious abnormalities: Unable to close localDB {0} Connection。", _connName);
          ();
        }
        finally
        {
          _conn = null;
        }
      }
    }
  }
  #endregion
  #region GetConn
  public static SqliteConn GetInstance()
  {
    return instance;
  }
  public SQLiteConnection GetConnection(string name)
  {
    SQLiteConnection _conn = connPool[name];
    try
    {
      if (_conn != null)
      {
        ("TRY GET LOCK");
        //Add lock, until it is released, other threads cannot get conn        rwl[name].AcquireWriterLock(3000);
        ("LOCK GET");
        return _conn;
      }
    }
    catch (Exception exp)
    {
      ("===GET CONN ERR====\r\n{0}", );
    }
    return null;
  }
  public void ReleaseConn(string name)
  {
    try
    {
      //release      ("RELEASE LOCK");
      rwl[name].ReleaseLock();
    }
    catch (Exception exp)
    {
      ("===RELEASE CONN ERR====\r\n{0}", );
    }
  }
  public SQLiteConnection GetConnection()
  {
    return GetConnection(DEFAULT_NAME);
  }
  public void ReleaseConn()
  {
    ReleaseConn(DEFAULT_NAME);
  }
  #endregion
}
}
////////////////////////

The code called is as follows:

SQLiteConnection conn = null;
try
{
  conn = ().GetConnection();
  //Write your own code here}
finally
{
  ().ReleaseConn();
}

It is worth noting that after each request for a connection, you must use the ReleaseConn method to release it, otherwise other threads will no longer be able to get the connection.

For safety reasons, in this tool class written by the author, the strictest read-write lock restrictions are enabled (i.e. cannot be read when writing). If data is read frequently, readers can also develop a method to obtain read-only connections to improve performance.

Test passed in Winxp/Win7/Win8/Win8.1 32/64 bit.

For more information about C# related content, please check out the topic of this site:Summary of thread usage techniques for C# programming》、《Summary of C# operation skills》、《Summary of XML file operation skills in C#》、《Tutorial on the usage of common C# controls》、《Summary of WinForm control usage》、《C# data structure and algorithm tutorial》、《Summary of C# array operation skills"and"Introduction to C# object-oriented programming tutorial》

I hope this article will be helpful to everyone's C# programming.