Thread synchronization in C#?

Question

I have lots of few small functions, each executes a query. I want only one function to be running at a time, what is the best possible way of thread sync to avoid database locked issue in SQLite (C#).

The functions are in multiple classes, how will you lock all the functions in all the DB classes, so that only one function from any of the DB class gets executed.

Answer 1

The easiest way is to have a common object and lock that object using c# lock() keyword. However I find this to be little cumbersome to manage on large projects and often have used wait handles.

I would have a function that gets the connection to the DB and then a release that must be called to signal the wait handle. On the get to the connection you could try wait to throw an error rather then have a deadlock.

Answer 2

To add onto rerun's answer, you'll want code that looks like this:

public class MyClass
{
    private readonly object lockObject = new object();

    public void MyMethod()
    {
        lock (lockObject)
        {
            // Do stuff
        }
    }

    public void AnotherMethod()
    {
        lock (lockObject)
        {
            // Do stuff
        }
    }
}

Jon Skeet has an excellent threading series: http://www.yoda.arachsys.com/csharp/threads/

Answer 3

I'll play devil's advocate... why do you want to invoke them in separate threads if only one will be executing at a time? I would just put the work items into a Queue and have a single thread read off the queue one at a time.

EDIT: As per my comment below, here is an example of a helper class you could use to refactor your data access into a more centralized place. It's a very basic example and not tested.

class Database {
    private string connectionString;
    private readonly object syncRoot = new object();

    public Database(string connectionString) {
        this.connectionString = connectionString;
    }

    public void ExecuteReader(SqlCommand command, Action<IDataRecord> forEachRow) {
        lock (syncRoot) {
            using (var connection = new SqlConnection(connectionString)) {
                command.Connection = connection;
                connection.Open();

                using (var reader = command.ExecuteReader()) {
                    while (reader.Read()) {
                        forEachRow(reader);
                    }
                }
            }
        }
    }
}

var myDB = new Database("connection string");
var myCommand = new SqlCommand("select id from blah");
myDB.ExecuteReader(myCommand, row => Console.WriteLine("ID: {0}", row["id"]));