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

Chris Curvey wrote: > > > 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. > > > > That depends. If you have a small number of master records, > then the probability of a lock collision is high, but then you *can* > extend > the key to be 1K, since you don't need that much disk. If you have > a large number of master records, then you can control the probability > of a lock collision via a clustered index. > > > Page lock workarounds could be: > > > > 1) Make the primary key >1Kb, so you would only have one key per page. > > (Disks are cheap, right?) IMHO wasted space (i.e., filler fields) is more of a problem even if disks are cheap. The bigger issue is cache management. If every page/block could hold 4 records and is forced to hold only 1 now, you need 4 times as much cache space. In addition, records are significantly less likely to be in cache as a result of somebody else's SQL statements. Ultimately, you end up with more physical versus logical I/O or a require more memory to maintain the same level of performance. Kyle Hachey