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
