I am not sure if derby documents the meaning of the line you are
referring to in the query plan:
at read uncommitted isolation level using share row locking chosen by the
> optimizer.
I think this is what it always says for read uncommitted, as is up to
a lower part of the code to not get the read locks. Could you describe
the behavior you are seeing or give a small test case that could be run.
Note Derby's implementation of read uncommitted does get table level
intent locks, so if you try operations that depend on table level
locking you will get blocking. The system is set up such that the top
level optimizer passes down the isolation level and then the information
about row vs. table and share vs exclusive down to a lower level. At
the lower level if things are working right the code makes a share row
lock call that goes into the read uncommitted module and then no actual
read lock is requested.
There have been significant improvements to IN handling in 10.3/10.4 so
you may want to try that.
Bogdan Calmac wrote:
Derby documentation specifies that SELECT statements do not place any locks
in the READ_UNCOMMITTED isolation level. However, the query plan shows that
it uses shared locks (which is also confirmed by application behaviour). So
is the documentation wrong or is this a bug?
This is derby 10.2.2.0, see the query plan below:
2008-05-07 14:21:39.990 GMT Thread[ExportWorker 2,5,ArchiveGroup] (XID =
583431), (SESSIONID = 17), select ID, AGENT_SESSION_ID, SEQUENCE_NO,
SCRIPT_SESSION_ID, EVENT_TIME, EVENT_TIME_SRV, L1, L2, L3, L4, L5, TOPIC_ID,
OBJECT_ID, TEXT, ATTRIBUTES from TRACK_EVENT where AGENT_SESSION_ID in
(43909,43914,43921,43922,43923,43924,43925,43926,43927,43930,43931,43932,43933,43934,43935,43936,43937,43938,43939,43940)
******* Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 63645
Rows filtered = 0
restriction = true
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 16824.30
optimizer estimated cost: 127904.69
Source result set:
Index Row to Base Row ResultSet for TRACK_EVENT:
Number of opens = 1
Rows seen = 63645
Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, 13, 14}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 16824.30
optimizer estimated cost: 127904.69
Index Scan ResultSet for TRACK_EVENT using index TE_IDX_SESSION
at read uncommitted isolation level using share row locking chosen by the
optimizer
Number of opens = 1
Rows seen = 63645
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 2}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=512
Number of rows qualified=63645
Number of rows visited=63646
Scan type=btree
Tree height=4
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 16824.30
optimizer estimated cost: 127904.69
