[
https://issues.apache.org/jira/browse/DERBY-1017?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-1017:
---------------------------------
Attachment: selfContainedRepro.zip
I hacked the repro with DDL ops, so it runs self contained.
Additional data point: If I user the optimizer override to force use of the
index in ConnB, the query does not hang:
String sql = "SELECT au_id, au_lname, au_fname, phone, contract FROM authors
--DERBY-PROPERTIES index=firstnameindex" + "\n where au_lname = ? ORDER BY
au_fname";
> 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.
