Serial Update error

Found an interesting problem with a STANDARD ENGINE database. Note this, STANDARD ENDING *not* IDS. Table has a serial column. Program has 'UPDATE WHERE serial-column = program-variable'. Table does *not* have an index on the serial column, and starts producing locking errors presumably because it is scanning the whole table (yes I know that's bad) and coming across a row locked to another user. Now the table was missing an index which fixed the problem, but would this happen with IDS? Is it considered a bug in SE? Really this is curiosity on my part as due to the nature of the update there should be an index on the column, but curiosity can be a useful think. Of course especially with my sig I should note what it did to the cat as well.... -- Five Cats

Five Cats wrote: > Found an interesting problem with a STANDARD ENGINE database. Note > this, STANDARD ENDING *not* IDS. > > Table has a serial column. Program has 'UPDATE WHERE serial-column = > program-variable'. Table does *not* have an index on the serial > column, and starts producing locking errors presumably because it is > scanning the whole table (yes I know that's bad) and coming across a > row locked to another user. Err emmm errr I thought it was illegal to update a serial column. Since you point out so strongly that it's standard engine, weelllll, maybe it's legal in that beast, I haven't been near it for 10 years, but it certainly isn't legal in IDS. Perhaps you mean an integer column that you manual allocate "serial numbers" for ??? Re the locking - any update will sweep a "promotable lock" - which is a kind of half-arsed write lock, across all the rows it examines. It does this on the gamble that it might find the row suitable and then promote the lock to a proper write lock. If you have no indexes helping you out for the search, then it will sweep the entire table, or at least more rows that it will eventually end up modifying. This is the source of the locking, and no amount of messing with the ISOLATION LEVEL will help.