Re: SQL Server vs INFORMIX

>>>>> "Christopher" == Christopher Browne <cbbrowne@hex.net> writes: Christopher> There are three commonly varieties of database locking: Christopher> Christopher> a) Table locking, Christopher> Christopher> b) At the other end of things is the notion of Christopher> row locking. Christopher> Christopher> c) The third variety of locking, espoused Christopher> fairly heavily by Sybase, is known as "page Christopher> locking." You are 100% correct. You can compound the problem even further by creating a clustered index. In Sybase (unlike Informix) a clustered index *is* maintained. That is, over time, the data is sorted. This can be a good thing as well as a bad thing. For instance, a good thing, the Sybase optimizer is smart enough to "know" when to use a clustered index for an order by and not do a sort. Informix doesn't maintain the clustered index therefore I'm not sure what it does. I surmise that it *can* do the following: a) If the data retrieved comes from the data that is clustered, retrieve it. b) If the data retrieved is not clustered, sort it. c) If part of the data comes from both places, grab each and act accordingly. However reading recent posts on the Informix optimizer it seems to be pretty darn stupid. It doesn't even understand the notion of piggybacking off an index. A *bad* thing would be to cluster a table on a monotonically increasing key. What this results is a severe hot spot on inserts because everything is going to that final page. What do you do? Well, like any RDBMS, you understand where its limits are (what, limits???? But this thing can solve the world energy crisis) and program accordingly. As Nihl (sp?) posted hints on how to work with the Informix optimizer, you do the same with working with page level locks. You exploit the engine and understand its weaknesses and look for savvy people for solutions. (Can anyone honestly say that Informix has no problems? Optimizer, corrupted indexes, .... you can find the same problems with Oracle and Sybase) No big whup, just deal. Christopher> Deadlock happens if my process successfully Christopher> gets locks on some pages, and your process Christopher> successfully gets locks on other pages, and Christopher> then we both then try to get access to the Christopher> pages that the other has locked. At the very Christopher> least, this causes increased work as the Christopher> transaction processes have to relinquish locks Christopher> and try again. But when we try again, we're Christopher> liable to again bump up against locks on the Christopher> same pages, resulting in update failures. This can happen with row level locking as well. No difference. Somehow, it seems intuitive that you'd get less deadlocks with page level locks. But ya know, it's late and I could be wrong... Anyway, to combat deadlocks you always lock/acquire resources in the same order. Deadlocks == world destruction (also known as, really, really, really, really, [did I say really?] bad). -- Pablo Sanchez | Ph # (650) 933.3812 Fax # (650) 933.2821 pablo@sgi.com | Pg # (800) 930.5635 -or- pablo_p@pager.sgi.com ============= Please include "not spam" in your "Subject: " line ============== I am accountable for my actions. http://reality.sgi.com/pablo [ /Sybase_FAQ ]