Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (hermes.apache.org: domain of ksunithaghm@gmail.com
 designates 64.233.170.206 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:content-type:content-transfer-encoding;
        b=TcU2y9Kpan4xbR58+r3UBYf1Ti7WcrUhJ/d+82aZPB4ectwE0ZhZRShJGngh4xXR4DwM6DWmvrSmmI6SNd6Vfz7GOuxzg1cwQ0IYHgREXB67FL+wjSRGQfknjRJhoGFia7wVTuQx9Q3+lUU4rUZD5+hFeJd9QsqTiVNc7wshvDs=
Message-ID: <4291437B.7010505@gmail.com>
Date: Sun, 22 May 2005 19:44:11 -0700
From: Sunitha Kambhampati <ksunithaghm@gmail.com>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: regarding  semantics of select in Autocommit mode .. ( and derby
 265)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I am actually looking at Derby 265 (an assert failure in store).  The 
assert failure occurs on a getBlob call which is because at that time 
there is no transaction context.  But then, looking at the repro got me 
thinking about select stmt in autocommit mode and also wonder if the 
repro is testing the right behavior or not.. 

Section 10.1 of the JDBC 3.0 spec says
Enabling autocommit,  causes the jdbc driver to do a transaction commit 
after each individual sql statement as soon as it is complete.  the 
point at which it is complete depends on type of statement.  for select 
statement  :- statement is complete when resultset is closed and result 
set is closed* as soon as one* of the following happens
   -- all rows have been retrieved
  -- associated statement object is re-executed
  -- another Statement object is executed on the same connection

from repro in  Derby-265 :
Note s, s2 are on the same connection object that is in  autocommit mode
1    s.execute("select * from maps")
2    rs1 = s.getResultSet();
3    s2.execute("select * from maps")        
4    rs2 = s2.getResultSet();                    
5    rs2.next();
6    rs2.getBlob(6);
7    rs1.close();
8    rs2.next();
9    rs2.getBlob(6);           
__________________
-- from the spec (10.1) , does it mean that the statement execution on 
line 3 would commit the earlier statement on #1.   ? If so, we dont seem 
to do that. 

-- Also, rs1.close() is internally calling a commit but the connection 
is actually dealing with s2 currently and  so is it right that 
rs1.close() commits the transaction associated with s2 ?   Then again, 
is this interleaving of reading of resultsets and select statement even 
valid ? . I checked the jdbc spec and the api and also briefly the 
tutorial book but didnt come across much about this. .

Coming back to the reason for the assert failure
-- so rs1.close() is committing the transaction which is why 
rs2.getBlob(6) is left without a transaction context leading to the 
assert failure. 

A simpler snippet for just the assert failure case (s ,s1 on one 
connection in autocommit mode).
1    s.execute("select * from maps'");
2    rs = s.getResultSet();
3    s1.executeUpdate("insert ....  ");
4    rs.next();
5    rs.getBlob(6);

-- when s1 is executed , s is complete ( and committed ) per spec.  Will 
rs still be valid at (line 4), I guess that depends on the holdability.  
As rs is a hold cursor, what transaction context should this be in  ?
-- The assert failure happens on the getBlob call ( line 5) , which is 
because the blob has an underlying outputstream and uses a transaction 
context in this case.
The jdbc api for Blob says ' A blob object_ is valid for the duration* 
*of the transaction in which* *it was created_*'*.  From this it seems 
like the call on #5  is actually not valid ( since the transaction in 
which the blob was created is complete).

-- All this makes me think that the program is incorrect.  But I guess  
we should be throwing  a  better user error instead of an npe/assert.
___________________

Also some notes on derby 265.
-- repro violated this part of the jdbc api for Statement
"By default, only one |ResultSet| object per |Statement| object can be 
open at the same time. Therefore, if the reading of one |ResultSet| 
object is interleaved with the reading of another, each must have been 
generated by *different |Statement| objects*. All execution methods in 
the |Statement| interface implicitly close a statment's current 
|ResultSet| object if an open one exists"
 So  made changes to use different Statement objects.

-- The derby 265 assert failure cause  is not specific to network server 
mode as such. In the original repro, getBlob()  was not being called in 
the program which is why embedded was not throwing the error,  but for 
network server a rs2.next() actually retrieves the blob (getBlob()) 
which causes the assert to be thrown at the store level.  So changing 
the program to call rs2.getBlob shows up the error in embedded mode also.

-- Note, the assert failure happens only if the blob column overflows

I'd appreciate any comments/feedback.

Thanks,
Sunitha.