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,
[sqlite] SQLite lock behavior in shared-cache mode
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, apr_dbd_results_t * * res=0x09b5f688, const char *
statement=0x0139c1b8, int random=0) Line 319 + 0x22 bytes
Additional information about the SQLite library in use:
- Version 3.6.16 (amalgamation source)
- Preprocessor definitions used to build the library:
-
