db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: SELECT places shared locks in READ_UNCOMMITTED insolation level?
Date Wed, 07 May 2008 18:59:45 GMT
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
> 


Mime
View raw message