How to maintain concurrency in SQLite Database

If you’re planning to use embedded database for your small data needs, SQLite is just a go to option. It’s faster, supports transactional (ACID) queries.

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)


Execute these commands before the actual query executes:

PRAGMA locking_mode = EXCLUSIVE;
BEGIN EXCLUSIVE;


This will lock whole database until you execute a:


COMMIT;


You can use the syntax below to implement this in your code:

command.CommandText=”PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE;“++” COMMIT;”;command.ExecuteNonQuery();

Cheers!



(9)

Leave a Reply

Your email address will not be published. Required fields are marked *