Question: From sqlite FAQ I've known that:

> 
> Multiple processes can have the same database open at the same time.
>  Multiple processes can be doing a SELECT at the same time. But only
>  one process can be making changes to the database at any moment in
>  time, however.
> 
> 
> 

So, as far as I understand I can:
1) Read db from multiple threads (SELECT)
2) Read db from multiple threads (SELECT) and write from single thread (CREATE, INSERT, DELETE)

But, I read about [**Write-Ahead Logging**](http://www.sqlite.org/wal.html) that provides more concurrency as *readers do not block writers and a writer does not block readers*. Reading and writing can proceed concurrently.

Finally, I've got completely muddled when I found [it](http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked&t=1292363538), when specified:

> 
> Here are other reasons for getting an SQLITE\_LOCKED error:
> 
> 
> * Trying to CREATE or DROP a table or index while a SELECT statement is
>  still pending.
> * Trying to write to a table while a SELECT is active on that same table.
> * Trying to do two SELECT on the same table at the same time in a
>  multithread application, if sqlite is not set to do so.
> * fcntl(3,F\_SETLK call on DB file fails. This could be caused by an NFS locking
>  issue, for example. One solution for this issue, is to mv the DB away,
>  and copy it back so that it has a new Inode value
> 
> 
> 

So, I would like to clarify for myself, when I should to avoid the locks? Can I read and write at the same time from two different threads? Thanks.

Answer: 
Not specific to SQLite: 

1) Write your code to gracefully handle the situation where you get a locking conflict at the application level; even if you wrote your code so that this is 'impossible'. Use transactional re-tries (ie: SQLITE\_LOCKED could be one of many codes that you interpret as "try again" or "wait and try again"), and coordinate this with application-level code. If you think about it, getting a SQLITE\_LOCKED is better than simply having the attempt hang because it's locked - because you can go do something else.

2) Acquire locks. But you have to be careful if you need to acquire more than one. For each transaction at the application level, acquire all of the resources (locks) you will need in a consistent (ie: alphabetical?) order to prevent deadlocks when locks get acquired in the database. Sometimes you can ignore this if the database will reliably and quickly detect the deadlocks and throw exceptions; in other systems it may just hang without detecting the deadlock - making it absolutely necessary to take the effort to acquire the locks correctly.

Besides the facts of life with locking, you should try to design the data and in-memory structures with concurrent merging and rolling back planned in from the beginning. If you can design data such that the outcome of a data race gives a good result for all orders, then you don't have to deal with locks in that case. A good example is to increment a counter without knowing its current value, rather than reading the value and submitting a new value to update. It's similar for appending to a set (ie: adding a row, such that it doesn't matter which order the row inserts happened).

A good system is supposed to transactionally move from one valid state to the next, and you can think of exceptions (even in in-memory code) as aborting an attempt to move to the next state; with the option to ignore or retry.
You're fine with multithreading. The page you link lists what you cannot do while you're looping on the results of your `SELECT` (i.e. your select is active/pending) in the same thread.