Record Locking and Update Statistics
Ray saw frequent lock/deadlock (error 143) problems on a row-level-locked table whose row count swings from ~5000 down to nearly empty each week; running UPDATE STATISTICS LOW made the problem go away. Respondents explained Informix does not escalate row locks to table locks; rather, stale statistics taken when the table was nearly empty led the optimizer to choose sequential scans, which (with Repeatable Read/Cursor Stability) lock far more rows than an indexed read. Advice: gather distributions when the table is fullest, exclude such tables from routine update stats/AUS, or use query hints. Ray adjusted his update stats routines and reported the issue handled, though he couldn't reproduce it in a test setup.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have had a number of situations where I have experienced a large number of record locks in our application which have been difficult to diagnose. However I have found that an update statistics low on the database causes the lock issues to disappear. I am struggling to understand the relationship between the update statistics command and the issuing of locks on a given table. Can anyone shed any light on what this relationship is? I am using 12.10.
Hi Ray, Are you using Repeatable Read or Cursor Stability Isolation Modes? If you are, and you end up scanning a table for a few records, you are going to end up with way more locks than if you do an indexed read and read just a few records. Obviously whether the engine does a tables scan versus an indexed read can be influenced by statistics. That's all that I can think of right now... Mike Walker Advanced DataTools Corporation -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of RAY BURNS Sent: Sunday, August 28, 2016 5:27 PM To: ids@iiug.org Subject: Record Locking and Update Statistics [37671] I have had a number of situations where I have experienced a large number of record locks in our application which have been difficult to diagnose. However I have found that an update statistics low on the database causes the lock issues to disappear. I am struggling to understand the relationship between the update statistics command and the issuing of locks on a given table. Can anyone shed any light on what this relationship is? I am using 12.10. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Please make sure to run update statistics outside an explicit transaction. If you run it in an explicit transaction then you must be able to rollback which means we must hold all lock till the commit or rollback is provided. If you run it in an implicit transaction (without a begin/commit) then a transaction is completed after each object (i.e. table/procedure). John F. Miller III miller3@us.ibm.com 503-747-1366 ids-bounces@iiug.org wrote on 08/28/2016 05:36:52 PM: > From: "Mike Walker" <mike@advancedatatools.com> > To: ids@iiug.org > Date: 08/28/2016 05:37 PM > Subject: RE: Record Locking and Update Statistics [37672] > Sent by: ids-bounces@iiug.org > > Hi Ray, > > Are you using Repeatable Read or Cursor Stability Isolation Modes? If you > are, and you end up scanning a table for a few records, you are going to end > up with way more locks than if you do an indexed read and read just a few > records. Obviously whether the engine does a tables scan versus an indexed > read can be influenced by statistics. That's all that I can think of right > now... > > Mike Walker > Advanced DataTools Corporation > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of RAY > BURNS > Sent: Sunday, August 28, 2016 5:27 PM > To: ids@iiug.org > Subject: Record Locking and Update Statistics [37671] > > I have had a number of situations where I have experienced a large number of > record locks in our application which have been difficult to diagnose. > However I have found that an update statistics low on the database causes > the lock issues to disappear. I am struggling to understand the relationship > between the update statistics command and the issuing of locks on a given > table. Can anyone shed any light on what this relationship is? > > I am using 12.10. > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >
In this particular instance the application is adding and updating and deleting records to a file which is defined with row level locking. The record count is going up and down constantly. It will peek at about 5000 records and by the end of the week there will be a very small number. On Monday mornings the application is constantly getting deadlock errors (143) even though we have confirmed that the sessions receiving the errors are locking different records. We do an update statistics and the lock errors disappear. There is a suggestion that when there is a small number of records, the engine is deciding to issue a table lock rather than a row lock in order to improve performance. I have never heard of this, but it is certainly consistent with symptoms. Have you heard of such a behaviour and if so, do you know of any way of stopping it doing this? Ray
No, there is no promotion of row locks to table locks in Informix, but if there are very few rows the engine may decide to perform a table scan rather than use an index which will lock more rows. At least intermittently. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Sep 20, 2016 at 4:03 PM, RAY BURNS <ray.burns@velocityglobal.co.nz> wrote: > In this particular instance the application is adding and updating and > deleting records to a file which is defined with row level locking. The > record > count is going up and down constantly. It will peek at about 5000 records > and > by the end of the week there will be a very small number. On Monday > mornings > the application is constantly getting deadlock errors (143) even though we > have confirmed that the sessions receiving the errors are locking different > records. We do an update statistics and the lock errors disappear. > > There is a suggestion that when there is a small number of records, the > engine > is deciding to issue a table lock rather than a row lock in order to > improve > performance. > > I have never heard of this, but it is certainly consistent with symptoms. > Have > you heard of such a behaviour and if so, do you know of any way of > stopping it > doing this? > > Ray > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b6700651e38c8053cf61f9c
Thanks Art, Again that would be consistent with symptoms. Can you suggest anything we could do to stop this from happening?
As Art wrote, for better and for worse Informix does not do lock escalation. But apparently your situation is very common, seems very obvious ans is terribly easy to solve. You're probably doing daily(?) update stattistics... and as you say, by the end of the week the table has very few records. So the engine thinks doing a sequential scan is a very good plan (the query plans consider efficiency, not concurrency). If the table has in fact very few records and if your sessions have LOCK MODE WAIT N (N being a very small number) it may work. But if the table becomes bigger (more records) the sequentail plan is a bad thing... (in fact, in most cases it's always a bad thing considering concurrency). This is solved by running update statistics when the table has more records, because by then a sequential plan doesn't seem good. The solution? At leas a couple: 1- Use an hint on the query. I personally don't like this. It tend to "stick around". Developers tend to think it's a "normal thing" (which may be true in other RDBMS who invented the hints, because they needed to, considering their optimizer is/was very bad), and it may become useless if you change something, like the index names etc. 2- Stop running update statistics on those tables... Run it once when they have a "medium" number of records and then include them in an exclusion list used by your update statistics process. Art's dostats and my dbs_updstats/tbl_updstats allow this. I'd need to re-check the AUTO UPDATE statistics in their latest versions to understand how to do this (a dirty trick would be to delete the entries created by the evaluator that refer to those tables before statistics are effectively calculated) So it seems everyting is working as "planned", but not as desired :) Hope this helps. On Tue, Sep 20, 2016 at 9:03 PM, RAY BURNS <ray.burns@velocityglobal.co.nz> wrote: > In this particular instance the application is adding and updating and > deleting records to a file which is defined with row level locking. The > record > count is going up and down constantly. It will peek at about 5000 records > and > by the end of the week there will be a very small number. On Monday > mornings > the application is constantly getting deadlock errors (143) even though we > have confirmed that the sessions receiving the errors are locking different > records. We do an update statistics and the lock errors disappear. > > There is a suggestion that when there is a small number of records, the > engine > is deciding to issue a table lock rather than a row lock in order to > improve > performance. > > I have never heard of this, but it is certainly consistent with symptoms. > Have > you heard of such a behaviour and if so, do you know of any way of > stopping it > doing this? > > Ray > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11426122d7eaea053cf765bd
Generate data distributions when the tables are fullest and leave them be until the table's full again. Watch out for AUS running when the table is empty. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Sep 20, 2016 at 4:27 PM, RAY BURNS <ray.burns@velocityglobal.co.nz> wrote: > Thanks Art, Again that would be consistent with symptoms. Can you suggest > anything we could do to stop this from happening? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c1943484c7516053cf76b14
Many thanks for your suggestions. I have a altered all the update stats routines and have a process in place to resolve the problem. However, I created some test programs and tables on another machine to attempt to duplicate the problem. Unfortunately I can't seem to repeat the error. I wonder if someone can tell me how the engine handles the locks when a table scan is performed on table defined with LOCK MODE ROW? I can't find this anywhere in the documentation. I've read the locking chapter in the Performance guide, the SQLS and the developers handbook redbook. Ray
It's going depend on the isolation level you are using (committed read, dirty read, etc) and whether you are updating the row or not. If you are using lock mode row, then any updates/inserts/deletes of a SINGLE record, will lock that one record only. A SELECT (NOT select in an update cursor) will not lock any rows if using dirty read or committed read, but cursor stability will lock the last row read, and repeatable read will lock every row it reads. So if you have an isolation level of repeatable read and then perform a select which performs a full table scan, then a shared lock will be put on all rows for the duration of the transaction. Regardless of a table scan or not, when the table is defined with lock mode row, Informix will track each lock of each row - it won't record it as a table lock or anything like that. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of RAY BURNS Sent: Thursday, September 22, 2016 2:53 PM To: ids@iiug.org Subject: Re: RE: Record Locking and Update Statistics [37869] Many thanks for your suggestions. I have a altered all the update stats routines and have a process in place to resolve the problem. However, I created some test programs and tables on another machine to attempt to duplicate the problem. Unfortunately I can't seem to repeat the error. I wonder if someone can tell me how the engine handles the locks when a table scan is performed on table defined with LOCK MODE ROW? I can't find this anywhere in the documentation. I've read the locking chapter in the Performance guide, the SQLS and the developers handbook redbook. Ray **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Not sure exactly what you're asking... A table scan readas all the used pages in a table. If there are rows being changed which are not yet committed when the scan goes through the pages they will have a lock The behavior depends on the isolation level and lock mode of the reading session. A DIRTY READ will ignore the lock and will read the last version of the row (which is not committed) A COMMITTED READ wil take the behavior defined in the LOCK MODE: - NOT WAIT (default) raises an error - WAIT N will wiat up to N seconds. If the lock is still there after N seconds it will raise an error - WAIT will wait forever until the lock is released A COMMITTED READ LAST COMMITTED wil behave depending on the operation that caused the lock: - if it was an INSERT the row is ignored - If it was a DELETE the row will be considered - If it was an UPDATE the last commited image of the row is retrived from the logical logs Regards On Thu, Sep 22, 2016 at 9:52 PM, RAY BURNS <ray.burns@velocityglobal.co.nz> wrote: > Many thanks for your suggestions. I have a altered all the update stats > routines and have a process in place to resolve the problem. > > However, I created some test programs and tables on another machine to > attempt > to duplicate the problem. Unfortunately I can't seem to repeat the error. > > I wonder if someone can tell me how the engine handles the locks when a > table > scan is performed on table defined with LOCK MODE ROW? I can't find this > anywhere in the documentation. I've read the locking chapter in the > Performance guide, the SQLS and the developers handbook redbook. > > Ray > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c07e8060d7a43053dff1a55