4

Is it possible to use embedded databases concurrently (read/write) on the same machine (no server) with different processes. And what about concurrent read/writes in one multithreaded process?

Iam trying to find out how the concurrency is solved in HSQL,H2, Apache Derby and SQLite (via xerial JDBC) but i dont understand well those locking mechanism (MVCC, OCC etc) so i need someone to correct what i have found.

  1. H2: Only one process possibly multithreaded can be connected to the database.
    • Table level locking for writes read operations doesnt require lock and can be done concurrently with write (N readers one writer)
    • MVCC - table or row level locking for writes (N readers one writer??)
  2. Hsql: Seems completely same as H2 but there are some differences in MVCC mechanism
  3. Apache Derby (JavaDB): Only one process possibly multithreaded can be connected to the database.
    • Table level locking or row level locking for writes. Read operations doesnt require lock and can be done concurrently with writes (N readers one writer)
  4. SQLite (xerial JDBC): multiple processes can connect simultaneously but only one can do writting operations. (database locking).
    • when there is a write lock other processes or threads cant perform read and write operations??
user4949624
  • 51
  • 1
  • 2

3 Answers3

1

HSQLDB supports full multithreaded operation in all three transaction models that it supports (MVCC, LOCKS and MVLOCKS which is MVCC with table level locks).

If HSQLDB is run embedded without a server, by definition it is part of the process that embeds it. This process can use multiple threads to simultaneously access the database (for example a web server such as TOMEE which embeds HSQLDB). In the embedded-with-no-database-server deployment, no other process can possibly see the database.

In all modes N writers and M readers can work at the same time. The LOCKS mode locks tables for reads and writes, but the MVCC mode only locks rows that have been updated.

It is also possible to run a server in the process that embeds HSQLDB. Such a server allows access to other processes at the same time as the process that embeds the database.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • 1
    thx a lot. When the documentation says it is fully multithreaded does it mean that i just choose transaction model (MVCC, LOCKS, MVLOCKS) and DBMS ensures automatically that multithreaded application will not damage data integrity (in embedded mode)? And second if it is embedded (in-proc) but the datas can be persisted on disk so it uses database file locking for other processes? – user4949624 May 29 '15 at 06:48
  • 1
    Yes, you choose the TX model and it ensures data integrity, and yes, it prevents a second process to connect to database and damage the files. – fredt May 29 '15 at 20:10
1

There is also MySQL embedded, usually it is believed to behave lighter for read-often write-almost-never workload typical for WWW than full-scale OLTP-targeted servers.

https://github.com/vorburger/MariaDB4j

I don't know if this project or some other Java-ization is good or not.

Arioch 'The
  • 15,799
  • 35
  • 62
0

Firebird 2.5 might be an option for single process multiple threads approach

Is Firebird good embedded DB for ASP.NET? Which else?

But you would have to make several connections (one per thread)

Update: in Firebird 3 the embedded (in-process) server (or several, if there is several OS processes using the embedded server DLL/LIB.SO) and Classic-mode stand-alone server ( which basically now is the OS process loader of embedded dll) can work simultaneously with the same database file. The SuperServer-mode stand-alone FB3 though locks the database file for monopolistic access.

Community
  • 1
  • 1
Arioch 'The
  • 15,799
  • 35
  • 62
  • thx. Firebird embedded is an embedded server right? so the application connects to the embedded database and start another server process or embedded server means that server runs in the same process as the application? – user4949624 May 29 '15 at 06:56
  • Server is just a library you load into JVM – Arioch 'The May 29 '15 at 07:43
  • database is usually a single file, placed on the same computer the server (a standalone or embedded into your app - no difference) runs (local access required to make exclusive locks against other processes accessing it). engine is copy-on-write based, thus usually no locks happen while; working with data, but some commits might get rejected due to changed data committed from another thread. It is believed in a typical workload this optimistic no-lock approach causes less collisions then pessimistic lock-everything one – Arioch 'The May 29 '15 at 11:34