[ https://issues.apache.org/jira/browse/DERBY-1017?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-1017: ---------------------------------- Derby Categories: [High Value Fix] > 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 > > > 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.