Templexp Tan wrote:
On 9/8/07, *Kristian Waagan* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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> <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?
Hi Temple,
The SELECT will need a lock. This is in principle the same as for other
lock-based DBMSs. (I don't know about Oracle, maybe they use MVCC?)
Since you are doing concurrent INSERT and SELECT, I wonder if you are
hitting DERBY-2991 (https://issues.apache.org/jira/browse/DERBY-2991).
Do you have any indexes, including primary key, on your table?
Can you enable deadlock/timeout tracing to your Derby server and post
the relevant contents from derby.log?
Add these in you derby.properties file or as Java system properties on
the command line you use for starting the Derby network server:
derby.locks.monitor=true
derby.locks.deadlockTrace=true
derby.stream.error.logSeverityLevel=0
When your client throws the exception about getting a lock, there should
be a dump of the lock table in derby.log.
* How many concurrent users/connections?
only 2-3 concurrent connection will cause the lock, but it is using
(BATCH QUERY)
Don't know how easy this is to check for you, but does Derby work
satisfactory if you only do the INSERTs?
* 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?
I assume you use the default isolation level in Derby, which is
READ_COMMITTED. You can find some information about the different
isolation levels here:
http://en.wikipedia.org/wiki/Isolation_(computer_science)
If you want to check, you can use
java.sql.Connection.getTransactionIsolation().
regards,
--
Kristian
* 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
