23

I am using H2 for database management, and this is what I would like to do:

I would like to allow multiple users to access a database at the same time. I've read a bit about "MULTI_THREADED=TRUE", "LOCK_FILE=NO", and "AUTO_SERVER=TRUE". I've also read that "LOCK_FILE=NO" can be dangerous, because it can corrupt the database. I definitely would not want this, so I'm assuming that is a bad way to go. I've also tried to close the connection immediately after a record is accessed, whether it is being read from or written to. So far, nothing seems to work. The application is not allowing me to read from or write to the database if the database has been connected to in a separate instance of the application (ex: on another computer). Once I completely close the application on one computer, I am able to access the database records.

How do I allow multiple users to connect to the H2 database at the same time without compromising the safety of the database?

Ryan
  • 511
  • 1
  • 8
  • 18
  • Funny how the first answer you get is exactly what I've been saying... – MadProgrammer Nov 04 '14 at 20:25
  • 2
    Not exactly. None of your responses were very direct or helpful. – Ryan Nov 04 '14 at 21:40
  • So "use the server mode" wasn't helpful...shame – MadProgrammer Nov 04 '14 at 22:46
  • Okay, I don't think using "AUTO_SERVER=TRUE" alone fixed the problem. Should that alone have fixed the problem? Right now, I have it set up to immediately commit and close the connection after a query is made, so the lock file is created and then deleted. Is this an efficient way to allow multiple users to access the database? – Ryan Nov 04 '14 at 23:48

2 Answers2

25

It looks like you are using H2 in embedded mode, which only allows one database connection at a time. See connection modes in the documentation for details.

If you need support for multiple connections, including from multiple application instances, then you need to start H2 in server mode instead and use the appropriate connection URLs for this mode.

Lolo
  • 4,277
  • 2
  • 25
  • 24
  • 1
    So, is there a way to use "AUTO_SERVER=TRUE" to do this? Would that make it mixed? – Ryan Nov 04 '14 at 21:38
  • And, if I make the Auto Server true, would that allow multiple connections? – Ryan Nov 04 '14 at 21:39
  • 2
    The [automatic mixed mode](http://www.h2database.com/html/features.html#auto_mixed_mode) (i.e. AUTO_SERVER=TRUE) allows multiple concurrent connections to the database. However, I wouldn't recommend it, due to the constraints that the first connection starts the server and when that connection is closed, so is the server and all remote connections are therefore closed, and pending transactions will be rolled back. – Lolo Nov 05 '14 at 06:29
  • But if I use "connection.commit()", I won't have that problem, correct? – Ryan Nov 05 '14 at 07:12
  • 2
    The documentation states that in embedded mode there is no limit on the number of open connections. – Stefan Neuhaus Feb 19 '17 at 20:13
  • 1
    @StefanNeuhaus but not by multiple users – user305224 Mar 15 '18 at 08:12
7

AUTO_SERVER=TRUE allows multiple connections mode.

Jackkobec
  • 5,889
  • 34
  • 34