Re: SQL Server vs INFORMIX

On 7 Oct 1997 20:25:09 GMT, "Reiner Kunz" <Reiner.Kunz@pr-kunz2.M.eunet.de> wrote: >where's the problem of no row-level-locking? In your opinion each database >using other locking methods are garbage. So, please tell me where do you >see the big disadvantage of no row-level-locking during UPDATEs and >DELETEs? There are three commonly varieties of database locking: a) Table locking, where a process demands exclusive access to the whole table, and indicates to other processes, "HANDS OFF!" Doing this is obviously highly hostile to any other processing that might be going on at the same time. Table locking is the sort of thing that might for instance be used by a posting/reconciliation process that runs once a month or once a year. You don't want the average transaction entry process to use table locking. b) At the other end of things is the notion of row locking. In this case, a process claims a lock on specific records that it's working on. The downside to row locking is that if there are a lot of users on the system, and a lot of locks active, it takes quite a bit of system resources to track and maintain these locks. c) The third variety of locking, espoused fairly heavily by Sybase, is known as "page locking." In this situation, one locks a whole page of database records at once, thus locking the particular row of the database that *needs* to be locked, as well as a variety of other data that does *not* need to be locked. The benefit of page locking over row locking is that this is cheap, quick, and easy to implement. You just need to have a lock indicator on each page, rather than having to track a lot of row-lock information. Unfortunately, it has the downside that locking one record has the effect of locking a bunch of unrelated records. It's not as bad as locking the whole table, but it still leaves you open to the possibility of hitting deadlocks between processes that happen to be updating some of the same tables. If there's only one process active, there won't be any "locking conflicts." But as the number of users/update processes increases, the probability of having conflicts increases towards unity. (I'm ignoring the furthest "degenerate" possibility, that being "field locks," where one locks specific *fields* within rows; this obviously means tracking even more information than row locking...) Deadlock happens if my process successfully gets locks on some pages, and your process successfully gets locks on other pages, and then we both then try to get access to the pages that the other has locked. At the very least, this causes increased work as the transaction processes have to relinquish locks and try again. But when we try again, we're liable to again bump up against locks on the same pages, resulting in update failures. -- cbbrowne@hex.net, <http://www.hex.net/~cbbrowne>; Q: Where would Microsoft take you today? A: Confutatis maledictis, flammis acribus addictis... Spam bait: domreg@cyberpromo.com postmaster@netvigator.com postmaster@onlinebiz.net pmdatropos@aol.com admin@submitking.com cte@llv.com walt@pwrnet.com