IDS - Record Lock

Hi, We always facing this situation "record Lock". I already try to increase our LOCKS size on onconfig file ( 100000 to 500000), but still have record lock. How we determined the best value we need to assign to LOCKS, Any formula to calculated this parameter? Beside that how to trace user currently lock the table? Which table are lock? any command can we used to check it ? I noticed that default table look mode is page right? If we change to row lock mode, it will help us to reduce table lock? What is different between row lock & page lock, which situation good to used row lock? sample: for reference table means table that only used for select. Thank You

If a table is only ever used for selects, then the locking mode doesn't matter. For OLTP type systems where concurrency is required, you should probably be using row level locking for most tables, unless some tables have mass changes, where you may want to use page level, but this should be done on a case by case basis. The LOCKS parameter only sets the initial number of locks. If more locks are required, then this will grow dynamically (not true in old versions of Informix) - but try and avoid the dynamic growth if possible. There is no formula, but you need to guesstimate the total of how many locks may be held by all users at any one time...and then make LOCKS larger than that...and increase if necessary. Increasing LOCKS will not avoid the locking problems that you are running into. This is simply that somebody is trying to read something that somebody else has locked. Row level locks are simply that - a lock will be held against a single row. When a user updates a record for example, that record, and (hopefully) only that record will be locked for the duration of the transaction, preventing other users from updating it...or selecting it (unless dirty read isolation mode is used). Page level locks are locks that are held at a page level - and a page may have one or more records on it. A lock applied to any record on that page, for example by an update, will lock ALL records on the page. For large operations this will reduce the number of locks, but concurrency will be reduced as processes will be prevented from changing other records on that same page. Locking problems can be reduced by changing the lock mode of a table (i.e. from page to row), setting the isolation mode appropriately ("dirty read", "commited read", etc), setting a lock mode to WAIT in the applications, performing large updates/deletes etc on smaller data sets with frequent commits, and also by improving performance overall so that transactions are shorter and locks held for a shorter time. Also avoiding sequential scans, as these will bump into any locks held on the table. As for monitoring for locks, you can use "onstat -k". The last line shows how many locks are currently held, and also how many locks are currently configured (normally the same as LOCKS). A simpler way, but not the most efficient, is to query sysmaster:syslocks. It will tell you what session is holding a lock, the table or index, the type of lock and (very useful) if a session is waiting for a lock (be wary of running this continually). Also onstat -u will list any sessions that are waiting on a lock with a "L" inthe first position of the flags column - but a session will only wait for a lock to be releases if a wait mode has been set, otherwise it will fail immediately. Hope that helps! Mike Walker Advanced DataTools -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MOHD FADZIL JUSOH Sent: Wednesday, February 24, 2016 8:17 PM To: ids@iiug.org Subject: IDS - Record Lock [36612] Hi, We always facing this situation "record Lock". I already try to increase our LOCKS size on onconfig file ( 100000 to 500000), but still have record lock. How we determined the best value we need to assign to LOCKS, Any formula to calculated this parameter? Beside that how to trace user currently lock the table? Which table are lock? any command can we used to check it ? I noticed that default table look mode is page right? If we change to row lock mode, it will help us to reduce table lock? What is different between row lock & page lock, which situation good to used row lock? sample: for reference table means table that only used for select. Thank You **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.