db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: [jira] Updated: (DERBY-47) Some possible improvements to IN optimization
Date Tue, 13 Mar 2007 23:30:37 GMT
It is confusing just looking at the diff, the diff only tells the story 
about what locks exist after the first fetch from the cursor.  Below is 
the output for that portion of the diff in the new code.  The way to 
read this test is it
does an operation, then gets all the locks outstanding, then another
statement, then all the locks, ....

When looking at row locks anything listed as (N, 1) is a special
scan lock on page N and not a real row lock.  This lock is the internal 
way of
optimizing keeping track of where a scan is while a scan is positioned
on a page.  So a lock of (3, 1) is a scan lock on page 3 - ignore these
locks for this discussion.

So in the new code when the cursor does the first next and gets the row
where a=5, it does get less locks at that point in time vs. the old 
code.  Because this is serializable and the index in non-unique the
probe using the index locks (4,6) and (5, 6) which are rows a=4 and a=5.
In serializable we lock a range of keys by locking the key previous
to the first key we look at and all the keys until the scan is done (
including any deleted keys).

I am not exactly sure (without the queryplan) what the old code did, but
it looks like it does a bulk fetch on the index of size 16.  So when it
does the first next it actually looks at rows (4, 6), (5, 6), (6, 6) and
(7, 6) before returning the 1st qualifying row to the cursor.  So 
previously at this point 4 rows will be locked rather than 2.  But after
all rows have been fetched to the cursor both before and after have 
locked 4 rows.

ij> get cursor scan_cursor as
     'select a from a where a = 5 or a = 7';
ij> call 
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault
', '16');
0 rows inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, 
lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IS  |A           |Tablelock |GRANT|ACTIVE
ij> next scan_cursor;
A
-----------
5
ij> select * from lock_table order by tabname, type desc, mode, cnt, 
lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IS  |A           |Tablelock |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(3,1)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(4,6)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(5,6)     |GRANT|ACTIVE
ij> next scan_cursor;
A
-----------
7
ij> select * from lock_table order by tabname, type desc, mode, cnt, 
lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IS  |A           |Tablelock |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(3,1)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(4,6)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(5,6)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(6,6)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(7,6)     |GRANT|ACTIVE
ij> close scan_cursor;
ij> select * from lock_table order by tabname, type desc, mode, cnt, 
lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |IS  |A           |Tablelock |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(4,6)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(5,6)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(6,6)     |GRANT|ACTIVE
APP     |UserTran|ROW     |1   |S   |A           |(7,6)     |GRANT|ACTIVE
ij> commit;
Army wrote:
>> Mike Matrigali updated DERBY-47:
>> --------------------------------
>>
>> I took a look at the diffs in readlocks and I believe all are 
>> "correct" with
>> respect to your changes.
> 
> 
> Thank you very, *very* much for such a detailed review of the readlocks 
> diff, Mike.  Not only does this answer the question of whether or not 
> the diffs are acceptable, but I also learned a bit about how this test 
> works, which is great.
> 
> <snip very useful details on the readlocks diff>
> 
>> 13) @@ -22639,8 +22639,6 @@^M
>>    o diff ok
>>    o not as good a test as 10.  Because of previous key locking and 
>> the very
>>      small data set both before and after we lock the same number of 
>> rows.
>>      Diff does show difference in processing between before and 
>> after.  If
>>      there had been more than one row between 5 and 7 with the non-unique
>>      index it would have shown less rows locked under new code vs. old 
>> code.
> 
> 
> Just out of curiosity, the diff for this one is:
> 
> @@ -22639,8 +22639,6 @@
>  APP     |UserTran|ROW     |1   |S   |A           |(1,1)     |GRANT|ACTIVE
>  APP     |UserTran|ROW     |1   |S   |A           |(1,10)    |GRANT|ACTIVE
>  APP     |UserTran|ROW     |1   |S   |A           |(1,11)    |GRANT|ACTIVE
> -APP     |UserTran|ROW     |1   |S   |A           |(1,12)    |GRANT|ACTIVE
> -APP     |UserTran|ROW     |1   |S   |A           |(1,13)    |GRANT|ACTIVE
>  ij> next scan_cursor;
>  A
>  -----------
> 
> When I saw this I though that the new code was, in fact, locking fewer 
> rows than the old code (because the last two locks are missing from the 
> lock table).  But it sounds like that's not really what's happening?
> 
> If there's an easy explanation behind this and you have the 
> time/inclination to elaborate, could you perhaps touch on what we would 
> expect to see with a query such as "IN (1, 7)", and how that would be 
> different from the above?

With a query such as IN (1, 7) against a non-unique key I would expect 
to see 4 total row locks in your new code after the query is finished. 
It would probe twice and
in serializable get a previous key for each probe.  In repeatable read
I would expect to see 2 total locks.  Previous to your change I would 
expect to see the code get locks on all the rows from 1 through 7.

> 
> If that's too much to ask, no problem--you've already helped me a great 
> deal by looking at the diff and providing feedback.  Just piqued my 
> curiosity, that's all :)
> 
> Thank you again,
> Army
> 
> 
> 


Mime
View raw message