SQLite multi-thread lock file write Solution

An exception occurs when multiple threads are simultaneously written in sqlite programming. I wrote a class to solve this problem.

The idea is simple, that is, when a write operation is started, write down the id of the managed thread for the write operation, indicating that there are threads currently performing the write operation; when other threads write, you need to first check whether a process is performing write operations. If yes, you need to wait. When a configured timeout time is reached, an exception will be thrown to terminate the wait. If not, you can directly release it, this thread can obtain the write lock. The lock must be released after the write operation is completed.

The specific code is as follows:

SQLiteWriteLock
/// <Summary>
/// Used to prevent lock files caused by synchronous write when multiple threads access sqlite
///
/// Usage:
/// Using (SQLiteWriteLock sqliteLock = new SQLiteWriteLock (SQLite link string ))
///{
//// Sqlite write operation code
///}
///
/// You can set the value of SQLiteWriteLockTimeout to control the lock wait timeout by adding the value in the configuration file deletetimeout section. The value must be a positive integer in milliseconds,
/// The default timeout value is 1000 ms.
/// </Summary>
Public sealed class SQLiteWriteLock: IDisposable
{
# Region static fields and attributes
Const short WAIT_TIME = 5;
Static readonly object locker = new object ();
Static Dictionary <string, int> _ dbThreadIdDict = new Dictionary <string, int> ();

/// <Summary>
/// Get the timeout time for the write operation, in milliseconds. You can set the value of SQLiteWriteLockTimeout in the configuration file deletetimeout section to control the lock wait timeout time, the value must be a positive integer in milliseconds.
/// The default timeout value is 1000 ms.
/// </Summary>
Public static int SQLiteWriteLockTimeout
{
Get
{
String configValule = ConfigurationManager. receivettings ["SQLiteWriteLockTimeout"];
If (! String. IsNullOrEmpty (configValule ))
{
Return int. Parse (configValule );
}
Return 1000;
}
}
# Endregion

Private readonly string _ connString;

// Hide the parameter-free Constructor
Private SQLiteWriteLock (){}

Public SQLiteWriteLock (string connString)
{
_ ConnString = connString;
AcquireWriteLock ();
}

# Region private Method

Private void AcquireWriteLock ()
{
Int threadId = Thread. CurrentThread. ManagedThreadId;

Int waitTimes = 0;
While (_ dbThreadIdDict. ContainsKey (_ connString) & _ dbThreadIdDict [_ connString]! = ThreadId)
{
Thread. Sleep (WAIT_TIME );
WaitTimes + = WAIT_TIME;
# If DEBUG
Console. WriteLine (_ connString + "wait for" + waitTimes + "ms ");
# Endif
If (waitTimes> SQLiteWriteLockTimeout)
{
Throw new TimeoutException ("SQLite waiting for write operation timeout ");
}
}

Lock (locker)
{
If (! _ DbThreadIdDict. ContainsKey (_ connString ))
_ DbThreadIdDict. Add (_ connString, threadId );
}
}

Private void ReleaseWriteLock ()
{
Lock (locker)
{
If (_ dbThreadIdDict. ContainsKey (_ connString ))
{
_ DbThreadIdDict. Remove (_ connString );
}
}
}

# Endregion

# Region IDisposable Member

Public void Dispose ()
{
ReleaseWriteLock ();
}

# Endregion
}

 

Hope this article is useful.