[
https://issues.apache.org/jira/browse/DERBY-1017?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12484836
]
Peter Nedonosko commented on DERBY-1017:
----------------------------------------
Hi, I work on a next version of the eXo JCR product (eXo Platform).
We did test Derby with our eXo JCR impl before and had no problem in v.1.5 (as
with MyQSL, PgSQL, Oracle ect). But a next refactoring changes a connection
logic and we got it with Derby only.
I found that the problem has place if I try open two (or more) connections to
a same db and try to modify same table(s).
So, the problem is trivial if a transaction mechanism is based on locks (as I
understood).
I think that it's a not ORDER statement issue, it's a problem of concurrent
modification of a same table (different records), may be a page lock etc.
I fix the problem for eXo JCR product tuning a connection usage - so, no more
one connection to a same database.
But it's a units/TCK only. I hope that the problem will not occurs in
multithreading use case. Or, sure, you will fix it before.
A code like this (eXo JCR impl core v.1.6 with modifications for the issue):
Connection regularConnection = null;
Connection systemConnection = null;
try {
for (Data change: changes) {
Connection conn = null;
if (isSystemPath(data.getQPath())) {
conn = systemConnection == null ? systemConnection =
systemDataContainer.openConnection() : systemConnection;
} else {
conn = regularConnection == null ? regularConnection =
dataContainer.openConnection() : regularConnection;
}
// exception here if conn is systemConnection, and the
systemConnection opened to the same db
// as the regularConnection. The regularConnection was opened before
in the cycle and a data was inserted/updated.
addSameTableRow(change, conn);
}
if (regularConnection != null)
regularConnection.commit();
if (systemConnection != null)
systemConnection.commit();
} catch (Exception e) {
log.error(e);
throw e;
}
Hope that it will help you.
> 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.
