db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <kristian.waa...@oracle.com>
Subject Re: Can someone tell me if "syscs_util.update_statistics" locks table/indexes being updated
Date Wed, 01 Dec 2010 15:18:59 GMT
On 01.12.2010 15:32, Bryan Pendleton wrote:
> On 12/01/2010 05:26 AM, Bergquist, Brett wrote:
>> If I have a table with millions of rows and I invoke 
>> syscs_util.update_statistics on it, will the table/indexes be lock 
>> such that inserts or updates will fail while this is being done.  The 
>> documentation is not clear if this is the case (at least to me).
> I'm pretty sure that the scan of the data happens using the
> READ_UNCOMMITTED isolation level, which is the lowest-level of
> locking (that is, the highest concurrency)
> http://db.apache.org/derby/docs/10.6/devguide/cdevconcepts15366.html
> However, that's based on looking at the code in the updateStatistics()
> method in AlterTableConstantAction.java, not on actually trying it,
> so if you could set up a test environment and try it, that would be 
> great!

FYI, the code I'm working on, which may or may not make it into a future 
release, doesn't take any locks at all [1]. This means that the table 
can even be dropped while scanning the indexes. If this happens, it will 
only result in the effort to update the statistics to be cancelled.

For explicit calls to SYSCS_UTIL.UPDATE_STATISTICS, it sounds reasonable 
to me that we take locks to avoid that the table is dropped while 
scanning. The statistics aren't expected to be 100% accurate, so we 
allow that updates, inserts, and deletes happen while computing them.


[1] Except for some "metadata operations" at the very beginning and end 
of the process that is.

> thanks,
> bryan

View raw message