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

Anthony Mandic wrote: > > Snorri Bergmann wrote: > > > Ok. Here is an easy one. Only involves 2 tables (master and detail). > > This is of course very simlified and I have not checked the syntax :-) > > [DDL definitions snipped] > > > Now what the app does is it allows users to select a record from the > > master table, get all child records from the detail table and allow the > > users to add/delete/modify those records as needed. After the user exits > > it updates the > > master table by selecting the sum of cost from the detail table. The sql > > statements involved are something like this: (assume everything is under > > transaction control) > > > > -- 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. > > > SELECT * FROM master WHERE id = ? FOR UPDATE;> > > > -- No need to lock the detail recs 'cause the master rec is locked. > > Yes, no need to lock. Its how you lock master thats the > problem. I'll explain below. > > > SELECT * FROM detail WHERE id = ? ORDER BY line_no;> > > > -- After the user exits we update all detail recs, probably by deleting > > them first, and then inserting whatever the user has typed in. > > What an overhead, delete followed by insert. An in situ > update is far more efficient. Use it wherever possible. > > > -- 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). > > > 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. > > > 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. > > > > 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) > M O N E Y :-) > -am The secret is indeed: in not performing a lock on the row till you're absolutely damn sure it's time to perform the update. Simply select the data into the form, and save off the original information in a save buffer. Allow the user to edit the form, and allow them to either update or change their mind and restore the screen. If the user is camped out on a row, and then decides to do something else, no lock on the data base whilst they edit the row. If they change their mind, abort the change, and refresh the screen with the save-buffer information. The larger the OLTP organization of people doing the work the greater the factor for people camping out on data. Should they get around to actually updating the row, check first to see if it *can* be done ( DECLARE CURSOR ... FOR UPDATE ) If the declare comes back clean, by checking the SQLCA, perform the update. Otherwise alert the user that the row is locked please wait. My code generators do this. It's still no excuse to allow page-level locking. Row-level locking in my not so humble opinion *is* important. :-) If you have a highly active OLTP environment, you should not introduce a page-level only data base into that environment and add risk. I know a Sybase programmer who shared his "trick" for working with this inherent problem. But it involves a stored procedure which adds overhead, and I think the above numbering scheme for serial numbers. More work than is really necessary, and a lot of wasted space in the data base. Those that argue against sloppy programming definitely are on the right track. Programs should include the additional management of checking the row to see if it can be updated, and only do the update when it's absolutely time to do so. That should make it a get-in-and-get-out proposition. As far as Pablo's comments, I saw bait, and that silly smile with a baloon on his head. :-) Timmy -- Tim Schaefer \\\\|// tschaefe@mindspring.com 6 6 -------------------------------oOOo---( )---o00o---------------------- http://www.inxutil.com http://www.informix.com http://www.iiug.org news://comp.databases.informix mailto:majordomo@iiug.org no subject body: subscribe linux-informix ======================================================================