CODEWITHSUNDEEP
sqliteconcurrencyhsqldbh2javadb
user4949624
user4949624

Reputation: 61

Concurrency in embedded (in-proces) databases H2, Hsqldb, Apache Derby, SQLite (JDBC)

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??

Upvotes: 4

Views: 3031

Answers (3)

Arioch 'The
Arioch 'The

Reputation: 16045

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.

Upvotes: 0

Arioch 'The
Arioch 'The

Reputation: 16045

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.

Upvotes: 1

fredt
fredt

Reputation: 24362

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.

Upvotes: 1

Related Questions