multithreaded app with in-memory database and transactions

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

multithreaded app with in-memory database and transactions

Wim Hoekman
I have an app which is multithreaded. Sometimes during lengty inserts a
different thread (which only reads the database) sees part of the
updated data.

This would be solved by using transactions. However, during the
transaction the "reading" thread gets a 'database table is locked' error.

In my case, transactions work fine when using on-disk database file, but
not when using an in-memory database.

In my code, I open the database twice, so I have two separate database
connections: one for updating, one for reading.

I've tried several ways of opening the in-memory database:

1) "file:memdb?cache=shared&mode=memory",
SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
                does not work, 'database table is locked' error
               
2) "file:memdb?cache=private&mode=memory",
SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
        does not work, "different" databases
        data added via one db connection is not visible when querying via other
db conncection

3) "file:filedb?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
        does not work, 'database table is locked' error
       
4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
        works, but is not in memory

5) "file:filedb", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
does not work, 'database table is locked' error
       
        (because I called sqlite3_enable_shared_cache( 1 )! )

6) "file::memory:?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
        same behaviour as 1)

7) "file::memory:?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
        same behaviour as 2)

It seems I can not get 2 database connections to the same in-memory
database, without using a shared cache.
And having a shared cache causes 'database table is locked' errors.

Did I miss something, or is multithreaded transactions with an in-memory
database not supported?
Any hints or tips to get this working are appreciated.

Regards,

Wim.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multithreaded app with in-memory database and transactions

Simon Slavin-3
On 13 Feb 2020, at 6:44pm, Wim Hoekman <[hidden email]> wrote:

> 4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> works, but is not in memory

Can you try

"file:filedb", SQLITE_OPEN_MEMORY|SQLITE_OPEN_PRIVATECACHE

?  If that doesn't work, for the filename try ":memory:" or "file:memory:" in the quotes instead of a filename.

For reference:

<https://sqlite.org/c3ref/c_open_autoproxy.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multithreaded app with in-memory database and transactions

Keith Medcalf
In reply to this post by Wim Hoekman

Correct.  "memory" databases can only be shared between connections in the same process, and then only by the sharedcache method.  In effect, a "memory" database is nothing more than a cache, and sharing it between connections means sharing the cache.  cache=private uses a separate cache for the connection and therefore (by definition) a different "memory" database.

You could, of course, use an "on disk" database where the database resides on a memory resident filesystem.  On Linux you would simply mount another filesystem using tmpfs and put your "on disk" database there.  Delete the "file" off the tmpfs filesystem when you are done with it.  (Or just use /tmp if it is mounted using tmpfs which it usually is.  Just beware that everyone has access to /tmp and if you mount your own tmpfs at a different location you can control the access permissions).

On Windows the closest thing I know of that can create an autosizing ramdisk is http://winramtech.atwebpages.com/RAMDriv/ramdriv.htm though I have not tried it.

For other OS's, Google is your friend!

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Wim Hoekman
>Sent: Thursday, 13 February, 2020 11:44
>To: [hidden email]
>Subject: [sqlite] multithreaded app with in-memory database and
>transactions
>
>I have an app which is multithreaded. Sometimes during lengty inserts a
>different thread (which only reads the database) sees part of the
>updated data.
>
>This would be solved by using transactions. However, during the
>transaction the "reading" thread gets a 'database table is locked' error.
>
>In my case, transactions work fine when using on-disk database file, but
>not when using an in-memory database.
>
>In my code, I open the database twice, so I have two separate database
>connections: one for updating, one for reading.
>
>I've tried several ways of opening the in-memory database:
>
>1) "file:memdb?cache=shared&mode=memory",
>SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> does not work, 'database table is locked' error
>
>2) "file:memdb?cache=private&mode=memory",
>SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> does not work, "different" databases
> data added via one db connection is not visible when querying via
>other
>db conncection
>
>3) "file:filedb?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> does not work, 'database table is locked' error
>
>4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> works, but is not in memory
>
>5) "file:filedb", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>does not work, 'database table is locked' error
>
> (because I called sqlite3_enable_shared_cache( 1 )! )
>
>6) "file::memory:?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> same behaviour as 1)
>
>7) "file::memory:?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> same behaviour as 2)
>
>It seems I can not get 2 database connections to the same in-memory
>database, without using a shared cache.
>And having a shared cache causes 'database table is locked' errors.
>
>Did I miss something, or is multithreaded transactions with an in-memory
>database not supported?
>Any hints or tips to get this working are appreciated.
>
>Regards,
>
>Wim.
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multithreaded app with in-memory database and transactions

Jim "Jed" Dodgen
I have placed databases on/in /dev/shm and shared them across both threads
and processes.

Jim "Jed" Dodgen
[hidden email]


On Thu, Feb 13, 2020 at 2:38 PM Keith Medcalf <[hidden email]> wrote:

