Re: Comparing RDBMS engines / LOCKING
From: "Chuck G." <chuckg@Starbase.NeoSoft.COM> Date: Tue, 6 Jun 1995 11:43:37 -0500 (CDT) > } > } According to the best of my knowledge both these servers do not support > } record level locking. This is a major weakness in online transaction > } systems. I can therefore not recommend these servers to our clients. > } > } Theo > } > > Why is row-level locking so important? Page level locking is very > efficient. Furthermore, doesn't Online lock the entire page in a DML > transaction? > > Every case of contention that I have encountered was better addressed > by spreading out the updates instead of increasing the granularity. > > I would appreciate further discussion on this topic. > > -- > chuckg@NeoSoft.com "An egg's in the plan" -- M. Mothersbaugh We use page locking only on tables that require no user update, such as data lookup tables. On any data that the user may lock, we use row-level locking. We had problems with people locking their row *as well as* someone else's, where the row someone else wanted just happened to be on the same page. This kind of problem can be very hard to debug, as it happens intermittently and unpredictably. Now it is one of the first things I check when I go into a system. OTOH, you must adjust the LOCKS parameter way upward from the default setting of 2000. 2000 page locks is a lot of data, but 2000 rows is not much at all. If you run out of locks during some transaction it will probably corrupt the indexes, the table, and may require a restore from backup (been there, done that.) You may have >1 lock per transaction, I have seen as many as 4 or 5 to 1 locks/row ratio. Max locks on a table is 256000, which comes out of your shared memory, but locks are cheap and the manuals encourage you to use them lavishly. In summary, we use row locking, and pump up the LOCKS value in the ONCONFIG file as big as we can. __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|