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

At 05:10 PM 11/26/97 +1000, you wrote: }Snorri Bergmann wrote: } [snip] }> }> -- We need to lock the master table so nobody else will update it while }> we are. } } Here is the crux of your problem. "We need to lock". } Its this mindset that doesn't allow you to see a better } solution, so you let your DB server do it for you. } [snip] } } 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. } }> -- 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. } } Thats the reason why server-generated locks should be fast, } and not held for an indefinite period. the initial select } should NOT be part of the transaction. This rule should } apply to all locking methods, including row level. I happen to agree with you, apps should be written to hold locks for the shortest possible amount of time, not while the user takes off for lunch. } }> Page lock workarounds could be: }> }> 1) Make the primary key >1Kb, so you would only have one key per page. }> (Disks are cheap, right?) } } Wrong approach. This is the BFI method favoured by those who } just can't grasp the concepts. } }> 2) Use optimistic locking. (So users who have been typing in for hours }> get the message: Somebody else has modified this rec while you were }> working. Please try again). } } Same wrong approach. I beleive he's being facetious in these two cases. }> }> 3) Swich to Informix :-) } } I feel tempted to state the same here, but I'd rather ask why } some of Informix's top programmers have jumped ship and joined } Orable? (According to what I've read recently in the trade } papers) } While we're off on tangents, have you heard that a woman in Iowa had septuplets? I think they're all going to be named 'Sam' and 'Samantha'. People change companies all the time, and where is a database person going to go when they decide to leave a database company? Probably some other database company that recognizes how far behind they are and how much they need to catch up. ;-). cheers j.