The meaning of MULTI_THREADED

[Rami Ojares]

Hi,

What does muti-threading inside database engine mean?
Surely it can't mean that only then more than one connection is allowed concurrently.
That seems to be the meaning of EXCLUSIVE
So does it mean that more than one processes (jvms) can access the same database file concurrently?
But that does not seem likely either because how could the engine ensure consistency of the database file then?

Well it seems to be somehow possible because that is what FILE_LOCK=SERIALIZED promises.
"This locking mode allows to open multiple connections to the same database.
The connections may be opened from multiple processes and from different computers.
When writing to the database, access is automatically synchronized internally."
Maybe the synchronization is done so that only one process has access to the file but for a short period only
so that it seems like concurrent access.

MVCC is said to conflict with MULTI_THREADED=TRUE but that does not explain what MULTI_THREADED means.

- rami

[Ryan How]

Hi,

By Default H2 only allows access for a single thread to the database at a time internally. It doesn't seem to create performance issues because usually the disk IO is the bottleneck. Don't get confused between concurrent transactions and multi-threaded access.

It only creates issues if you have long running queries (not transactions) because it will lock out other threads. The solution is to make sure no single query takes too long to run, so break them into smaller queries, or enable the multi-threaded access, which is not well tested apparently.

This is just from my understanding / experience.

Ryan

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Rami Ojares]

Thanks Ryan!

> Don't get confused between concurrent transactions and multi-threaded access

I am trying to but ain't easy :-)
So if I understand it correctly h2 manages multiple connections giving them each a slice of operation time in to the actual database (the file on the disk)
And if multi threaded is false only one operation can make changes to that file (transaction log being part of that file)
But if multi threaded is true then more than one connection can manipulate that file at the same time.
And the only reason to turn this on would be to increase the efficiency of the database, I assume.
Does this increase efficiency in practice?

- rami

[Ryan How]

Kind of. I guess each query gets it's turn in sequence. Someone else can probably answer that better.

I think with multi-threaded off, even read operations are done sequentially, not simultaneously.

Yes, if multi-threaded is on then multiple queries can be running at the same time.

Turning it on means that multiple queries can run at the same time, so long running queries won't block.

Don't know if it increases effeciency. I think the main bottleneck is disk IO, so probably wouldn't make a lot of difference.

I turned it on because I needed to still have queries running while the database was backing up, but I have had a few odd issue.

Don't take everything I say as gospel, it is just from my experience in using H2.


Hope this helps. Ryan

[Rami Ojares]

> Don't take everything I say as gospel, it is just from my experience
> in using H2.

All info is received with great appreciation.
I rarely mix gospel with tech talk :-)

- rami

[Wildam Martin]

On Thu, May 27, 2010 at 12:28, Ryan How <rh...@exemail.com.au> wrote:
> By Default H2 only allows access for a single thread to the database at a
> time internally.

But that does not mean that I can't have different threads running
using the database (e.g. one thread is doing a lot of small sized
updates while other threads perform different short queries), right?

--
Martin Wildam

[Rami Ojares]

Some observations about concurrency:

I progressed with Trigger's remove() method that I mentioned in my
earlier post.

Basically the situation was this.

When DROP TRIGGER is executed h2 call's the triggers remove() method.
In the remove method I tried to delete some data associated with the
trigger.
The data was stored in a table.

In order to delete the data I needed a connection so I had a line of code
Connection conn = DriverManager.getConnection(url);

h2 got completely locked up trying to execute that line.
My explanation is that h2 was thinking that DROP TRIGGER statement is
still being executed and
somehow a race condition happened so that getConnection method never
returned (I kept it on for over 12 hours and no timeouts occurred either).

This does raise an awful lot of questions but for starters:
"Why could I not at least get a connection to the database while DROP
TRIGGER was executing?"

I solved the issue by starting a new thread in the remove method that
does the cleanup.
And everything worked fine.

MULTI_THREADED=1 did not have any effect on this behaviour.
Based on these discussions one could assume that it would have something
to do with this
because DROP_TRIGGER could be seen in this case as a long running statement.

Maybe this is just a bug or maybe I still don't understand how
concurrency is handled in h2.

