Re: Serial Update error

In article <beifr3$5ge2o$1@ID-79573.news.dfncis.de>, Andrew Hamm <ahamm@mail.com> writes >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 ??? Read the WHERE clause. That is where the serial column is used, we certainly do *not* update it's value. > >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. So in effect the answer is yes. Thought it might be... Thanks. > > -- Five Cats