Nested transactions in H2

[Aaron Digulla]

Hello,

I'm trying to get this test case to work:

Test setup:

Database with a single table with one row. The table just contains a single text column.

Test case:

1. Create transaction
2. Update the text in the single data row to foo
3. Create a new thread
   1. Create inner transaction
   2. Update the text in the single data row to bar
   3. Commit the inner transactions
4. Commit the outer transaction
5. Read the value

With 1.3, this worked and returned "bar". With 1.4, I get error CONCURRENT_UPDATE_1 90131

http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90131

Is there a way to make nested transactions which update the same rows work with 1.4? I tried MVCC=true and false, LOCK_MODE=3 but to no avail.

[Noel Grandin]

Can you create a small self-contained test-case?

[Aaron Digulla]

On Thursday, August 21, 2014 11:00:14 AM UTC+2, Noel Grandin wrote:

Can you create a small self-contained test-case?

Yes, but it'll take a while. 

[Aaron Digulla]

I haven't forgotten about this, I'm just too busy to work on it :-)

[Aaron Digulla]

That took some time but I have now a JUnit test where I tried various ways to access an H2 database from multiple threads.

You can find the code here: https://gist.github.com/digulla/edb8095b7cfa63da3a1e

What I find odd is that my application can do it ( think?), but the test case always locks up. But I can't find a difference between my application and the test.

So my question is: How can I make the testNestedTransactions* tests pass?

[Noel Grandin]

Hi

You are running the nested transaction in a second connection, which is never going to work.

Regards, Noel

[Noel Grandin]

Also, your inner transaction is trying to update a row that does not exist, since it has never been inserted into the table.

[Aaron Digulla]

On Friday, January 16, 2015 at 9:13:15 AM UTC+1, Noel Grandin wrote:

You are running the nested transaction in a second connection, which is never going to work.

Why not? Since I have two threads, I need two connections (JDBC connections must not be shared between threads).

What is the correct way to update a single table in H2 from two threads?

[Aaron Digulla]

On Friday, January 16, 2015 at 11:14:43 AM UTC+1, Noel Grandin wrote:

Also, your inner transaction is trying to update a row that does not exist, since it has never been inserted into the table.

The row was inserted in insertDemoRow() before the two threads are started. 

[Aaron Digulla]

Update: I've changed the test slightly.

- insertDemoRow() now creates two rows

- The two connections try to update different rows

Before the change, the outer transaction tried to update an existing row while the inner TX updated a non-existing row.

With the change, testNestedTransactionsMVCC() passes. The other nested transactions still fail.

So it seems that you need two connections, different threads, you need to update different objects, they have to exist and you must use "MVCC=TRUE" as connection option.

Does that sound right?

[Noel Grandin]

Well, yes, if you're going to be silly and hold the transaction open
for longer than your lock timeout, that is essentially correct.

But in the words of a famous doctor joke "don't do that"

[Aaron Digulla]

What is the correct code for an application where several users can make concurrent changes to the database? 

[Noel Grandin]

On Tue, Jan 20, 2015 at 6:35 PM, Aaron Digulla <adig...@gmail.com> wrote:
> What is the correct code for an application where several users can make
> concurrent changes to the database?
>

Regular concurrent updates are not the problem, that should work just fine.
Your original test case was this:

Create transaction
Update the text in the single data row to foo
Create a new thread

Create inner transaction
Update the text in the single data row to bar
Commit the inner transactions

Commit the outer transaction
Read the value

And that is going to be a problem on any database.
The point is that you should not be spawning a child thread and
waiting for it while you have an open transaction. You should commit
the transaction before spawning the child thread.
This is because while a transaction is open that table is locked.

Regards, Noel

[Aaron Digulla]

On Wednesday, January 21, 2015 at 2:20:24 AM UTC+1, Noel Grandin wrote:

The point is that you should not be spawning a child thread and
waiting for it while you have an open transaction. You should commit
the transaction before spawning the child thread.
This is because while a transaction is open that table is locked.

Sounds like a use case for Spring's  Propagation.NEVER:

* Execute non-transactionally, throw an exception if a transaction exists.

I begin to understand what you say and why it's a problem.

Now in my case, I need to roll back the outer transaction when the inner one fails.

I guess there is no good solution besides two-phase commits ... I mean there is no good solution ;-)

Regards, Aaron

[Noel Grandin]

One simple thing you could try is just passing the connection into the
child thread so that the parent and child use the same connection.

[Aaron Digulla]

On Thursday, January 22, 2015 at 8:09:07 AM UTC+1, Noel Grandin wrote:

One simple thing you could try is just passing the connection into the
child thread so that the parent and child use the same connection.

With the Spring transaction manager, this isn't simple :-( All the connections are hidden somewhere in a thread local and there is no setter. 

[Noel Grandin]

Instead of spawning a thread and waiting for it, why not just make a
method call?
Then you'd be using the same connection by default.

[Aaron Digulla]

We have to scenarios:

- A "sudo" like functionality

- Background processes

In the sudo case, we make sure thread locals can't leak from one user to the other. We also have a couple of Spring beans which are "user" scoped.

The background process starts a thread, waits for a few moments. If the thread returns in that time, the results are processed immediately. The most common case here is an error. But if the thread doesn't return quickly, we need to save the current state and prepare everything that the thread can later save its results and the UI can tell the user that a background job is working for them.

In both cases, the new threads and the foreground connection usually don't access the same objects. But if it happens, then we get deadlocks.

I was also looking into a way to have Spring pass the current connection to the new thread (since the parent thread is always waiting) but I can't figure out how.

Regards,