sqlite database lock error

Question

In my c# application i am using sqlite database.My c# application running as service in another machine.I perform CRUD operations from local machine , to perform CRUD operations to the database we developed flex application. Now the problem is when i try to delete or add items to the database from the front end, sometimes i am getting error as database is locked.Please find the code snippet below for deleting the data.

connection string

public ThingzDatabase(string dataFile)
        {
            _dataFile = dataFile;
            conn = new SQLiteConnection("data source=" + dataFile+";Version=3;");
        }

for executing commands below methods are used.

 public SQLiteDataReader ExecuteSQL(String sqlExpr)
        {

            if (conn.State != ConnectionState.Open)
                Open(DataFile);

                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlExpr;
                cmd.CommandType = CommandType.Text;
                return cmd.ExecuteReader();

        }

        public int ExecuteNonQuerySQL(String sqlExpr)
        {
            if (conn.State != ConnectionState.Open)
                Open(DataFile);

            int ireturn = 0;

            using (SQLiteTransaction dbtrans = conn.BeginTransaction())
            {
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlExpr;

                ireturn = cmd.ExecuteNonQuery();
                dbtrans.Commit();
            }

            return ireturn;
        }

Now below is the code used for deleting the relation,

OrderedRelationSet orderedRelationSet = new OrderedRelationSet(SessionDatabase, relation.RelationTypeId, relation.SourceThingId);

public OrderedRelationSet(ThingzDatabase db, int relationTypeId, int parentItemId)
        {
            this.db = db;
            this.relationTypeId = relationTypeId;
            this.parentItemId = parentItemId;
            childId = ThingzDatabase.thingsId;
            relations = new List<Relation>();


            SQLiteDataReader relationsReader = db.ExecuteSQL(
               @"SELECT *
                  FROM   relation
                  WHERE  parent_itemid = " + this.parentItemId.ToString() + @" and child_itemid=" + childId + @" and
                         relation_typeid = " + this.relationTypeId.ToString()
           );
            while (relationsReader.Read())
            {
                Relation relation = new Relation();

                relation.db = db;
                relation.SourceThingId = Convert.ToInt32(relationsReader["parent_itemid"]);
                relation.ThingId = Convert.ToInt32(relationsReader["child_itemid"]);
                relation.Id = Convert.ToInt32(relationsReader["id"]);
                relation.RelationTypeId = Convert.ToInt32(relationsReader["relation_typeid"]);

                relation.ordinal = Utilities.OptionalIntFieldFromSQLReader("ordinal", relationsReader);

                relation.thingIsChild = true;

                relations.Add(relation);
            }
            relationsReader.Close();
            reorder();
        }

----------

case "remove":
                                try
                                {
                                    if (orderedRelationSet.RemoveRelation(relation) != true)
                                    {
                                        TouchServer.Log(Logger.MessageType.Error, 0, "Remove relation operation failed");
                                        throw new Exception("remove relation failed");
                                    }
                                }
                                catch (Exception)
                                {
                                    if (orderedRelationSet.RemoveRelation(relation) != true)
                                    {
                                        TouchServer.Log(Logger.MessageType.Error, 0, "Remove relation operation failed");
                                        throw new Exception("remove relation failed");

This is my code....please let me know what is the solution to fix this lock issue. Thakx in advance

} }

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

as I know SQLite can really only handle one write at a time but can handle multiple simultaneous reads

sometimes i am getting error as database is locked

when there's a simultaneous write in the same time

you can check this question

edited May 23 '17 at 11:48

Community

1
1

answered Mar 09 '12 at 10:49

Akrem

5,033
8
37
64

sqlite database lock error

----------

1 Answers1

Linked