- rami

[Ryan How]

Yeah exactly. I probably didn't word it very well!.

[Ryan How]

I think someone more qualified than me will need to answer that!
Multithread=1 worked for me so backups didn't block access to the
database. (I don't know how safe it is, but haven't had any issues yet).
Haven't got a clue about the triggers. You could probably run it in a
debugger and see where it gets stuck?

[Thomas Mueller]

Hi,

> What does muti-threading inside database engine mean?

There is no documentation about that, except that it's not fully
tested. I will document it.

The default is "not multi-threaded" meaning only one statement can run
at any time (per database). There is a synchronized block around
running a statement. When multi-threaded is enabled, then the
synchronized statement is on the session (connection) instead of on
the database object.

The option is to increase concurrency, not throughput. The default
setting is usually not a problem except if you have long running
queries.

It's completely unrelated to EXCLUSIVE and FILE_LOCK=SERIALIZED.

> But if multi threaded is true then more than one connection can manipulate that file at the same time.

No. There is another synchronization on the database file and cache.
Also other areas are synchronized (search for for the "synchronized"
keyword in the source code).

> DROP TRIGGER is executed h2 call's the triggers remove() method....

If something is blocked, please provide the full thread dump (you get
that using kill -QUIT <pid> on Linus and Ctrl+Break on Windows).

Regards,
Thomas

[Rami]

Here is a minimal trigger that I create and then drop

CREATE TRIGGER T1 BEFORE INSERT ON PROGRAM
FOR EACH ROW CALL "DeadlockTrigger";
DROP TRIGGER T1;

/* START OF TRIGGER CLASS */

import java.sql.*;

public class DeadlockTrigger extends TriggerAdapter {

public void fire(Connection conn, ResultSet oldRow, ResultSet newRow)
throws SQLException {
// do nothing
}

public void remove() throws SQLException {

// This is the place where everything gets stuck

System.out.println("THIS I CAN SEE");
Connection conn =
DriverManager.getConnection("jdbc:h2:tcp://localhost/TV7");
System.out.println("THIS I CAN NOT");
}
}

Now when the trigger is dropped I try to create a connection to the database.
And that's when everything is deadlocked.

And here is the Thread Dump I get from the process.

2010-06-01 13:31:02
Full thread dump Java HotSpot(TM) Server VM (16.3-b01 mixed mode):

"H2 TCP Server (tcp://127.0.1.1:9092) thread" prio=10 tid=0x09cf1400 nid=0xc28
waiting for monitor entry [0x87ffe000]
java.lang.Thread.State: BLOCKED (on object monitor)

at org.h2.engine.Engine.openSession(Engine.java:72)

- waiting to lock <0xa74db9d8> (a org.h2.engine.Database)

at org.h2.engine.Engine.openSession(Engine.java:141)

- locked <0x8d135218> (a org.h2.engine.Engine)

at org.h2.engine.Engine.getSession(Engine.java:120)

at org.h2.server.TcpServerThread.run(TcpServerThread.java:118)

at java.lang.Thread.run(Thread.java:619)

"H2 Log Writer TV7" daemon prio=10 tid=0x09a82000 nid=0xc26 waiting for monitor
entry [0x8835c000]
java.lang.Thread.State: BLOCKED (on object monitor)

at org.h2.engine.Database.flush(Database.java:1701)

- waiting to lock <0xa74db9d8> (a org.h2.engine.Database)

at org.h2.store.WriterThread.run(WriterThread.java:85)

at java.lang.Thread.run(Thread.java:619)

"H2 File Lock Watchdog /opt/h2/TV7.lock.db" daemon prio=10 tid=0x09c3b400
nid=0xc25 waiting on condition [0x883ad000]
java.lang.Thread.State: TIMED_WAITING (sleeping)

at java.lang.Thread.sleep(Native Method)

at org.h2.store.FileLock.run(FileLock.java:482)

at java.lang.Thread.run(Thread.java:619)

"H2 TCP Server (tcp://127.0.1.1:9092) thread" prio=10 tid=0x099c8400 nid=0xc23
runnable [0x88583000]
java.lang.Thread.State: RUNNABLE

