IDS Forum

RE: RE: Record Locking and Update Statistics

Posted By: Mike Walker
Date: Monday, 3 October 2016, at 7:15 p.m.

In Response To: Re: RE: Record Locking and Update Statistics (RAY BURNS)

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.

Messages In This Thread

• Record Locking and Update Statistics
  RAY BURNS -- Sunday, 28 August 2016, at 7:26 p.m.
  • RE: Record Locking and Update Statistics
    Mike Walker -- Sunday, 28 August 2016, at 8:36 p.m.
    • RE: Record Locking and Update Statistics
      John Miller iii -- Monday, 29 August 2016, at 12:29 p.m.
    • Re: RE: Record Locking and Update Statistics
      RAY BURNS -- Tuesday, 20 September 2016, at 4:02 p.m.
      • Re: RE: Record Locking and Update Statistics
        Art Kagel -- Tuesday, 20 September 2016, at 4:17 p.m.
        • Re: RE: Record Locking and Update Statistics
          RAY BURNS -- Tuesday, 20 September 2016, at 4:27 p.m.
          • Re: RE: Record Locking and Update Statistics
            Art Kagel -- Tuesday, 20 September 2016, at 5:50 p.m.
        • Re: RE: Record Locking and Update Statistics
          RAY BURNS -- Thursday, 22 September 2016, at 4:52 p.m.
          • RE: RE: Record Locking and Update Statistics
            Mike Walker -- Monday, 3 October 2016, at 7:15 p.m.
          • Re: RE: Record Locking and Update Statistics
            Fernando Nunes -- Monday, 3 October 2016, at 8:27 p.m.
      • Re: RE: Record Locking and Update Statistics
        Fernando Nunes -- Tuesday, 20 September 2016, at 5:49 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.