Re: [sqlite] SQLite lock behavior in shared-cache mode
Does SQLite support MVCC (http://en.wikipedia.org/wiki/Multiversion_concurrency_control)? It sounds like it doesn't. Maybe it should--that's a very nice way to support unblocked reads while still getting mutex for writes. - Original Message - From: "Dan" <danielk1...@gmail.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Monday, July 27, 2009 9:27 AM Subject: Re: [sqlite] SQLite lock behavior in shared-cache mode > > While compiling any statement (sqlite3_prepare_v2()), or while > stepping > (sqlite3_step()) a statement that accesses the main database, a > mutex > associated with the in-memory cache of the main database will be > held. > > Dan. > > > > On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote: > >> Hi, >> >> I'm using SQLite latest version (3.6.16) with shared-cache enable >> in >> a process that has around 5 threads. Database connections for each >> thread are created with the same main database file. After that, >> each >> connection is attached to a particular database file (one for each >> thread) using the same schema name. Final structure is similar to >> the >> following: >> >> Main database file: main.db >> Thread-1 database file: thread1.db >> Thread-2 database file: thread2.db >> Thread-3 database file: thread3.db >> Thread-4 database file: thread4.db >> Thread-5 database file: thread5.db >> >> Thread-1 connection is opened with the main.db file and attaches >> the >> thread1.db as "extradb" schema name; >> Thread-2 connection is opened with the main.db file and attaches >> the >> thread2.db as "extradb" schema name; >> Thread-3 connection is opened with the main.db file and attaches >> the >> thread3.db as "extradb" schema name; >> Thread-4 connection is opened with the main.db file and attaches >> the >> thread4.db as "extradb" schema name; >> Thread-5 connection is opened with the main.db file and attaches >> the >> thread5.db as "extradb" schema name; >> >> Every SQL statement submitted to the process and passed to one of >> these threads can read global informations maintained in the >> main.db >> database file ("main" schema) and write/read particular >> informations >> in the "extradb" schema in such a way that one thread does not need >> to >> wait for another thread to write its information, since each thread >> has the "extradb" schema attached to a particular database file. >> >> Shared-cache is used for 2 reasons: >> - to improve main.db database file data access; and, >> - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to >> avoid many SQLITE_LOCKED errors based on the code provided in >> http://www.sqlite.org/unlock_notify.html >> . >> >> In my understanding, the expected behavior should be: >> - If a SQL statement with only read (heavy) operations is passed to >> Thread-1; and, >> - Another SQL statement with a write (and some reads) operations is >> passed to Thread-3; >> - Both should run in parallel in a multi-core system. >> >> But, it seems that the Thread-3 is waiting for the Thread-1 to >> finish >> its work before continue. >> This behavior is turning the solution into a non-scalable solution. >> >> As far as I could debug (and understand) using Visual Studio 2005, >> it >> seems that Thread-3 (in the above example) is waiting in for a lock >> in >> the sqlite3BtreeEnterAll function. See the piece of the call stack >> below: >> >>> sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8) Line >>> 15159 >> sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8) Line >> 36706 + 0x11 bytes >> sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const >> char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1, >> sqlite3_stmt * * ppStmt=0x, const char * * >> pzTail=0x0965f63c) >> Line 9672 >> sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char >> * zSql=0x00c0, int nBytes=180, sqlite3_stmt * * >> ppStmt=0x013a9094, >> const char * * pzTail=0x0965f63c) Line 9747 + 0x1f bytes >> apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 * >> db=0x, const char * zSql=, int nSql=, sqlite3_stmt * * >> ppStmt=0x, const char * * pz=0x) Line 247 + 0x10 >> bytes >> apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t * >> pool=0x0
Re: [sqlite] SQLite lock behavior in shared-cache mode
Thanks again Dan for the quick response. I understood. Is this also true for any other database file attached to these threads connections that, as the main.db file, are used for read-only operations but might have one or more threads reading from it at the same time? Alessandro. On 27/07/2009, at 13:27, Dan wrote: > > While compiling any statement (sqlite3_prepare_v2()), or while > stepping > (sqlite3_step()) a statement that accesses the main database, a mutex > associated with the in-memory cache of the main database will be held. > > Dan. > > > > On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote: > >> Hi, >> >> I'm using SQLite latest version (3.6.16) with shared-cache enable in >> a process that has around 5 threads. Database connections for each >> thread are created with the same main database file. After that, each >> connection is attached to a particular database file (one for each >> thread) using the same schema name. Final structure is similar to the >> following: >> >> Main database file: main.db >> Thread-1 database file: thread1.db >> Thread-2 database file: thread2.db >> Thread-3 database file: thread3.db >> Thread-4 database file: thread4.db >> Thread-5 database file: thread5.db >> >> Thread-1 connection is opened with the main.db file and attaches the >> thread1.db as "extradb" schema name; >> Thread-2 connection is opened with the main.db file and attaches the >> thread2.db as "extradb" schema name; >> Thread-3 connection is opened with the main.db file and attaches the >> thread3.db as "extradb" schema name; >> Thread-4 connection is opened with the main.db file and attaches the >> thread4.db as "extradb" schema name; >> Thread-5 connection is opened with the main.db file and attaches the >> thread5.db as "extradb" schema name; >> >> Every SQL statement submitted to the process and passed to one of >> these threads can read global informations maintained in the main.db >> database file ("main" schema) and write/read particular informations >> in the "extradb" schema in such a way that one thread does not need >> to >> wait for another thread to write its information, since each thread >> has the "extradb" schema attached to a particular database file. >> >> Shared-cache is used for 2 reasons: >> - to improve main.db database file data access; and, >> - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to >> avoid many SQLITE_LOCKED errors based on the code provided in >> http://www.sqlite.org/unlock_notify.html >> . >> >> In my understanding, the expected behavior should be: >> - If a SQL statement with only read (heavy) operations is passed to >> Thread-1; and, >> - Another SQL statement with a write (and some reads) operations is >> passed to Thread-3; >> - Both should run in parallel in a multi-core system. >> >> But, it seems that the Thread-3 is waiting for the Thread-1 to >> finish >> its work before continue. >> This behavior is turning the solution into a non-scalable solution. >> >> As far as I could debug (and understand) using Visual Studio 2005, >> it >> seems that Thread-3 (in the above example) is waiting in for a lock >> in >> the sqlite3BtreeEnterAll function. See the piece of the call stack >> below: >> >>> sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8) Line 15159 >> sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8) Line >> 36706 + 0x11 bytes >> sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const >> char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1, >> sqlite3_stmt * * ppStmt=0x, const char * * pzTail=0x0965f63c) >> Line 9672 >> sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char >> * zSql=0x00c0, int nBytes=180, sqlite3_stmt * * >> ppStmt=0x013a9094, >> const char * * pzTail=0x0965f63c) Line 9747 + 0x1f bytes >> apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 * >> db=0x, const char * zSql=, int nSql=, sqlite3_stmt * * >> ppStmt=0x, const char * * pz=0x) Line 247 + 0x10 >> bytes >> apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t * >> pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * * >> results=0x0965f688, const char * query=0x00c0, int seek=0) Line >> 307 + 0x33 bytes >> libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t * >> driver=0x00a66270, apr_pool_t * pool=0x013a9050, apr_dbd_t * >> handle=0x029cc040, apr_dbd_results_t * * res=0x0965f688, const char * >> statement=0x00c0, int random=0) Line 319 + 0x22 bytes >> >> While Thread-1 call stack looks like this: >> >>> sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x1c471318) Line 52862 >> sqlite3.dll!sqlite3Step(Vdbe * p=0x) Line 49388 + 0x7 bytes >> sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=) Line 49449 + 0x7 >> bytes >>
Re: [sqlite] SQLite lock behavior in shared-cache mode
While compiling any statement (sqlite3_prepare_v2()), or while stepping (sqlite3_step()) a statement that accesses the main database, a mutex associated with the in-memory cache of the main database will be held. Dan. On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote: > Hi, > > I'm using SQLite latest version (3.6.16) with shared-cache enable in > a process that has around 5 threads. Database connections for each > thread are created with the same main database file. After that, each > connection is attached to a particular database file (one for each > thread) using the same schema name. Final structure is similar to the > following: > > Main database file: main.db > Thread-1 database file: thread1.db > Thread-2 database file: thread2.db > Thread-3 database file: thread3.db > Thread-4 database file: thread4.db > Thread-5 database file: thread5.db > > Thread-1 connection is opened with the main.db file and attaches the > thread1.db as "extradb" schema name; > Thread-2 connection is opened with the main.db file and attaches the > thread2.db as "extradb" schema name; > Thread-3 connection is opened with the main.db file and attaches the > thread3.db as "extradb" schema name; > Thread-4 connection is opened with the main.db file and attaches the > thread4.db as "extradb" schema name; > Thread-5 connection is opened with the main.db file and attaches the > thread5.db as "extradb" schema name; > > Every SQL statement submitted to the process and passed to one of > these threads can read global informations maintained in the main.db > database file ("main" schema) and write/read particular informations > in the "extradb" schema in such a way that one thread does not need to > wait for another thread to write its information, since each thread > has the "extradb" schema attached to a particular database file. > > Shared-cache is used for 2 reasons: > - to improve main.db database file data access; and, > - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to > avoid many SQLITE_LOCKED errors based on the code provided in > http://www.sqlite.org/unlock_notify.html > . > > In my understanding, the expected behavior should be: > - If a SQL statement with only read (heavy) operations is passed to > Thread-1; and, > - Another SQL statement with a write (and some reads) operations is > passed to Thread-3; > - Both should run in parallel in a multi-core system. > > But, it seems that the Thread-3 is waiting for the Thread-1 to finish > its work before continue. > This behavior is turning the solution into a non-scalable solution. > > As far as I could debug (and understand) using Visual Studio 2005, it > seems that Thread-3 (in the above example) is waiting in for a lock in > the sqlite3BtreeEnterAll function. See the piece of the call stack > below: > >> sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8) Line 15159 > sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8) Line > 36706 + 0x11 bytes > sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const > char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1, > sqlite3_stmt * * ppStmt=0x, const char * * pzTail=0x0965f63c) > Line 9672 > sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char > * zSql=0x00c0, int nBytes=180, sqlite3_stmt * * ppStmt=0x013a9094, > const char * * pzTail=0x0965f63c) Line 9747 + 0x1f bytes > apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 * > db=0x, const char * zSql=, int nSql=, sqlite3_stmt * * > ppStmt=0x, const char * * pz=0x) Line 247 + 0x10 > bytes > apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t * > pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * * > results=0x0965f688, const char * query=0x00c0, int seek=0) Line > 307 + 0x33 bytes > libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t * > driver=0x00a66270, apr_pool_t * pool=0x013a9050, apr_dbd_t * > handle=0x029cc040, apr_dbd_results_t * * res=0x0965f688, const char * > statement=0x00c0, int random=0) Line 319 + 0x22 bytes > > While Thread-1 call stack looks like this: > >> sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x1c471318) Line 52862 > sqlite3.dll!sqlite3Step(Vdbe * p=0x) Line 49388 + 0x7 bytes > sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=) Line 49449 + 0x7 > bytes > apr_dbd_sqlite3.dll!sqlite3_blocking_step(sqlite3_stmt * > pStmt=0x) Line 220 + 0x6 bytes > apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t * > pool=0x01340958, apr_dbd_t * sql=0x012e1e28, apr_dbd_results_t * * > results=0x09b5f688, const char * query=0x0139c1b8, int seek=0) Line > 324 + 0x5 bytes > libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t * > driver=0x00a66270, apr_pool_t * pool=0x01340958, apr_dbd_t * > handle=0x012e1e28,