Could not do a physical-order read to fetch next r

I am receiving this error at various places in our application (using J2EE with CMP as the persistence layer). "Could not do a physical-order read to fetch next row - Record is Locked" We are migrating from MySQL to IDS and never saw this exception in MySQL. Apparently the database is complaining about a row not available for read as it's being locked. I did a quick check in our application code and we are using indexes at the right places and things seem in order. The question is, is this an application ONLY or a database server ONLY issue or both? I am guessing it's both, there are things in the application that can cause this to happen but is there an easier database level setting that i can configure to have it go away. Some of the things i am considering are: allowing row level locks (as against the default page level locks), increasing the lock timeout to let's say a second so that the thread that's trying to read the data from a table or row that is locked waits this long before it gives up and throws the exception, allowing dirty reads. Any help or suggestion would be much appreciated. We are using IIF.10.00.UC5X4.Linux

> I am receiving this error at various places in our application (using J2EE > with CMP as the persistence layer). > "Could not do a physical-order read to fetch next row - Record is Locked" > We are migrating from MySQL to IDS and never saw this exception in MySQL. The result of 'sloppy locking'. > Apparently the database is complaining about a row not available for read as > it's being locked. I did a quick check in our application code and we are > using indexes at the right places and things seem in order. Try adding "IFX_LOCK_MODE_WAIT=1" to your JDBC connection URL. This sets your app to wait 1 second to see if it can acquire a lock, in many cases this is sufficient. You can also set IFX_ISOLATION_LEVEL=1 to enable dirty reads. http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.jd bc.doc/jdbc55.htm http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sq ls.doc/sqls817.htm What you specifically want depends upon your application and how mandatory what you present to the user be *absolutely* current. > The question is, is this an application ONLY or a database server ONLY issue > or both? I'd argue it is a false distinction. Locking is the interplay of the dababase server and all the applications/users connected to the server. What locking isolation/mode is appropriate can vary between applications connected to the same server. > I am guessing it's both, Yep. > there are things in the application that can > cause this to happen but is there an easier database level setting that i can > configure to have it go away. Some of the things i am considering are: > allowing row level locks (as against the default page level locks), Yep. > increasing > the lock timeout to let's say a second so that the thread that's trying to > read the data from a table or row that is locked waits this long before it > gives up and throws the exception, allowing dirty reads. Yep. > Any help or suggestion would be much appreciated. We are using > IIF.10.00.UC5X4.Linux

Chances you are doing a physical scan on the data pages which would be = a result of not running update statistics after loading the data. = "ANKUR SHAH" = <ankurdotshah@gma = il.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Could not do a physical-order re= ad 01/05/2007 01:21 to fetch next r [8126] = PM = = = Please respond to = ids@iiug.org = = = I am receiving this error at various places in our application (using J= 2EE with CMP as the persistence layer). "Could not do a physical-order read to fetch next row - Record is Locke= d" We are migrating from MySQL to IDS and never saw this exception in MySQ= L. Apparently the database is complaining about a row not available for re= ad as it's being locked. I did a quick check in our application code and we a= re using indexes at the right places and things seem in order. The question is, is this an application ONLY or a database server ONLY issue or both? I am guessing it's both, there are things in the application t= hat can cause this to happen but is there an easier database level setting that= i can configure to have it go away. Some of the things i am considering are: allowing row level locks (as against the default page level locks), increasing the lock timeout to let's say a second so that the thread that's trying= to read the data from a table or row that is locked waits this long before= it gives up and throws the exception, allowing dirty reads. Any help or suggestion would be much appreciated. We are using IIF.10.00.UC5X4.Linux ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

You are thinking correctly :) Two things to check: 1. Is the lock mode of the table set to "row" and not "page"? 2. Is there a WAIT time established for the application to "sleep" while waiting for a locked record to be freed? I would check those two items, since you have already checked the query to make sure it is using established indices (and hopefully update statistics have been executed as well). Change it for the current table only and if it works as you wish, you may then want to perform alter table statements for the remaining tables. You only want to set isolation to dirty read when it comes to "point-in-time" reports; otherwise, you want to at least the normal database default of "repeatable read" (if I remember right). Take care. Clifton -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ANKUR SHAH Sent: Friday, January 05, 2007 1:22 PM To: ids@iiug.org Subject: Could not do a physical-order read to fetch next r [8126] I am receiving this error at various places in our application (using J2EE with CMP as the persistence layer). "Could not do a physical-order read to fetch next row - Record is Locked" We are migrating from MySQL to IDS and never saw this exception in MySQL. Apparently the database is complaining about a row not available for read as it's being locked. I did a quick check in our application code and we are using indexes at the right places and things seem in order. The question is, is this an application ONLY or a database server ONLY issue or both? I am guessing it's both, there are things in the application that can cause this to happen but is there an easier database level setting that i can configure to have it go away. Some of the things i am considering are: allowing row level locks (as against the default page level locks), increasing the lock timeout to let's say a second so that the thread that's trying to read the data from a table or row that is locked waits this long before it gives up and throws the exception, allowing dirty reads. Any help or suggestion would be much appreciated. We are using IIF.10.00.UC5X4.Linux **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Definitely change to row level locking (This is done at the database level). With page locking, an update on any of the records stored within that page will lock the remaining records until the data is committed. Also check the isolation level used. Committed read will wait for the locks to be removed. Dirty read will allow the records to be read. Read about and understand the implications of these settings (done at the application level, not database level). Cheers Jonathan Little Gardenstuff Ltd 59 Winchester St Merivale Christchurch Website: www.gstuff.co.nz Email: flowers@gstuff.co.nz Tel: 021 1666100 Gardenstuff, All the flower seeds you will ever want, the widest range of spectacular flowers -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ANKUR SHAH Sent: 06 January 2007 08:22 To: ids@iiug.org Subject: Could not do a physical-order read to fetch next r [8126] I am receiving this error at various places in our application (using J2EE with CMP as the persistence layer). "Could not do a physical-order read to fetch next row - Record is Locked" We are migrating from MySQL to IDS and never saw this exception in MySQL. Apparently the database is complaining about a row not available for read as it's being locked. I did a quick check in our application code and we are using indexes at the right places and things seem in order. The question is, is this an application ONLY or a database server ONLY issue or both? I am guessing it's both, there are things in the application that can cause this to happen but is there an easier database level setting that i can configure to have it go away. Some of the things i am considering are: allowing row level locks (as against the default page level locks), increasing the lock timeout to let's say a second so that the thread that's trying to read the data from a table or row that is locked waits this long before it gives up and throws the exception, allowing dirty reads. Any help or suggestion would be much appreciated. We are using IIF.10.00.UC5X4.Linux **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.