Sqlite database is locked

sqlite는 embedded system에서 널리 사용되는 무료 dbms?(dbms라고 말하긴 좀 그렇지만, dbms라 불러주자 ㅎ) 이다.

특히 memory db 기능이 아주 유용하다. 그 밖의 dbms에서도 이 기능이 있으나, 이 기능이 지원되는 버전은 대부분 고가이다. 따라서, 무료인 sqlite를 많이들 애용하는 것 같다.

멀티쓰레드를 sqlite DB를 구현하고 롱런테스트를 하다보면,
pthread_mutex_lock으로 쓰레드 간의 교착상태를 막아줘도,
sqlite lock 에러가 간헐적으로 발생할 것이다.

이에 대해 본인은 다음과 같은 에러 처리 구문을 준비하여 사용하고 있다.
sqlite Error가 발생하면, sqlite3_exception함수를 호출한다.
이 함수에서 sqlite error code를 구분하여, 만약, busy 또는 locked이면
최대 2초간 sleep 상태로 만드는 sqlite3_busy_timeout, busy handler를 호출한다.
그 다음, goto 구문으로 재차 sqlite3_exec를 실행한다.
단, sqlite3_exec는 transaction의 begin과 commit 또는 rollback 구문 사이에서 실행한다. 대부분 lock 에러가 발생하더라도 1~2번 실패 후에, 처리된다는 것이 본인의 테스트 결과이다.
단, journal를 WAL로 변경하였음.

기존 journal은 멀티쓰레드 지향적이지 않다는 점을 잊지마시길....

Error Code SQLITE_LOCKED (6): Database Is Locked

This error code occurs when you try to do two incompatible things with a database at the same time from the same database connection. For example, if you are in the middle of a SELECT statement and you try to DROP one of the tables being read by the SELECT, you will get an SQLITE_LOCKED error. Here is an example (using Tcl):

db eval {SELECT rowid FROM ex1} {
     if {$rowid==10} {
       db eval {DROP TABLE ex1}  ;# will give SQLITE_LOCKED error
     }
   }

Note that an SQLITE_LOCKED error is distinct from SQLITE_BUSY (5). SQLITE_BUSY means that another database connection (probably in another process) is using the database in a way that prevents you from using it. SQLITE_LOCKED means the source of contention is internal and comes from the same database connection that received the SQLITE_LOCKED error.

Here are other reasons for getting an SQLITE_LOCKED error:

1. Trying to CREATE or DROP a table or index while a SELECT statement is still pending.
   
   • Sometimes people think they have finished with a SELECT statement because sqlite3_step() has returned SQLITE_DONE. But the SELECT is not really complete until sqlite3_reset() or sqlite3_finalize() have been called.
   • As of check-in [3902] (2007-05-02 after version 3.3.17) this is now allowed for CREATE statement.
2. Trying to write to a table while a SELECT is active on that same table.
   
   • As of check-in [3355] (2006-08-16 after version 3.3.7) this is now allowed.
3. 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.
4. 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.

Anonymous adds: can someone please expand on the 'not set to do so' above? DRH replys: This third bullet item was added anonymously. I am not aware of any restrictions on doing two or more SELECTs from different threads at the same time, as long as each SELECT is happening in an independent database connection (that is: a separate sqlite3* object obtained from separate sqlite3_open() calls). As far as I know, the only time you can get an SQLITE_LOCKED error in the latest code is according to the first bullet above.