delay after rollback

[anli]

In my JDBC wrapper I use two constants - how many times to retry some tx code block, and how many msecs to wait after rollback before code block repeating. Can anybody suggest criterias for selecting these parameters - in general, and wrt H2 tx implementation in particular? Are there some H2 tunings influencing these params selecion?

I see, they depends on use case. Nevertheless I hope somebody has common useful tisps.

[Thomas Mueller]

Hi,

That's an interesting idea. What are the most common reasons for a
rollback (lock timeout,...)?

Regards,
Thomas

[anli]

At my test (plenty of threads try to modify very limited set of objects) the error I get is "deadlock detected".

I have found rather unexpected (by me) estimation of more or less optimal delay between attempts to execute tx code. It is about 800-1500mS, while I expected much less. As for amount of tx-code reexecution attempts - well, it depends on context. The more - the better, but, for example. can not be accepted for interective scenario.

Thomas, I didn't present any idea. What do you mean? :)

[Thomas Mueller]

Hi,

> I get is "deadlock detected".

Well, you seem to lock the tables in the wrong order. I would try to
avoid deadlocks if ever possible. See the dining philosophers problem
in Wikipedia. You could use "select ... for update".

> I didn't present any idea. What do you mean? :)

The idea to "tune" the values (retry count and delay).

Regards,
Thomas

[Andrew Gaydenko]

Thomas,hi!

On Tue, 9 Aug 2011 19:04:28 +0200
Thomas Mueller <thomas.to...@gmail.com> wrote:

> Well, you seem to lock the tables in the wrong order. I would try to
> avoid deadlocks if ever possible. See the dining philosophers problem
> in Wikipedia.

Yes, I'm aware of such problems as far as have dealt with heavy concurrency here [1] and in jdbc-wrapper I have wrote, but still wasn't aware of SQL-specific details.

[1] http://gaydenko.com/scala/tiscaf/httpd/

> You could use "select ... for update".

Thanks for the hint! It does the work.

I'm not sure it is very interesting, but will describe the test. Db consists of two tables with 10 rows in each one - parents and children. Each parent has a single child, and visa versa. So we have 10 pairs. Each table also has integer field.

There are 50 threads (H2 is used in server mode, threads are not joined), each thread executes 10000 transactions. Each transaction consists of the steps:

- select random parent-child pair,
- increment that integer field in both participants (is used to check integrity).

In my test I have got all transactions successfully finished with tryCount set to 1. Excellent!! Say, without FOR UPDATE, with 10 threads, with tryCount set to 10 and 1000 millis between attempts I get ~99.96% of successfully finished transactions (of course, with kept integrity also).

Sorry for delay - I needed some time to seamlessly introduce FOR UPDATE into the wrapper.

Andrew