db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-1017) locking issue with a select statement using an order by clause
Date Fri, 26 Jun 2009 17:07:47 GMT

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

Dag H. Wanvik commented on DERBY-1017:
--------------------------------------

Looking more, I notice that ConnB does not actually use the result set by calling next on
it.
This explains why, when ORDER BY is dropped, there is no hang. If the next() is performed,
the query will hang even if the ORDER BY is dropped. The same goes for the forced use
of the index (using --DERBY-PROPERTIES), because even in that case, the base table needs to
be visited to check the predicate on au_lname. When ORDER BY is specified, the base table
will be read in its entirety before the first
row is returned, as Mike said, so a hang will be seen, even if rs.next is not called. 

So, in summary, unless a au_lname is also indexed, there is no way around checking the base
table
for the predicate WHERE au_lname=? and a hang will be seen until ConnA commits.
 
If nobody objects I will close this a not a bug.



> locking issue with a select statement using an order by clause
> --------------------------------------------------------------
>
>                 Key: DERBY-1017
>                 URL: https://issues.apache.org/jira/browse/DERBY-1017
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.0
>         Environment: Windows XP Professional operating system and Java2 platform using
JDK 5.0
>            Reporter: Mark H. Kaplan
>         Attachments: derbyLocking.zip, selfContainedRepro.zip
>
>
> I am using the network version of Derby (version 10 - the network version). I am running
two threads. The first thread is doing an insert into a table but not committing. The second
table is doing a select statement. When the select statement has an order by clause, it will
not complete but when it does not have the order by clause, it completes while the first thread
is sleeping.
> The database contains one table with five columns. I have tried having an index on the
order by column but that does not seem to make a difference. I have not set any isolation
level on the database so it is using the default of TRANSACTION_READ_COMMITTED.
> The insert statement in the first thread looks like:
> INSERT INTO Authors (au_id, au_lname, au_fname, phone, contract) VALUES ('999-99-9999',
'last', 'first', 'xxx-xxxx', 0)
> The select statement in the second thread looks like:
> SELECT au_id, au_lname, au_fname, phone, contract FROM authors where au_lname = 'xxx'
ORDER BY au_fname
> MORE INFORMATION --
> My order by select statement does timeout with the error 40XL1. I tried putting an index
on the au_fname but that did not make a difference
> I have included locking data which I retrieved by running a  "SELECT * FROM NEW org.apache.derby.diag.LockTable()
AS LT" while the second thread was doing its SELECT statement. I do not understand the data
but I thought that it might give you a better idea of what is going on. I have also included
the database sql script that creates the database table and the two sql statements that I
am running in separate threads to give you a better idea of what I am doing. Let me know if
you need any other information:
> (Locking Data)
> XID |TYPE |MODE |TAB |LOCK |STATE |TABLETYPE |LOCK& |INDEXNAME
> ===
> 302 |ROW |X |AUTHORS |(2,18) |GRANT |T |1 |null
> 302 |ROW |X |AUTHORS |(1,7) |GRANT |T |1 |null
> 304 |ROW |S |AUTHORS |(1,7) |WAIT |T |0 |null
> 302 |TABLE |IX |AUTHORS |Tablelock |GRANT |T |3 |null
> 304 |TABLE |IS |AUTHORS |Tablelock |GRANT |T |1 |null
> (SQL Script)
> DROP TABLE authors;
> CREATE TABLE authors (
> au_id VARCHAR(32) NOT NULL,
> au_lname VARCHAR(40) ,
> au_fname VARCHAR(20) ,
> phone VARCHAR(12) ,
> contract INT NOT NULL,
> PRIMARY KEY (au_id)
> );
> CREATE INDEX firstnameindex ON authors (au_fname);
> (SQL Statements)
> Thread 1 - INSERT INTO Authors (au_id, au_lname, au_fname, phone, contract) VALUES ('999-99-9999',
'last', 'first', 'xxx-xxxx', 0)
> Thread2 - SELECT au_id, au_lname, au_fname, phone, contract FROM authors where au_lname
= 'xxx' ORDER BY au_fname

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message