[jira] [Commented] (DERBY-5282) Convert store/RowLockBasic.sql to junit

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-5282?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13056950#comment-13056950 ] 

Bryan Pendleton commented on DERBY-5282:
----------------------------------------

The reason you are seeing 5 rows instead of the expected 1 row is because that particular
test is deciding to do row-level locking instead of table-level locking, and the select from lock_table
is returning 1 Table-level IS lock and 4 Row-level S locks.

I ran the test with -Dderby.language.logQueryPlan=true and the choice of row-level locking
is also confirmed by the trace output:

Tue Jun 28 18:25:12 PDT 2011 Thread[main,5,main] (XID = 242), (SESSIONID = 3), select a from a ******* Index Scan ResultSet for A using index A_IDX at repeatable read isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0

scan information:
        Bit set of columns fetched={0}
        Number of columns fetched=1
        Number of deleted rows visited=0
        Number of pages visited=1
        Number of rows qualified=4
        Number of rows visited=4


I extracted the relevant bit of SQL into a small separate file and ran it
under IJ, and sure enough when I do that Derby chooses table-level locking.

Tue Jun 28 18:45:21 PDT 2011 Thread[main,5,main] (XID = 245), (SESSIONID = 1), --------------------------------------------------------------------------------
-- Do full covered index scan.
--------------------------------------------------------------------------------
select a from a ******* Index Scan ResultSet for A using index A_IDX at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4


Then I noticed that in the row-level locking result, Derby is using "repeatable
read" isolation, while in the table-level locking result, Derby is using "serializable"
isolation.

So I tried changing your test code so that instead of setting the isolation
level to TRANSACTION_REPEATABLE_READ in the setUp() method, it
sets TRANSACTION_SERIALIZABLE instead.

And then the test passes -- Derby uses table level locking and all is well.

What is curious is that clearly the original test looks like it is trying to use
REPEATABLE_READ isolation, as it contains:

set isolation to RR;

at the very start of the test.

Perhaps this line in the test isn't actually working, though, and the test
is still using SERIALIZABLE, not REPEATABLE_READ isolation?

Can you try changing the test in your environment to use SERIALIZABLE
isolation and verify whether or not the test then passes for you?


> Convert store/RowLockBasic.sql to junit
> ---------------------------------------
>
>                 Key: DERBY-5282
>                 URL: https://issues.apache.org/jira/browse/DERBY-5282
>             Project: Derby
>          Issue Type: Sub-task
>          Components: Test
>            Reporter: Houx Zhang
>            Assignee: Houx Zhang
>              Labels: gsoc2011
>         Attachments: derby-5282-1.patch, derby-5282-1.state
>
>


--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira