CODEWITHSUNDEEP
c#sqlitetransactionssystem.data.sqlite
quarandoo
quarandoo

Reputation: 369

Transactions handling in System.Data.Sqlite

I have a problem with understanding transactions handling in System.Data.Sqlite connected with the following piece of code. 

public static string ConnenctionString = "Data Source=C:\\DB\\Test.db";

public static void Test1()
{
    SQLiteConnection connection = new SQLiteConnection(ConnenctionString);
    connection.Open();
    SQLiteTransaction transaction = connection.BeginTransaction();
    for (int i = 0; i < 1000000; i++)
    {
        string sql = $"insert into table1 (id) values ({i})";

        SQLiteCommand command = new SQLiteCommand(sql, connection);
        command.ExecuteNonQuery();
    }
    transaction.Commit();

}
public static void Test2()
{
    SQLiteConnection connection = new SQLiteConnection(ConnenctionString);
    connection.Open();
    string sql = "select count(*) from table1";
    SQLiteCommand command = new SQLiteCommand(sql, connection);
    SQLiteDataReader reader = command.ExecuteReader();
    reader.Read();
    Console.WriteLine(reader[0]);
}
public static void Test3()
{
    for (int i = 0; i < 300; i++)
    {
        SQLiteConnection connection = new SQLiteConnection(ConnenctionString);
        connection.Open();
        SQLiteTransaction transaction = connection.BeginTransaction();
        string sql = $"insert into table1 (id) values ({i})";
        SQLiteCommand command = new SQLiteCommand(sql, connection);
        command.ExecuteNonQuery();
        transaction.Commit();
    }
}


static void Main(string[] args)
{
    //Everything is ok

    Task.Factory.StartNew(() => Test1());
    Thread.Sleep(1000);
    Task.Factory.StartNew(() => Test2());

    //Exception db is locked
    Task.Factory.StartNew(() => Test3());
    Thread.Sleep(1000);
    Task.Factory.StartNew(() => Test2());
    Console.ReadLine();
}

In one task I am executing some long running operation (in one transaction) - method Test1. In second task, I am trying to execute another query, without transaction - method Test2. Everything is running OK, I obtain a result that doesn't include changes from Test1 - because transaction hasn't been commited yet.

But when trying to execute in one task lots of queries - each in separate transaction, like in method Test3 and trying meanwhile to execute method Test2, I am obtaing exception: DB is locked. Is there a way to omit such behaviour modyfing connection string?

Upvotes: 2

Views: 3438

Answers (2)

Grisha
Grisha

Reputation: 723

You can try to modify your database so it will use write-ahead logging. It will allow simultaneous access for multiple tasks without locking. 

ExecuteQuery("pragma journal_mode=WAL");

immediately after creating the database. You need to execute this statement only once - it's persistent.

Upvotes: 1

jazb
jazb

Reputation: 5791

I would recommend using using statements (unfortunate pun):

using (var connection = new SqliteConnection("conn-string"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        for (int i = 0; i < 1000000; i++)
        {
            string sql = $"insert into table1 (id) values ({i})";

            using (var command = new SqliteCommand(sql, connection))
            {
                command.ExecuteNonQuery();
            }
        }

        transaction.Commit();
    }
}

Upvotes: 3

Related Questions