Long running query locks another session

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

1. create table
create table TEST (id number, name varchar(30), ts timestamp);
insert into TEST
select X, current_timestamp, current_timestamp from system_range(1,10000);

2. connect to session A with jdbc:h2:~/test;MVCC=TRUE;AUTO_SERVER=TRUE
3. connect to session A with jdbc:h2:~/test;MVCC=TRUE;AUTO_SERVER=TRUE

4. run a long query in A
select count(*) from TEST,TEST;

5. run a fast query in B
select count(*) from TEST;

What is the expected output? 

it is expected that query in session B should not be locked.

What do you see instead?

The query in session B is locked.
It proceeds as soon the query in A is done or aborted.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 1.1.116 (2009-07-18) installed from h2-setup-2009-07-18.exe
Windows XP Professional
NTFS

Do you know a workaround?
No. I'd like to.

How important/urgent is the problem for you?
It is quite important. We need to run at least two sessions, one writes to
table another reads. Because of this issue the work probably will be
serialized, that impacts a performance. 

In your view, is this a defect or a feature request?
a defect. A reader should not lock reader, even in MVCC=FALSE mode. 

Please provide any additional information below.

Original issue reported on code.google.com by mark.mal...@gmail.com on 8 Aug 2009 at 3:47

[GoogleCodeExporter]

Please disregard this issue. This is expected behavior with a single thread 
server.
However more extended description of differences between single and 
multi-threaded
server modes would be nice.

Original comment by mark.mal...@gmail.com on 9 Aug 2009 at 2:37

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 13 Aug 2009 at 5:06

• Changed state: WontFix

[GoogleCodeExporter]

I cannot seem to find the instructions on how to use a multi-threaded server 
mode. I am suffering from the same problem where a long running delete 
statement on table A is causing a read o table B to stall for a long time. 
Please advise

Original comment by xushicha...@gmail.com on 9 Jun 2010 at 3:50

[GoogleCodeExporter]

> I cannot seem to find the instructions on how to use a multi-threaded server 
mode

See the documentation, MULTI_THREADED=TRUE

> I am suffering from the same problem where a long running delete statement 
> on table A is causing a read o table B to stall for a long time.

The multi-threaded mode will not solve this problem. The delete will lock the 
table.

I suggest to try to speed up the delete, or break it into multiple smaller 
transactions.

Original comment by thomas.t...@gmail.com on 12 Jun 2010 at 11:09