What is the proper way to close H2?

Question

This is related to this post.
I think I am having problem with H2 meaning that it does not close properly.
I suspect this since I see myDB.lock.db when I shutdown tomcat and the process does not stop.
I use Tomcat's connection pooling and the url to the database is:
url="jdbc:h2:file:/opt/myOrg/tomcat/webapps/MyApplication/db/myDatabase;SCHEMA=myschema"

From the doc close H2:

Usually, a database is closed when the last connection to it is closed.... By default, a database is closed when the last connection is closed. However, if it is never closed, the database is closed when the virtual machine exits normally, using a shutdown hook

I can not understand if I am doing something wrong.
Should I be forcing the database to close via a command? Is this the meaning of shutdown hook?
What am I doing wrong here?

Note:
I can not find in Google an example of how to close H2 properly (besides the statement that it closes automatically on last connection shutdown). Should I call SHUTDOWN myself? Is this the proper approach?
I already see votes to close the question but there has not been a reason or link on an example of what I am investigating

UPDATE:
After Joonas Pulakka answer some extra info:

From the javacore I got using a kill -3 I see the threads:

"H2 Log Writer MYAPPLICATION" J9VMThread:0x08DC6F00, j9thread_t:0x08C9B790, java/lang/Thread:0xE7206CC8, state:CW, prio=5 3XMTHREADINFO1 (native thread ID:0xA32, native priority:0x5, native policy:UNKNOWN) 3XMTHREADINFO2
(native stack address range from:0xE5E26000, to:0xE5E67000, size:0x41000) 3XMTHREADINFO3 Java callstack:
4XESTACKTRACE at java/lang/Object.wait(Native Method)
4XESTACKTRACE at java/lang/Object.wait(Object.java:196(Compiled Code)) 4XESTACKTRACE at org/h2/store/WriterThread.run(WriterThread.java:102)
4XESTACKTRACE at java/lang/Thread.run(Thread.java:736)

3XMTHREADINFO "pool-8-thread-1" J9VMThread:0x087C0200, j9thread_t:0x0840566C, java/lang/Thread:0xE79BFC80, state:P, prio=5
3XMTHREADINFO1 (native thread ID:0xE1A, native priority:0x5, native policy:UNKNOWN) 3XMTHREADINFO2
(native stack address range from:0xE5F69000, to:0xE5FAA000, size:0x41000) 3XMTHREADINFO3 Java callstack:
4XESTACKTRACE at sun/misc/Unsafe.park(Native Method)
4XESTACKTRACE at java/util/concurrent/locks/LockSupport.park(LockSupport.java:184(Compiled Code)) 4XESTACKTRACE at java/util/concurrent/locks/AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:1998(Compiled Code)) 4XESTACKTRACE at java/util/concurrent/LinkedBlockingQueue.take(LinkedBlockingQueue.java:413(Compiled Code)) 4XESTACKTRACE at java/util/concurrent/ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:958(Compiled Code)) 4XESTACKTRACE at java/util/concurrent/ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) 4XESTACKTRACE at java/lang/Thread.run(Thread.java:736)

3XMTHREADINFO "H2 File Lock Watchdog opt/myOrg/tomcat/webapps/MyApplication/db/myDatabase.lock.db" J9VMThread:0x08DC6900, j9thread_t:0x08C9BA24, ja
va/lang/Thread:0xE71E9018, state:CW, prio=9 3XMTHREADINFO1
(native thread ID:0xA30, native priority:0x9, native policy:UNKNOWN)
3XMTHREADINFO2 (native stack address range from:0xE5DBA000, to:0xE5DFB000, size:0x41000) 3XMTHREADINFO3 Java callstack: 4XESTACKTRACE at java/lang/Thread.sleep(Native Method) 4XESTACKTRACE
at java/lang/Thread.sleep(Thread.java:851(Compiled Code))
4XESTACKTRACE at org/h2/store/FileLock.run(FileLock.java:490) 4XESTACKTRACE
at java/lang/Thread.run(Thread.java:736)

