Informix Erorr - Key Value Locked
A user on 11.10 asked what "ISAM error: key value locked" means: with a page-level lock table, a second uncommitted insert failed, while row-level locking or dropping all indexes/primary key made the error disappear. Fernando Nunes and Art Kagel explained that page locks cover whole pages, and here the contention is on index/key pages rather than data pages (rows may land on different data pages but share an index page). Suggested workarounds: use SET LOCK MODE TO WAIT n in each session so brief locks are waited out, or switch the table to row-level locking. The poster's final question about keeping page locking given a 1346-byte row size went unanswered in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, what does "isam error: key value locked" mean when I get this error? my informix version is 11.10 When you say key value, does it pertain to the index key which is locked? Thanks Horacio
just to note, i tried doing two inserts. my table is lock mode page and it has a primary key and indexes insert record one don't commit insert record two isam error key value locked. When i set lock mode to row. insert record one don't commit insert record two no erorr encountered. here is another scenario.no index no primary key table lock mode page. insert record one don't commit insert record two success table lock mode row insert record one don't commit insert record two success
A page can hold several rows. When you use the page level lock the first insert (you don't commit) holds the lock for the whole page. Any other insert that lands on the same page will get the lock error. I haven't faced any real situation where lock page should be used (although I can imagine a scenario). Regards. On Wed, Feb 22, 2012 at 4:30 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > just to note, > > i tried doing two inserts. > my table is lock mode page and it has a primary key and indexes > > insert record one > don't commit > > insert record two > isam error key value locked. > > When i set lock mode to row. > insert record one > don't commit > > insert record two > no erorr encountered. > > here is another scenario.no index no primary key > > table lock mode page. > > insert record one > don't commit > > insert record two > success > > table lock mode row > > insert record one > don't commit > > insert record two > success > > > > ******************************************************************************* > 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... --00235452f638ec1d3d04b9904269
so the page locks may be on index pages? since when i dropped all indexes and primary keys, the error wa gone.
Yes. These locks are transient (lasting only a fraction of a second normally - unless your apps are poorly designed). The fix is to SET LOCK MODE TO WAIT <nseconds>; immediately after connecting to the database in your application so that the session threads wait <nseconds> for the locks to be released before returning an error if the lock is not released. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 22, 2012 at 11:18 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > Hi, > > what does "isam error: key value locked" mean when I get this error? > my informix version is 11.10 > > When you say key value, does it pertain to the index key which is locked? > > Thanks > Horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3baf0116030804b991adfb
Do the SET LOCK MODE TO WAIT 10; in both sessions before trying the inserts. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 22, 2012 at 11:30 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > just to note, > > i tried doing two inserts. > my table is lock mode page and it has a primary key and indexes > > insert record one > don't commit > > insert record two > isam error key value locked. > > When i set lock mode to row. > insert record one > don't commit > > insert record two > no erorr encountered. > > here is another scenario.no index no primary key > > table lock mode page. > > insert record one > don't commit > > insert record two > success > > table lock mode row > > insert record one > don't commit > > insert record two > success > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f2349cbac63cf04b991b135
Sorry that last got away from me. If you set LOCK MODE TO WAIT 10; then the second session will not get an error as long as the first one commits before 10 seconds elapse which is the normal behavior for a well designed application using optimistic locking protocols. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 22, 2012 at 11:30 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > just to note, > > i tried doing two inserts. > my table is lock mode page and it has a primary key and indexes > > insert record one > don't commit > > insert record two > isam error key value locked. > > When i set lock mode to row. > insert record one > don't commit > > insert record two > no erorr encountered. > > here is another scenario.no index no primary key > > table lock mode page. > > insert record one > don't commit > > insert record two > success > > table lock mode row > > insert record one > don't commit > > insert record two > success > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93404610dde4604b991b72e
yep, im setting lock mode to wait for both the applicstions before beginning them. the only thing is, why did the error not occur when i drop indexes and pks.... actually, i dropped all indexes and just retained the pk which of course has a generated uniquemindex wich i cant drop. the error stilloccurs... it only didnt occur when i dropped the pk as well. is it probably because the index pages are the ones locked and not the data pages? honestly,it seems thst the inserts behave serially in this scenario. i tried opening 19 other sessions and all ofthem waited for the first to commit. thanks
oh, and btw, if my lock mode is row, i dont encounter the locking issues.
Yes, because of the index page. The rows being inserted were probably being inserted onto separate pages so no row lock contention, only index key contention. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 22, 2012 at 2:03 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > yep, > > im setting lock mode to wait for both the applicstions before beginning > them. > the only thing is, why did the error not occur when i drop indexes and > pks.... > actually, i dropped all indexes and just retained the pk which of course > has a > generated uniquemindex wich i cant drop. the error stilloccurs... it only > didnt occur when i dropped the pk as well. > > is it probably because the index pages are the ones locked and not the data > pages? > > honestly,it seems thst the inserts behave serially in this scenario. > i tried opening 19 other sessions and all ofthem waited for the first to > commit. > > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3baf0134459604b9933890
so there is contention even if our rowsize is 1346? what we were told is that page locking doesnt matter because only one row fits into a page.... i asked regarding index page locks and they said that it wouldnt matter..... the recommendation is still to remain in page locking since it wouldnt matter because of the row size of the table... considering that our table has only selects and inserts, would there be any issue if we move to row locking? this is a transactional table btw....