[jira] Updated: (DERBY-1017) locking issue with a select statement using an order by clause
[
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-', 'last', 'first', 'xxx-', 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-', 'last', 'first', 'xxx-', 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.
[jira] Updated: (DERBY-1017) locking issue with a select statement using an order by clause
[
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-', 'last', 'first', 'xxx-', 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-', 'last', 'first', 'xxx-', 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.
[jira] Updated: (DERBY-1017) locking issue with a select statement using an order by clause
[ 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-', 'last', 'first', 'xxx-', 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-', 'last', 'first', 'xxx-', 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
[jira] Updated: (DERBY-1017) locking issue with a select statement using an order by clause
[ http://issues.apache.org/jira/browse/DERBY-1017?page=all ]
Mark H. Kaplan updated DERBY-1017:
--
Attachment: derbyLocking.zip
attached is the source code for this issue. Ignore all of the commented lines
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: Network Server
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-', 'last', 'first', 'xxx-', 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-', 'last', 'first', 'xxx-', 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
