db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <Br...@canoga.com>
Subject RE: Can someone tell me if "syscs_util.update_statistics" locks table/indexes being updated
Date Wed, 01 Dec 2010 15:25:12 GMT
Thank you very much for this quick response.  Much appreciated.

From: Kristian Waagan [kristian.waagan@oracle.com]
Sent: Wednesday, December 01, 2010 10:18 AM
To: derby-dev@db.apache.org
Subject: Re: Can someone tell me if "syscs_util.update_statistics" locks table/indexes being

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