Click here to Skip to main content
15,851,514 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am using SQLite database in my project, i tried a transaction which contains three update statements, while executing this transaction some times i am facing an exception while committing the transaction called data base is locked. Please check the piece of code:

C#
string connectionString = GetConnectionString();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                SQLiteTransaction transaction = connection.BeginTransaction();
                SQLiteCommand newCommand = connection.CreateCommand();

                try
                {
                    newCommand.CommandText = string.Format("UPDATE {0} SET MainVersion = {1}, CurrentVersion = {2}", "Version", (serverCurrentVersion - 1), (serverCurrentVersion + 1));
                    newCommand.ExecuteNonQuery();

                    newCommand.CommandText = string.Format("UPDATE {0} SET AppVersion = {1} WHERE AppVersion = {2}", "QueryTable", serverCurrentVersion + 1, serverCurrentVersion);
                    newCommand.ExecuteNonQuery();

                    newCommand.CommandText = string.Format("UPDATE {0} SET AppVersion = {1} WHERE AppVersion = {2}", "IndexQueryTable", serverCurrentVersion + 1, serverCurrentVersion);
                    newCommand.ExecuteNonQuery();

                    transaction.Commit();
                    status = true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                }
                finally
                {
                    newCommand.Dispose();
                    transaction.Dispose();
                }
            }    


could anybody please help me out.?

[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 12-Mar-14 0:56am
v3

1 solution

A quick look at the internet says that the "normal" reason for getting this is that the database was accessed by different code for reading or writing while the transaction was being processed - and if you are getting this error intermittently, then then that would make sense.

Are you perhaps reading from the DB in a different thread? Or are you trying to use SqLite for multiuser?
If it's your threads, then perhaps just locking would solve it? If it's a different app accessing the same file, then you have two options: catch the exception and retry a number of times, or change to a server based DB (SQL Server, or MySql) which is designed for multiuser.
I've never had good results with "file based" DB's (SqLite, Access) for multiuser access - it works, but it's generally more hassle than it's worth.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900