Thank you very much for this quick response. Much appreciated. Brett ________________________________________ From: Kristian Waagan [kristian.waa...@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 updated
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. -- Kristian [1] Except for some "metadata operations" at the very beginning and end of the process that is. > > thanks, > > bryan >
