C Solution to SQlite Concurrency Exception of Uses Read Write Lock

  • 2021-11-01 04:19:27
  • OfStack

In this paper, an example is given to describe the method of C # to solve the concurrency exception problem of SQlite. Share it for your reference, as follows:

When using C # to access sqlite, you often encounter the problem that multithreaded concurrency causes SQLITE database corruption.

SQLite is a file-level database, and its lock is 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, adding the locking mechanism of Java for invoking. However, similar functionality is not provided in C #.

The author uses read-write lock (ReaderWriterLock) to achieve the goal of multithread safe access.


using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Threading;
using System.Data;
namespace DataAccess
{
/////////////////
public sealed class SqliteConn
{
  private bool m_disposed;
  private static Dictionary<String, SQLiteConnection> connPool =
    new Dictionary<string, SQLiteConnection>();
  private static Dictionary<String, ReaderWriterLock> rwl =
    new Dictionary<String, ReaderWriterLock>();
  private static readonly SqliteConn instance = new SqliteConn();
  private static string DEFAULT_NAME = "LOCAL";
  #region Init
  //  Using singleton to solve the problems during initialization and destruction 
  private SqliteConn()
  {
    rwl.Add("LOCAL", new ReaderWriterLock());
    rwl.Add("DB1", new ReaderWriterLock());
    connPool.Add("LOCAL", CreateConn("\\local.db"));
    connPool.Add("DB1", CreateConn("\\db1.db"));
    Console.WriteLine("INIT FINISHED");
  }
  private static SQLiteConnection CreateConn(string dbName)
  {
    SQLiteConnection _conn = new SQLiteConnection();
    try
    {
      string pstr = "pwd";
      SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
      connstr.DataSource = Environment.CurrentDirectory + dbName;
      _conn.ConnectionString = connstr.ToString();
      _conn.SetPassword(pstr);
      _conn.Open();
      return _conn;
    }
    catch (Exception exp)
    {
      Console.WriteLine("===CONN CREATE ERR====\r\n{0}", exp.ToString());
      return null;
    }
  }
  #endregion
  #region Destory
  //  Manual control of destruction to ensure data integrity 
  public void Dispose()
  {
    Dispose(true);
    GC.SuppressFinalize(this);
  }
  protected void Dispose(bool disposing)
  {
    if (!m_disposed)
    {
      if (disposing)
      {
        // Release managed resources
        Console.WriteLine(" Close local DB Connect ...");
        CloseConn();
      }
      // Release unmanaged resources
      m_disposed = true;
    }
  }
  ~SqliteConn()
  {
    Dispose(false);
  }
  public void CloseConn()
  {
    foreach (KeyValuePair<string, SQLiteConnection> item in connPool)
    {
      SQLiteConnection _conn = item.Value;
      String _connName = item.Key;
      if (_conn != null && _conn.State != ConnectionState.Closed)
      {
        try
        {
          _conn.Close();
          _conn.Dispose();
          _conn = null;
          Console.WriteLine("Connection {0} Closed.", _connName);
        }
        catch (Exception exp)
        {
          Console.WriteLine(" Severe anomaly :  Unable to close local DB {0}  Connections to. ", _connName);
          exp.ToString();
        }
        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)
      {
        Console.WriteLine("TRY GET LOCK");
        // Lock until released, other threads cannot get it conn
        rwl[name].AcquireWriterLock(3000);
        Console.WriteLine("LOCK GET");
        return _conn;
      }
    }
    catch (Exception exp)
    {
      Console.WriteLine("===GET CONN ERR====\r\n{0}", exp.StackTrace);
    }
    return null;
  }
  public void ReleaseConn(string name)
  {
    try
    {
      // Release 
      Console.WriteLine("RELEASE LOCK");
      rwl[name].ReleaseLock();
    }
    catch (Exception exp)
    {
      Console.WriteLine("===RELEASE CONN ERR====\r\n{0}", exp.StackTrace);
    }
  }
  public SQLiteConnection GetConnection()
  {
    return GetConnection(DEFAULT_NAME);
  }
  public void ReleaseConn()
  {
    ReleaseConn(DEFAULT_NAME);
  }
  #endregion
}
}
////////////////////////

The code for the call is as follows:


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

It is worth noting that every time a connection is requested, it must be released using the ReleaseConn method, otherwise other threads will never get the connection again.

To be safe, in this utility class written by the author, the strictest read-write lock limit (that is, it cannot be read while writing) is enabled. If data is read frequently, readers can also develop a way to get read-only connections to improve performance.

The test passed at 32/64 bits of Winxp/Win7/Win8/Win8.1.

For more readers interested in C # related content, please check the topics on this site: "Summary of thread usage skills in C # programming", "Summary of C # operating Excel", "Summary of XML file operation skills in C #", "C # common control usage tutorial", "WinForm control usage summary", "C # data structure and algorithm tutorial", "C # array operation skills summary" and "C # object-oriented programming introduction tutorial"

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


Related articles: