Informix Page Lock

Hi, I know this is somewhat a newbie question but may I ask how do you measure whether or not the table must use Page lock? I mean do you compute the number of rows? number of pages? How can one definitively say that his/her table should be page locked? Thanks,

IMHO row lock by default. Page locks should be only exceptions. If you have one row per page (no remainder), than you can use page locks to limit number of locks. Also, well designed transaction database and application should have short and small transactions so row locks again win. HTH Hrvoje

I agree with Hrvoje's response. For any OLTP database you should be using row level locking on all of your tables. Reserve using page level locking for Data Warehouse databases. There is no rule of thumb as to when page locking would be better in an OLTP environment because the rule of thumb is that page locking is NEVER better.

IDS 12.10.FC12 Solaris 10 1/13 I just happened upon this thread, and it made me wonder. The recommendation makes sense to me, but, IIRC, this is a change in ancient recommended practice. Many years ago, and I mean late 90's or early 2000's, when I was first sent to some Informix training classes (the official ones that Informix provided, before Informix was acquired by IBM), I recall an instructor saying exactly the opposite. The topic came up in class, and the rationale was something like this... It is much, much faster to establish and release a page level lock than a row level lock. And, using page level locks (rather than row level locks) requires far less resources. The take-away was that, most of the time, the time and resource saving usually made page level locking the better choice in the Informix world. I believe there was also a comment to the effect that this was an Informix distinctive compared to competing products, such as the big O. My initial inclination at that time was that it was "backwards", but figured the instructor "had the goods" on Informix internals, and I accepted it as "revealed truth". But, the consensus now seems to be that row level locking is, generally, the more advisable choice. I'm curious, was page-level locking ever the advisable choice with Informix, or, perhaps, I just misunderstood in that long-ago & far-away class. DG

All locks are just entries in an in-memory lock table ("onstat -k"). On the plus side for page locks, given this lock table gets checked frequently, the fewer entries in this table the better for your engine speed and less memory will be needed. If you will be updating multiple rows on the same page in the same session and have only a few sessions (DW system), page locking may better. Systems will noticeably slow down if you have millions of lock entries. As an aside, most of the locks you usually see with "onstat -k" are identical shared locks on the database a session is connected to, to prevent it being dropped; there is a small overhead to the engine continually parsing these. I have never heard that page locks are easier to establish. Maybe they are slightly quicker to check, someone else will have to advise here. In practice on a busy OLTP application with many sessions/threads, inserts will be on the same data page and remember there are also index pages to think about. Moving to page locks will cause a lot of lock waits, making these much harder to establish. The delays from these lock waits will be several orders of magnitude greater than any small efficiencies from using page locking. Bear in mind that since your course, Informix supports page sizes up to 16 kB so it's more likely you will be able to pack data close to the 255 rows per page maximum. This potentially means even more threads trying to write to the same pages, more lock waits, if you use page locking. Oracle works completely differently. I believe it writes the transaction id to the row on disk (or in-memory buffer pool). If the transaction is still active, the row is locked. Oracle periodically cleans up stale transaction identifiers. Ben.

David, While it's true page locking uses less overhead row level locking reins superior in the OLTP world. It may also be that the instructor was referring to the LOCKS parameter which back then may have not been dynamic and was a pain point with long running oltp stmts which would exhaust all available locks. Art sums it correctly in his original response. Mark

Hi David, Well, if that isn't a trip down memory lane! I actually think your dates might be off by half a decade -- I recall back in the early 90s also being taught that Page Locking was better than Row Locking for many circumstances. However, as Ben and Mark have pointed out, the engine has changed dramatically since then. Larger Pages, bigger memory, faster cpus and better handling of context switching. If I recall, the two rationale about Page Locking back then were the number of Locks would eat into shared memory (a valuable commodity in those days) and SELECTs locked every row involved. Better SQL writing (moving selects out of transactions whenever possible) took care of the latter issue (and I believe there system changes to how locks on Selects are handled); larger memory segments and usage of shared memory took care of the former. There are several other things that we were taught "back in the day" have flipped on their heads. The price of progress! Michael Hoffman

Thank you for comments. Time marches on. Technology's great-- and so is progress! DG