Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

>} >} What an overhead, delete followed by insert. An in situ >} update is far more efficient. Use it wherever possible. > >An update IS a delete followed by an insert. Sorry - not true. The update is on the same physical page as the original row. If the row is a displaced row because is is using varchars, the update will attempt to place the displaced row back on it's home page. The only delete/inserts that occur are to index items that might have been altered by the update. > >} >}> -- Then we'll need to update the master table and release the lock. >} >} You can use a non-server generated lock instead. Then it >} just becomes a matter of updating the master table. The >} action of this update is also the action of releasing the >} lock. No other process is locked out of reading the record >} but others can't update or delete while that record is marked >} as being in use (provided that they observe the rules). > >Ah I see. We don't need a lock because we're going to do our own lock. >I'm certainly glad that we don't need to lock the record. Tell me, are you >going to do this lock by something like 'touch /tmp/$rowid.lock' (;-))? >Why use a kludge when the engine can do the lock for you? > >If you set ISOLATION to DIRTY READ then no process is locked out of reading >the record either. > >} >}> If you have row level locking, the first select stmt will lock one row. >}> As the primary key is 4 bytes you can potentially put 4-500 keys on any >}> page (if you have 2K pages). In a page lock system (like Sybase and >}> MSSQL) the probability of a user trying to access one of the ~499 key >}> values another user has locked is pretty high. >} Madison Pruet