Transaction Locking and Indexes
A 4GL app running concurrently at multiple sites hit "Lock Timeout Expired" (-244/-154) on DELETE/UPDATE statements filtering on one column, even with row-level locking and dirty read isolation; adding an index on that column fixed it. Suggestions included dirty read, UPDATE STATISTICS HIGH, optimizer directives and fragmenting by that column. Art Kagel explained the cause: without an index each delete does a full table scan, briefly locking every row and contending on page/LRU latches, so sessions queue and eventually time out; an index lets sessions go straight to their rows. The problem only appeared after a dbexport/dbimport because the reloaded data is denser (more rows per page). Resolution: add the index.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have an application which reports Lock Time Outs within transactions. The
place where the crash occurs is on a simple where clause using 1 column. I
have checked and row locking is enabled. While the code is very simple it does
loop to add many rows and then update many row and then deletes a number of
rows.
INSERT INTO table VALUES(....)
UPDATE table set row2 = new value WHERE row1 = value
DELETE FROM table WHERE row1 = value
The application uses the table(s) in the order above and multiple copies of
the application are running. The row value will be unique for each instance of
the application running. Its a location code and only one copy of the
application can run per location. If two copies of the application clash the
application crashes the delete statement with the following:-
Program error at 'l_linkslib.4gl', line number 453.
SQL statement error number -244.
Could not do a physical-order read to fetch next row.
SYSTEM error number -154.
ISAM error: Lock Timeout Expired
If I now add an index to the row being used in the where clause the crash is
resolve.
Is this the correct action for IDS?? Do you need indexes??
Hi,
use "set isolation to dirty read", this will prevent the internal problems
while reading the data to update/delete .
Marcus Haarmann
----- Ursprüngliche Mail -----
Von: "DAVID SIBLEY" <d.sibley@bms.co.uk>
An: ids@iiug.org
Gesendet: Donnerstag, 11. Februar 2016 13:36:26
Betreff: Transaction Locking and Indexes [36543]
I have an application which reports Lock Time Outs within transactions. The
place where the crash occurs is on a simple where clause using 1 column. I
have checked and row locking is enabled. While the code is very simple it does
loop to add many rows and then update many row and then deletes a number of
rows.
INSERT INTO table VALUES(....)
UPDATE table set row2 = new value WHERE row1 = value
DELETE FROM table WHERE row1 = value
The application uses the table(s) in the order above and multiple copies of
the application are running. The row value will be unique for each instance of
the application running. Its a location code and only one copy of the
application can run per location. If two copies of the application clash the
application crashes the delete statement with the following:-
Program error at 'l_linkslib.4gl', line number 453.
SQL statement error number -244.
Could not do a physical-order read to fetch next row.
SYSTEM error number -154.
ISAM error: Lock Timeout Expired
If I now add an index to the row being used in the where clause the crash is
resolve.
Is this the correct action for IDS?? Do you need indexes??
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I assume that initially the table is empty and there are no distributions.
Is there an index on the "row1" column? If not that MAY help. If there is
an index already, you can try either Marcus's suggestion and set DIRTY READ
isolation or update statistics high on "row1" between loading the rows and
the other operations. Alternatively you can try using an optimizer
directive to force the use of tbe index in the update and selete
statements. Final option partition the table by "row1" values to eliminate
contention.
Art
On Feb 11, 2016 7:37 AM, "DAVID SIBLEY" <d.sibley@bms.co.uk> wrote:
> I have an application which reports Lock Time Outs within transactions. The
> place where the crash occurs is on a simple where clause using 1 column. I
> have checked and row locking is enabled. While the code is very simple it
> does
> loop to add many rows and then update many row and then deletes a number of
> rows.
>
> INSERT INTO table VALUES(....)>
> UPDATE table set row2 = new value WHERE row1 = value>
> DELETE FROM table WHERE row1 = value>
> The application uses the table(s) in the order above and multiple copies of
> the application are running. The row value will be unique for each
> instance of
> the application running. Its a location code and only one copy of the
> application can run per location. If two copies of the application clash
> the
> application crashes the delete statement with the following:-
>
> Program error at 'l_linkslib.4gl', line number 453.
> SQL statement error number -244.
> Could not do a physical-order read to fetch next row.
> SYSTEM error number -154.
> ISAM error: Lock Timeout Expired>
> If I now add an index to the row being used in the where clause the crash
> is
> resolve.
>
> Is this the correct action for IDS?? Do you need indexes??
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bea43fce96557052b7e16a3
Many thanks for the responses although the detail below may explain the issue a little better. The database is in transnational mode. I have set lock mode to wait 30 and the isolation level is dirty read. The transaction running within the multiple copies of the application, one at each location should not clash as the select is unique to each site, the transactions should be unique. The real question is why without an index on the column being used in the where clause do I get the error outlined and when I add the index on the column does the error cease and the delete statement executes successfully. Is there something in the database set-up which would cause this issue?
David: OK, here's what's happening: Without the index every delete has to scan the entire table looking for matching rows to delete. Each row as it is examined has to be locked, dirty read or no dirty read, for a fraction of a second. As multiple scans zip through the table it is inevitable that one or more will encounter a row that's locked. If multiple sessions have to delete multiple rows on the same page they will all have to latch the same page in the cache and so will queue up behind one another each holding a lock on a different row on that one page. They will single thread on the cache page latch and on the LRU latch needed to move the page from the clean part of the LRU queue to the dirty part or from the middle of the dirty queue to the most recently used end of the dirty queue. All that time the N+1st session is waiting for a row lock on one of the rows in that frozen page to clear so it can read that row and continue its scan. Eventually, sometimes, the last waiter times out before it gets access to the locks or latches that it needs. This is one of the few cases in which page locks MIGHT perform better, but I doubt it. The index almost eliminates the problem because that waiter who doesn't need to update another row on the same cache page is able to skip over the page locks because it is scanning the index instead which is not being locked and which it does not have to acquire a lock for. 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 Thu, Feb 11, 2016 at 9:54 AM, DAVID SIBLEY <d.sibley@bms.co.uk> wrote: > Many thanks for the responses although the detail below may explain the > issue > a little better. > > The database is in transnational mode. I have set lock mode to wait 30 and > the > isolation level is dirty read. > > The transaction running within the multiple copies of the application, one > at > each location should not clash as the select is unique to each site, the > transactions should be unique. > > The real question is why without an index on the column being used in the > where clause do I get the error outlined and when I add the index on the > column does the error cease and the delete statement executes successfully. > > Is there something in the database set-up which would cause this issue? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013a2268fd6bb7052b800208
Hello Art
I guessed it was something like you describe. While I was happy to simply add
indexes I felt I needed an answer to corroborate my thoughts.
The strange thing about this problem is it appears to have only been
identified since the database was unloaded and re-loaded using dbexport and
dbimport. The system has been running for 3 years and I don't believe I have
seen this error prior to this. I have checked the schema and there were no
invoices on the offending table/columns previously.
I will pursue adding indexes.
Thanks for you input.
The data is more compact since the reload, so more data on a page.
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 Thu, Feb 11, 2016 at 11:42 AM, DAVID SIBLEY <d.sibley@bms.co.uk> wrote:
> Hello Art
>
> I guessed it was something like you describe. While I was happy to simply
> add
> indexes I felt I needed an answer to corroborate my thoughts.
>
> The strange thing about this problem is it appears to have only been
> identified since the database was unloaded and re-loaded using dbexport and
> dbimport. The system has been running for 3 years and I don't believe I
> have
> seen this error prior to this. I have checked the schema and there were no
> invoices on the offending table/columns previously.
>
> I will pursue adding indexes.
>
> Thanks for you input.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113eb3249d1a84052b82a4b1