SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

Ralf Junker
Hello,

I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.

I execute the following psydocode, all with same DB handle:

* In application's main thread: sqlite3_open
* Create a new thread
* In new thread: sqlite3_close

This creates an access violation in pager.c, lines 2065 to 2076:

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
  /* Remove the pager from the linked list of pagers starting at
  ** ThreadData.pPager if memory-management is enabled.
  */
  if( pPager==pTsd->pPager ){
    pTsd->pPager = pPager->pNext;
  }else{
    Pager *pTmp;
    for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
    pTmp->pNext = pPager->pNext;
  }
#endif

While I understand from the FAQ that it might be problematic to use more than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite should cause an AV in this case?

I even found that other SQL instructions, like INSERT, work fine when called from the 2nd thread.

Could anybody help, please?

Thanks & regards,

Ralf

Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

Ludovic Ferrandis-2
You have to call open & close in the same thread, and in each thread if
needed.

" The returned sqlite3* can only be used in the same thread in which it was
created. It is an error to call
sqlite3_open<http://www.sqlite.org/capi3ref.html#sqlite3_open>()
in one thread then pass the resulting database handle off to another thread
to use. This restriction is due to goofy design decisions (bugs?) in the way
some threading implementations interact with file locks."

<http://www.sqlite.org/capi3ref.html#sqlite3_open>

Hope this help

Ludovic

On 3/21/06, Ralf Junker <[hidden email]> wrote:

>
> Hello,
>
> I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.
>
> I execute the following psydocode, all with same DB handle:
>
> * In application's main thread: sqlite3_open
> * Create a new thread
> * In new thread: sqlite3_close
>
> This creates an access violation in pager.c, lines 2065 to 2076:
>
> #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
>   /* Remove the pager from the linked list of pagers starting at
>   ** ThreadData.pPager if memory-management is enabled.
>   */
>   if( pPager==pTsd->pPager ){
>     pTsd->pPager = pPager->pNext;
>   }else{
>     Pager *pTmp;
>     for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
>     pTmp->pNext = pPager->pNext;
>   }
> #endif
>
> While I understand from the FAQ that it might be problematic to use more
> than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite
> should cause an AV in this case?
>
> I even found that other SQL instructions, like INSERT, work fine when
> called from the 2nd thread.
>
> Could anybody help, please?
>
> Thanks & regards,
>
> Ralf
>
>
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

Rob Lohman
In reply to this post by Ralf Junker
Hi Ralf,

If I remember correctly you cannot use a SQLite database handle
across threads. Each thread will need to open (and close) the
database itself.

Cheers,

Rob

----- Original Message -----
From: "Ralf Junker" <[hidden email]>
To: <[hidden email]>
Sent: Tuesday, March 21, 2006 12:02 PM
Subject: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB
in different threads


> Hello,
>
> I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.
>
> I execute the following psydocode, all with same DB handle:
>
> * In application's main thread: sqlite3_open
> * Create a new thread
> * In new thread: sqlite3_close
>
> This creates an access violation in pager.c, lines 2065 to 2076:
>
> #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
>  /* Remove the pager from the linked list of pagers starting at
>  ** ThreadData.pPager if memory-management is enabled.
>  */
>  if( pPager==pTsd->pPager ){
>    pTsd->pPager = pPager->pNext;
>  }else{
>    Pager *pTmp;
>    for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
>    pTmp->pNext = pPager->pNext;
>  }
> #endif
>
> While I understand from the FAQ that it might be problematic to use more
> than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite
> should cause an AV in this case?
>
> I even found that other SQL instructions, like INSERT, work fine when
> called from the 2nd thread.
>
> Could anybody help, please?
>
> Thanks & regards,
>
> Ralf
>

Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

Ralf Junker
In reply to this post by Ludovic Ferrandis-2
Hello Ludovic Ferrandis,

thanks, but this is not quite true for the latest version:

http://www.sqlite.org/faq.html#q8 reads:

"The restriction on moving database connections across threads was relaxed somewhat in version 3.3.1. With that and subsequent versions, it is safe to move a connection handle across threads as long as the connection is not holding any fcntl() locks. You can safely assume that no locks are being held if no transaction is pending and all statements have been finalized."

Opening a DB in one thread and closing it in another works fine without SQLITE_ENABLE_MEMORY_MANAGEMENT, so I assume that this is causing me trouble. Especially, since SQLite causes an AV which crashes the application instead of issuing an error message or just silently do nothing about the shared memory.

Ralf

>You have to call open & close in the same thread, and in each thread if
>needed.

Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

Ralf Junker
In reply to this post by Rob Lohman
Hello Rob Lohman,

SQLite is multithreaded since 3.3.1. Still, threre are restrictions:

"When shared-cache mode is enabled, a database connection may only be used by the thread that called sqlite3_open() to create it. If another thread attempts to use the database connection, in most cases an SQLITE_MISUSE error is returned. However this is not guaranteed and programs should not depend on this behaviour, in some cases a segfault may result." [http://www.sqlite.org/sharedcache.html]

However, my example does NOT enable shared-cache mode and should therefor work fine IMO.

At least, SQLite should not cause the Access Violation. Or is this a bug?

Regards,

Ralf

>If I remember correctly you cannot use a SQLite database handle
>across threads. Each thread will need to open (and close) the
>database itself.

Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

D. Richard Hipp
In reply to this post by Ralf Junker
Ralf Junker <[hidden email]> wrote:
> Hello,
>
> I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.
>

Why?  What do you hope to accomplish by using MEMORY_MANAGEMENT on
Win32?  MM is designed for use on embedded devices with very tight
memory restrictions.  It is not intended for use on workstations,
which is why it is off by default.

> I execute the following psydocode, all with same DB handle:
>
> * In application's main thread: sqlite3_open
> * Create a new thread
> * In new thread: sqlite3_close

When SQLITE_ENABLE_MEMORY_MANAGEMENT is turned on, it is an error
to move a database connection from one thread to another.

>
> This creates an access violation in pager.c, lines 2065 to 2076:
>
> #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
>   /* Remove the pager from the linked list of pagers starting at
>   ** ThreadData.pPager if memory-management is enabled.
>   */
>   if( pPager==pTsd->pPager ){
>     pTsd->pPager = pPager->pNext;
>   }else{
>     Pager *pTmp;
>     for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
>     pTmp->pNext = pPager->pNext;
>   }
> #endif

And here is one reason why it is an error:  When MM is enabled,
SQLite keeps a linked list of every pager structure used in each
thread.  (It has to do this in order to support some features of MM.)
When you close a connection it must unlink that pager from the
linked list.  But it can only do so if the connection is closed
from the same thread in which it was created.


>
> While I understand from the FAQ that it might be problematic to use more than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite should cause an AV in this case?

Yes it should.

>
> I even found that other SQL instructions, like INSERT, work fine when called from the 2nd thread.

You were lucky.



Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

Ralf Junker
Hello Richard,

in case of stating the obvious: Thanks for your answer! It is always my pleasure reading your explanations on SQLite: Quick, precise, and right up to the point!

Ralf

>> While I understand from the FAQ that it might be problematic to use more than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite should cause an AV in this case?
>
>Yes it should.