at java.net.SocketInputStream.socketRead0(Native Method)

at java.net.SocketInputStream.read(SocketInputStream.java:129)

at java.io.BufferedInputStream.fill(BufferedInputStream.java:218)

at java.io.BufferedInputStream.read(BufferedInputStream.java:237)

- locked <0xa6dca7c0> (a java.io.BufferedInputStream)

at java.io.DataInputStream.readInt(DataInputStream.java:370)

at org.h2.value.Transfer.readInt(Transfer.java:145)

at org.h2.engine.SessionRemote.done(SessionRemote.java:500)

at org.h2.engine.SessionRemote.initTransfer(SessionRemote.java:113)

at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:322)

at
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:221)

at org.h2.engine.SessionRemote.createSession(SessionRemote.java:215)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:111)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:95)

at org.h2.Driver.connect(Driver.java:58)

at java.sql.DriverManager.getConnection(DriverManager.java:582)

at java.sql.DriverManager.getConnection(DriverManager.java:207)

at archon.exodus.h2.DeadlockTrigger.remove(DeadlockTrigger.java:17)

at
org.h2.schema.TriggerObject.removeChildrenAndResources(TriggerObject.java:326)

at org.h2.engine.Database.removeSchemaObject(Database.java:1563)

- locked <0xa74db9d8> (a org.h2.engine.Database)

at org.h2.command.ddl.DropTrigger.update(DropTrigger.java:50)

at org.h2.command.CommandContainer.update(CommandContainer.java:70)
at org.h2.command.Command.executeUpdate(Command.java:198)
- locked <0xa74db9d8> (a org.h2.engine.Database)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:297)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:134)
at java.lang.Thread.run(Thread.java:619)

"DestroyJavaVM" prio=10 tid=0x884ad000 nid=0xc0f waiting on condition [0x00000000]
java.lang.Thread.State: RUNNABLE

"H2 TCP Server (tcp://127.0.1.1:9092)" prio=10 tid=0x884a8800 nid=0xc1d runnable
[0x883fe000]
java.lang.Thread.State: RUNNABLE
at java.net.PlainSocketImpl.socketAccept(Native Method)
at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:390)
- locked <0xa74d4060> (a java.net.SocksSocketImpl)
at java.net.ServerSocket.implAccept(ServerSocket.java:453)
at java.net.ServerSocket.accept(ServerSocket.java:421)
at org.h2.server.TcpServer.listen(TcpServer.java:213)
at org.h2.tools.Server.run(Server.java:428)
at java.lang.Thread.run(Thread.java:619)

"H2 Console Server (http://127.0.1.1:8082)" prio=10 tid=0x09a06000 nid=0xc1a
runnable [0x88672000]
java.lang.Thread.State: RUNNABLE
at java.net.PlainSocketImpl.socketAccept(Native Method)
at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:390)
- locked <0xa74db748> (a java.net.SocksSocketImpl)
at java.net.ServerSocket.implAccept(ServerSocket.java:453)
at java.net.ServerSocket.accept(ServerSocket.java:421)
at org.h2.server.web.WebServer.listen(WebServer.java:283)
at org.h2.tools.Server.run(Server.java:428)
at java.lang.Thread.run(Thread.java:619)

"Low Memory Detector" daemon prio=10 tid=0x099d7c00 nid=0xc18 runnable [0x00000000]
java.lang.Thread.State: RUNNABLE

"CompilerThread1" daemon prio=10 tid=0x099d6400 nid=0xc17 waiting on condition
[0x00000000]
java.lang.Thread.State: RUNNABLE

"CompilerThread0" daemon prio=10 tid=0x099d3000 nid=0xc16 waiting on condition
[0x00000000]
java.lang.Thread.State: RUNNABLE

"Signal Dispatcher" daemon prio=10 tid=0x099d1800 nid=0xc15 waiting on condition
[0x00000000]
java.lang.Thread.State: RUNNABLE

"Finalizer" daemon prio=10 tid=0x099bd000 nid=0xc14 in Object.wait() [0x88a7e000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
- waiting on <0x8d066a80> (a java.lang.ref.ReferenceQueue$Lock)
at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:118)
- locked <0x8d066a80> (a java.lang.ref.ReferenceQueue$Lock)
at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:134)
at java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:159)