3XMTHREADINFO "FileWatchdog" J9VMThread:0x087C0800, j9thread_t:0x08C9B4FC, java/lang/Thread:0xE715D878, state:CW, prio=5
3XMTHREADINFO1 (native thread ID:0xA2C, native priority:0x5, native policy:UNKNOWN) 3XMTHREADINFO2
(native stack address range from:0xE5E67000, to:0xE5EA8000, size:0x41000) 3XMTHREADINFO3 Java callstack:
4XESTACKTRACE at java/lang/Thread.sleep(Native Method) 4XESTACKTRACE at java/lang/Thread.sleep(Thread.java:851(Compiled Code)) 4XESTACKTRACE at org/apache/log4j/helpers/FileWatchdog.run(FileWatchdog.java:104)

possible duplicate of [Play! not shutting down H2 correctly](http://stackoverflow.com/questions/7182515/play-not-shutting-down-h2-correctly) — Mohamed Mansour, Apr 02 '12 at 07:33
@MohamedMansour:I have read that thread but does not help.1) I shutdown tomcat and not the application. So there should be no connections open 2)The answer seems to be a work-arround and I am trying to understand if the force of `shutdown` via an SQL command from a hook is actually the recomended approach.I can't tell from the doc — Jim, Apr 02 '12 at 07:41
H2 invokes [`addShutdownHook()`](http://docs.oracle.com/javase/6/docs/api/java/lang/Runtime.html#addShutdownHook%28java.lang.Thread%29) for you, using an instance of `org.h2.engine.DatabaseCloser`. — trashgod, Apr 02 '12 at 07:49
@trashgod:So I shouldn't be doing it myself e.g. have a hook and do an SQL shutdown?I can't find an example on this on Google or H2 docs on how H2 is supposed to be closed — Jim, Apr 02 '12 at 07:52
I don't understand "*I shutdown tomcat and the process does not stop*". Does this mean **Tomcat** does not properly shutdown? In that case, you should find out why Tomcat doesn't shutdown. — , Apr 02 '12 at 07:55
@a_horse_with_no_name:From catalina.out there is no error.And using `kill -3` on the running process I see from `ps -ef` I see that `H2` some how is active.Also I see the `db.lock` of `H2`.So I strongly suspect that this is `H2` issue and not Tomcat — Jim, Apr 02 '12 at 07:58

Joonas Pulakka · Accepted Answer · 2012-04-02T08:20:04.557

9

The documentation says that H2 db connection is closed when the virtual machine exits normally. And that's what it does. The shutdown hook is already there by default, you don't have to do anything. The shutdown hook is a perfectly valid way of closing resources that only need to be closed when quitting.

If you have .lock.db files remaining after shutdown, then the virtual machine didn't exit normally. You wrote that the process does not stop. You have to find the reason for that, because probably that's what also prevents the H2 shutdown hook from executing.

With big databases, closing could take some time. See with debugger (e.g. VisualVM) what threads remain active after you've invoked (Tomcat) shutdown.

There's on more possibility: file permissions are set so that H2 can create the lock files, but cannot delete them. If the OS prevents H2 from deleting its lock files, there's not much H2 could do about it.

edited Apr 02 '12 at 08:20

answered Apr 02 '12 at 07:57

Joonas Pulakka

36,252
29
106
169

Thank you.I was trying to investigate why the process does not stop (http://stackoverflow.com/questions/9971876/tomcat-doesnt-stop-how-can-i-debug-this) and I ended up here.Also in the folder there is `rw-` permission to the user and `r--` permission to the group.Do I need `x` permission to delete? – Jim Apr 02 '12 at 08:05
`x` is only needed for executing, not for deleting. But check that the directory doesn't have [sticky bit](http://www.thegeekstuff.com/2011/02/sticky-bit-on-directory-file/) set. – Joonas Pulakka Apr 02 '12 at 08:07
No there is no `t` in the directory.And tomcat is running using the user who is the owner of the files.Now I am stuck! – Jim Apr 02 '12 at 08:13
@Jim: No you're not stuck. You've just excluded the possibility that it's a permission issue (and even if it had been a permission issue, it would only have explained the `.lock.db` files, not why the process does not stop. (If H2 can't delete the files, then it can't, but it definitely won't block forever because of that.)). What threads keep doing something when you invoke Tomcat shutdown? – Joonas Pulakka Apr 02 '12 at 08:16
I have update OP on this.I see also some threads not mine but I think I should focus on mine.The parts that are suspicious to me is `H2` and `Log4j` I noticed.The other threads I am not sure – Jim Apr 02 '12 at 08:29
Any lines with `3XMTHREADBLOCK`? For example, `H2 Log Writer` is waiting on on something, but what exactly? You have to focus on whatever "networks" the threads form. – Joonas Pulakka Apr 02 '12 at 08:45
No `3XMTHREADBLOCK` of `THREADBLOCK` in javacore. I don't know how to find the "networks".Any hint? – Jim Apr 02 '12 at 08:51
The `at sun/misc/Unsafe.park(Native Method) 4XESTACKTRACE at java/util/concurrent/locks/LockSupport.park(LockSupport.java:184(Compiled Code))` concerns me.Could it be because I am doing a `shutdownNow` of an `ExecutorService` on a `shutdown` hook? – Jim Apr 02 '12 at 09:11
@Jim: I can't tell how your code reacts to `shutdown` vs. `shutdownNow`. How about just trying out? As for thread networks, I mean mutual dependencies: if a thread is blocked on a lock, who owns that lock? You find them by looking at the *whole* thread dump. You can't just look at your "own" threads, because they can be sharing resources with others: could be waiting for some other threads, or vice versa. No thread is island. – Joonas Pulakka Apr 02 '12 at 09:35

