My application is a multi threaded application, which uses sqlite to store data on a file. Our current architecture of using sqlite in shared cache mode seems to be working absolutely fine. In this case a new connection is being given to a thread in shared cache mode to the database. If one of the thread gets a SQLITE_LOCKED status for statement execution, we use try again after some time assuming the other thread holding the lock would have either failed or committed the transaction.
Recently we moved to our connection type from shared cache to normal mode, to see if there is any significant improvement in performance as suggested by team member. Right now we have multiple database connections to same database. We started to see lot of SQLITE_LOCKED hangs. I am just putting forth this question to know how the locking mechanism happens in normal mode. 1. Does a transaction lock ,will lock the table or the whole data base connection.? 2. What is the major difference in locking when compared to shared cache mode. ? I am sure we haven't changed anything else except for the connection type. Srikanth
