[ http://issues.apache.org/jira/browse/DERBY-1017?page=all ]
Mike Matrigali updated DERBY-1017:
----------------------------------
Component: SQL
(was: Network Server)
The most recent comment says this reproduces in embedded, so not a network
server issue. Setting to SQL as that is the next most likely area (though
behavior may be expected - need to look at test).
> locking issue with a select statement using an order by clause
> --------------------------------------------------------------
>
> Key: DERBY-1017
> URL: http://issues.apache.org/jira/browse/DERBY-1017
> Project: Derby
> Type: Bug
> Components: SQL
> 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.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
