How can we maintain concurrency in Sqlite database?

Question

Hi all How can we maintain concurrency in SQlite database(Version-3) which is accessing by multiple threads /Apps. and if we cannot maintain concurrency in SQlite then which database is useful for my app? Please Help !!! Thanks.

Sunny Sharma · Answer

Hi Sumit modify your code like this: -------------------------------------------------- public bool ExecuteANonQuery(string sql int i = 0) { try { Open();//for connection open using (IDbCommand command = _dbCon.CreateCommand()) { command.CommandText= PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE ; +sql+ COMMIT;; int ww = command.ExecuteNonQuery(); } return true; } catch (SQLiteException ex) { if (ex.Message.Contains(readonly database)) { i++; if (i { Thread.Sleep(100); return ExecuteANonQuery(sql i); } } throw; } catch { throw; } finally { Close(); } --------------------------------------------------- I'm glad I could help.

Sumit Kumawat · Answer

Hello sir Sorry for delay in reply. actually i found solution myself. below is solution of my problem. After setting locking_mode = EXCLUSIVE; we have to reset locking_mode = NORMAL;  But now i have another query if i want to access my same SQlite db from two different applications(multi-threaded) Then how i can maintain concurrency at this time. i tried several times to access database but i got this error Database lock. Thanks.

Sunny Sharma · Answer

ok show me your code that causing the problem.

Sumit Kumawat · Answer

Hi sir yeah u were right i was missing semicolon in my query. now i added it :). but now again i am facing another issue SQL logic error or missing database cannot start a transaction within a transaction i searched on google but not able to find the solution. :( Please help me. Thanks

Sunny Sharma · Answer

Hi Sumit The code will definitely work just make sure that each of your statement terminates with a semi-colon ( the error is most probably because of this only ). ------------------------------------------ command.CommandText= PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE ; +sql+ COMMIT;; ----------------------------------------------------------- Make sure that the sql (that denotes your query statement) in above syntax is terminated properly with a semi-colon so that it could generate a single query like: ------------------------------------------ command.CommandText= PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE ; +insert into myTable values( ; + COMMIT;; ------------------------------------------ See in my above statement it has four statements combined in one and each one is terminated with a semi-colon. Hope you got the point now. Cheers!

Sumit Kumawat · Answer

Hii Sir I used code which u provided in my app but i am getting below error SQL logic error or missing database near COMMIT: syntax error

Sumit Kumawat · Answer

Thanks a lot :) :)

Sumit Kumawat · Answer

Hi Sunny Thanks a lott for your reply.Can u give me little more help. how to use PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE; COMMIT; public bool ExecuteANonQuery(string sql int i = 0) { try { Open();//for connection open using (IDbCommand command = _dbCon.CreateCommand()) { command.CommandText = sql; int ww = command.ExecuteNonQuery(); } return true; } catch (SQLiteException ex) { if (ex.Message.Contains(readonly database)) { i++; if (i { Thread.Sleep(100); return ExecuteANonQuery(sql i); } } throw; } catch { throw; } finally { Close(); } using this method i am executing my queries can u please tell me where i put your code inside this.or this is wrong way then can u plz share some demo code for this. Thanks a lot for your help

Sunny Sharma · Answer

Hi Sumit Execute these commands PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE; This will lock whole database until you execute a: COMMIT; Implement above commands in your query and I think the problem will get sorted out. Looking forward for your response in that.

Sumit Kumawat · Answer

Hi Sunny Thanks for your reply. Can u please tell me how can i lock the database file ? i tried to lock database command which is executing while inserting/updating my Query.but i did not get any success. Please tell me how i will do this processthus i will sort out concurrency issue in my database. Thanks

Sunny Sharma · Answer

Hi Sumit you CAN maintain the concurrency in SQLite (Version 3) like you do in other DBMS. SQLite FAQs page says-... no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once and for multiple processes to read the database at once. When any process wants to write it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once. (refer to: http://www.sqlite.org/faq.html ) So if you're planning to use embedded database just go ahead with SQlite it's quite useful and worthy. Mark this as answer if it helps. Cheers!