Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Mon, 10 Sep 2007 09:40:05 +0200
From: Kristian Waagan <Kristian.Waagan@Sun.COM>
Subject: Re: anybody know why the derby alway appear "requesting a lock"
In-reply-to: <1c0607b60709091924p5a375819x4ab0a1170a3c3c15@mail.gmail.com>
Sender: Kristian.Waagan@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <46E4F4D5.5000004@Sun.com>
Organization: Sun Microsystems Inc.
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <1c0607b60709080507w696eb7fcq37fd7db15feb9d01@mail.gmail.com>
 <1c0607b60709080513o45401959ia05b67c57df0d7c8@mail.gmail.com>
 <46E2C64A.50509@Sun.COM>
 <1c0607b60709091924p5a375819x4ab0a1170a3c3c15@mail.gmail.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; no-NO;
 rv:1.8.1.3) Gecko/20070419
 Thunderbird/2.0.0.0 Mnenhy/0.7.5.0

Templexp Tan wrote:
> On 9/8/07, *Kristian Waagan* <Kristian.Waagan@sun.com 
> <mailto: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> <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
> 
> 
> 
>