RE: Can someone tell me if "syscs_util.update_statistics" locks table/indexes being updated
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 >
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
Re: Can someone tell me if "syscs_util.update_statistics" locks table/indexes being updated
"Bergquist, Brett" writes: > 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. It used to lock the table exclusively, but that was fixed in DERBY-4274/Derby 10.5.2.0. Now it should only take an intention lock on the table, which prevents other transactions from dropping the table, but inserts and updates won't be blocked. -- Knut Anders
Re: Can someone tell me if "syscs_util.update_statistics" locks table/indexes being updated
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! thanks, bryan