>
> Correct.  "memory" databases can only be shared between connections in the
> same process, and then only by the sharedcache method.  In effect, a
> "memory" database is nothing more than a cache, and sharing it between
> connections means sharing the cache.  cache=private uses a separate cache
> for the connection and therefore (by definition) a different "memory"
> database.
>
> You could, of course, use an "on disk" database where the database resides
> on a memory resident filesystem.  On Linux you would simply mount another
> filesystem using tmpfs and put your "on disk" database there.  Delete the
> "file" off the tmpfs filesystem when you are done with it.  (Or just use
> /tmp if it is mounted using tmpfs which it usually is.  Just beware that
> everyone has access to /tmp and if you mount your own tmpfs at a different
> location you can control the access permissions).
>
> On Windows the closest thing I know of that can create an autosizing
> ramdisk is http://winramtech.atwebpages.com/RAMDriv/ramdriv.htm though I
> have not tried it.
>
> For other OS's, Google is your friend!
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users <[hidden email]> On
> >Behalf Of Wim Hoekman
> >Sent: Thursday, 13 February, 2020 11:44
> >To: [hidden email]
> >Subject: [sqlite] multithreaded app with in-memory database and
> >transactions
> >
> >I have an app which is multithreaded. Sometimes during lengty inserts a
> >different thread (which only reads the database) sees part of the
> >updated data.
> >
> >This would be solved by using transactions. However, during the
> >transaction the "reading" thread gets a 'database table is locked' error.
> >
> >In my case, transactions work fine when using on-disk database file, but
> >not when using an in-memory database.
> >
> >In my code, I open the database twice, so I have two separate database
> >connections: one for updating, one for reading.
> >
> >I've tried several ways of opening the in-memory database:
> >
> >1) "file:memdb?cache=shared&mode=memory",
> >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> >               does not work, 'database table is locked' error
> >
> >2) "file:memdb?cache=private&mode=memory",
> >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> >       does not work, "different" databases
> >       data added via one db connection is not visible when querying via
> >other
> >db conncection
> >
> >3) "file:filedb?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> >       does not work, 'database table is locked' error
> >
> >4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> >       works, but is not in memory
> >
> >5) "file:filedb", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> >does not work, 'database table is locked' error
> >
> >       (because I called sqlite3_enable_shared_cache( 1 )! )
> >
> >6) "file::memory:?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> >       same behaviour as 1)
> >
> >7) "file::memory:?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
> >       same behaviour as 2)
> >
> >It seems I can not get 2 database connections to the same in-memory
> >database, without using a shared cache.
> >And having a shared cache causes 'database table is locked' errors.
> >
> >Did I miss something, or is multithreaded transactions with an in-memory
> >database not supported?
> >Any hints or tips to get this working are appreciated.
> >
> >Regards,
> >
> >Wim.
> >
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multithreaded app with in-memory database and transactions

Keith Medcalf

On Thursday, 13 February, 2020 17:06, Jim Dodgen <[hidden email]> wrote:

>I have placed databases on/in /dev/shm and shared them across both
>threads and processes.

Yeah, /dev/shm is a pre-existing tmpfs filesystem, separate from the one mounted on /tmp.  I keep forgetting about that one ...

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multithreaded app with in-memory database and transactions

Jim "Jed" Dodgen
I have often wondered what the performance difference is between /dev/shm
and :memory: databases

Jim "Jed" Dodgen
[hidden email]


On Thu, Feb 13, 2020 at 4:48 PM Keith Medcalf <[hidden email]> wrote:

>
> On Thursday, 13 February, 2020 17:06, Jim Dodgen <[hidden email]>
> wrote:
>
> >I have placed databases on/in /dev/shm and shared them across both
> >threads and processes.
>
> Yeah, /dev/shm is a pre-existing tmpfs filesystem, separate from the one
> mounted on /tmp.  I keep forgetting about that one ...
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multithreaded app with in-memory database and transactions

Keith Medcalf

On Thursday, 13 February, 2020 17:58, Jim Dodgen <[hidden email]> wrote:

>I have often wondered what the performance difference is between /dev/shm
>and :memory: databases

Theoretically a :memory: database is faster than a /dev/shm stored database.  A :memory: database is purely in memory and has no extra connection cache (and no transactions to speak of).  A database in /dev/shm is just like an on-disk database, and the connection has its own memory cache, the difference being that there will never be a wait to flush the OS cache to disk (the file exists only in the OS file cache).  Using a /dev/shm file will still require file read/write because it is a file as far as the application is concerned.

A file on /dev/shm will release all its space back to the OS free pool when the file is deleted but will persist until reboot when closed by the application (and not deleted).  A :memory: database will release its memory back to the OS when it is detached/closed.  A database opened on a RAMDISK will behave like a database on /dev/shm except that deleting the file will not release the space allocated for the RAMDISK back to the OS since the RAMDISK usually permanently allocates virtual storage for its contents when created.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multithreaded app with in-memory database and transactions

Wim Hoekman
In reply to this post by Keith Medcalf
On 13-Feb-20 23:38, Keith Medcalf wrote:
>
> Correct.  "memory" databases can only be shared between connections in the same process, and then only by the sharedcache method.  In effect, a "memory" database is nothing more than a cache, and sharing it between connections means sharing the cache.  cache=private uses a separate cache for the connection and therefore (by definition) a different "memory" database.
>
> You could, of course, use an "on disk" database where the database resides on a memory resident filesystem.  On Linux you would simply mount another filesystem using tmpfs and put your "on disk" database there.  Delete the "file" off the tmpfs filesystem when you are done with it.  (Or just use /tmp if it is mounted using tmpfs which it usually is.  Just beware that everyone has access to /tmp and if you mount your own tmpfs at a different location you can control the access permissions).
>
> On Windows the closest thing I know of that can create an autosizing ramdisk is http://winramtech.atwebpages.com/RAMDriv/ramdriv.htm though I have not tried it.
>
> For other OS's, Google is your friend!
>

My application should run on Linux and Windows. Using tmpfs on Linux
could be doable, but having to setup a ramdisk on Windows would only
complicate matters...

Are there any plans on supporting multiple connections to in-memory
databases with separate caches?

If not, how much effort would be involved in getting this to be supported?

Regards,

Wim.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users