score 6 · Answer 2 · answered Oct 19 '16 at 15:24

You can execute the statement SHUTDOWN and then close the connection.

The SHUTDOWN command will make H2 free all resources related to the connection immediately. That will, for example, allow you to get rid of an embedded H2 database when you redeploy a web application.

score 6 · Answer 3 · edited May 23 '17 at 12:16

6

By looking at the DbStarter.contextDestroyed()'s code (thanks to Allan5's answer), here is the code that will work:

connection.createStatement().execute("SHUTDOWN");

So Aaron Digulla's answer was correct (even if not fully "copy/pastable").

Moreover, if you have started an H2 TCP server using server = Server.createTcpServer("-tcpAllowOthers"), you can stop it simply using server.stop().

edited May 23 '17 at 12:16

Community

1
1

answered Nov 23 '16 at 10:47

Anthony O.

22,041
18
107
163

score 3 · Answer 4 · answered Apr 27 '12 at 03:42

Not sure if this is relevant to your situation but have you tried adding a DBStarter listener?

http://www.h2database.com/html/tutorial.html, see the "Using a Servlet Listener to Start and Stop a Database" section.

The link suggests adding the following to web.xml:

<listener>
    <listener-class>org.h2.server.web.DbStarter</listener-class>
</listener>

Please see discussion here (admittedly from 2008 so may be out of date) - apparently the fix applies to both embedded and non-embedded instances: http://groups.google.com/group/h2-database/browse_thread/thread/eb609d58c96f4317

Alternatively how are you using the connections? Are you sure you are cleaning up the connections properly?

I was having issues before, in my case I was using the connection with a JPA EntityManager and I forgot to close the EntityManager instance after use, which resulted in some issues:

@PersistenceUnit(unitName="myEm")
private EntityManagerFactory emf;

public void doStuff() {
    EntityManager em = emf.createEntityManager();
    ...
    em.close(); // forgot this line
}

score 1 · Answer 5 · answered Apr 02 '12 at 07:33

1

No, a shutdown hook is simply a thread which runs when the JVM terminates, no matter if by returning from main(), calling System.exit(int) or throwing an exception. Only a JVM crash would avoid it. See Runtime.addShutdownHook(Thread).

answered Apr 02 '12 at 07:33

Christian Schlichtherle

3,125
1
23
47

I know what is a shutdown hook.I can't tell if this is the proper way to close H2 – Jim Apr 02 '12 at 07:39
@Jim: If you know what a shutdown hook is, why did you ask "*is this the meaning of shutdown hook?*" – Apr 02 '12 at 07:49
1

@a_horse_with_no_name:I meant in the context of shuting down `H2`.I can't find an example on showing that I should add a hook myself – Jim Apr 02 '12 at 07:51
What I get from this documentation is that the shutdown hook is provided by the framework and you don't have to add it. – Christian Schlichtherle Apr 02 '12 at 11:55

What is the proper way to close H2?

5 Answers5

Linked