"Reference Handler" daemon prio=10 tid=0x099bb800 nid=0xc13 in Object.wait()
[0x88acf000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
- waiting on <0x8d0662f0> (a java.lang.ref.Reference$Lock)
at java.lang.Object.wait(Object.java:485)
at java.lang.ref.Reference$ReferenceHandler.run(Reference.java:116)
- locked <0x8d0662f0> (a java.lang.ref.Reference$Lock)

"VM Thread" prio=10 tid=0x099b8c00 nid=0xc12 runnable

"GC task thread#0 (ParallelGC)" prio=10 tid=0x09945400 nid=0xc10 runnable

"GC task thread#1 (ParallelGC)" prio=10 tid=0x09946800 nid=0xc11 runnable

"VM Periodic Task Thread" prio=10 tid=0x099d9c00 nid=0xc19 waiting on condition

JNI global references: 858

Heap
PSYoungGen total 11520K, used 4829K [0xa6b10000, 0xa8190000, 0xb3860000)
eden space 9920K, 32% used [0xa6b10000,0xa6e3b6c8,0xa74c0000)
from space 1600K, 99% used [0xa74c0000,0xa764c030,0xa7650000)
to space 1600K, 0% used [0xa8000000,0xa8000000,0xa8190000)
PSOldGen total 26304K, used 1343K [0x8d060000, 0x8ea10000, 0xa6b10000)
object space 26304K, 5% used [0x8d060000,0x8d1aff08,0x8ea10000)
PSPermGen total 16384K, used 6358K [0x89060000, 0x8a060000, 0x8d060000)
object space 16384K, 38% used [0x89060000,0x89695a08,0x8a060000)

[Thomas Mueller]

Hi,

There is some kind of deadlock, but not a "Java level deadlock"
because you try to connect over TCP/IP. Why don't you connect in
embedded mode? I'm not saying this will work, but why do you use
TCP/IP to connect to a database within the same VM?

Why don't you keep the connection that was given to the trigger in the
'init' method?

Regards,
Thomas

[Rami Ojares]

Good points! But they raise a few questions that have been lying dormant
inside of me.

The relationship between a connection and transaction.

Can I have 2 different connections that participate in the same transaction?
My current understanding tells me that a transaction is the private
property of a connection.

So if 2 connections belong always to 2 separate transactions then it
follows that
if I would use the connection stored in the trigger instance in the
remove() method
it would not participate in the drop trigger transaction and thus even
if I would
throw an exception the drop trigger would succeed.

So what about the connection provided to the fire method.
Is that connection the same connection that did some action to the table
and thus caused the trigger to fire. To me it seems that this connection
belongs to the same
transaction that caused the trigger to fire. And it follows that trigger
should not commit that transaction
because the decision to commit should come from the user (not the
trigger). The documentation makes this also clear

> Committing or rolling back a transaction within a trigger is not
allowed, except for SELECT triggers.

But what happens if I do commit. For example if I create a table or some
other object it has a side
effect of committing a transaction.

> Why don't you keep the connection that was given to the trigger in
the 'init' method?

That�s a good idea. But whose privileges does that connection have?

- rami

[Thomas Mueller]

Hi,

> Can I have 2 different connections that participate in the same transaction?

No.

> So if 2 connections belong always to 2 separate transactions then it follows
> that
> if I would use the connection stored in the trigger instance in the remove()
> method
> it would not participate in the drop trigger transaction and thus even if I
> would
> throw an exception the drop trigger would succeed.

I don't know, you would need to try out.

> So what about the connection provided to the fire method.
> Is that connection the same connection that did some action to the table
> and thus caused the trigger to fire. To me it seems that this connection
> belongs to the same
> transaction that caused the trigger to fire.

Yes.

> But what happens if I do commit. For example if I create a table or some
> other object it has a side
> effect of committing a transaction.

Did you try it out?

> That´s a good idea. But whose privileges does that connection have?

I think the connection has admin rights, but I'm not sure.

Regards,
Thomas