On 9/8/07, Kristian Waagan <Kristian.Waagan@sun.com> wrote:
Templexp Tan skrev:
> it is under the c/s mode. why the program running a very frenquent
> inserting operation , it comes "requesting a lock"
> and the speed compare to oracle is extramly slow. it is about only
> 10,000 records.
>
> the DERBY version:
>
> 10.3.1.4 <http://10.3.1.4/>
>
>
>
Hello,

Can you please give a little more information about the load you see
problems with in Derby?

For instance;
* What kind of queries are being executed concurrently against the table?
* How many concurrent users/connections?
* What is the isolation level used?
* Do you run with auto-commit on or off?
* Are you using a PreparedStatement to execute your inserts?

Also, what do you mean with "requesting a lock"? Do you get an error
message in your client or the derby.log file?
Or does the server appear to be idle?


thanks,
--
Kristian


 
Hello,


It is using Java's batch query to do only "inserting" operation. it is about to insert 100 records (about 20+ fields , no lob) every few seconds. the error appear like:

java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException (Unknown Source)
    at org.apache.derby.client.am.Statement.executeQuery(Unknown Source)
    at triggermodify.SingleTable.selectGenTable(SingleTable.java:93)
    at triggermodify.SingleTable.generate(SingleTable.java :44)
    at triggermodify.TriggerDataProcess$1.update(TriggerDataProcess.java:63)
    at thread.Temple.run(Temple.java:66)
Caused by: org.apache.derby.client.am.SqlException: A lock could not be obtained within the time requested
    at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parseOpenQueryError(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parseOPNQRYreply (Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readOpenQuery(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readOpenQuery(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readOpenQuery _(Unknown Source)
    at org.apache.derby.client.am.Statement.readOpenQuery(Unknown Source)
    at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
    at org.apache.derby.client.am.Statement.executeQueryX (Unknown Source)
    ... 5 more


finally i find a very strange problem, since I used to use ORACLE as the database, now on just move to JAVADB(DERBY), but it looks like a little bit different. the "ADDBATCH" way is slower than the normall "EXCUTE" way. and while exec the batch, it gonna lock the while table. is it the way it should be?

* What kind of queries are being executed concurrently against the table?
only the normal insert and select. btw, it is the "select" will need a lock? it is differnt than other DB?

* How many concurrent users/connections?
only 2-3 concurrent connection will cause the lock, but it is using (BATCH QUERY)

* What is the isolation level used?
could you like to explain this a little bit more?  do you mean by TABLE lock  or RECORD lock?

* Do you run with auto-commit on or off?
it does set to auto-commit OFF.

* Are you using a PreparedStatement to execute your inserts?
BATCH query.


Regards,
Temple