RE: update impossible in spite of row locking
A user reported that with row-level locking, an uncommitted UPDATE in one session blocked UPDATEs of *other* rows in a second session, raising error 244 / ISAM 107 'record is locked' on IDS 11.50.FC3 and 9.40.FC9. Respondents asked for more detail (isolation level, why locks are held so long) and suspected application design. Madison Pruet confirmed adjacent-key locking was dropped in 6.0 and only ever applied to deleted keys, not updates. A side discussion on repeatable read followed, with Fernando Nunes showing via onstat -k that RR range scans take a lock on the 'infinity' rowid. No resolution of the original problem is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Color me silly...
But does row level locking really only lock the row being updated or does it also put a lock on the adjacent row.
If memory serves me there was an issue on this going way, way, way back to the Hyatt project (Before most everyone who post here's time.) Dana L. gave a presentation on this, back in the turbo/Online 5 days.
But this doesn't solve your problem....
I guess we could suppose that if the locks occurred on only the one row, that you'd still have problems if two users try to update the same row at the same time.
I think OTC has it right... take a 'clue by 4' upside your developer's head.
What sort of application is this and why are you holding locks for so long?
What is the isolation level of your reads?
Without knowing more, its possible that you could have a very high volume OLTP system and unless you revisit your application design, you're holding the locks for way to long. (Or you're really doing a very, very fast OLTP app.)
Please provide some more information.
Thx
-G
> From: RHabichtsberg@arz-emmendingen.de
> To: informix-list@iiug.org
> Subject: update impossible in spite of row locking
> Date: Tue, 23 Jun 2009 11:42:12 +0200
>
> Hi all,
>
> we have a strange behaviour of IDS while trying to update rows. IDS Version
> 11.50.FC3 and 9.40.FC9.
>
> The table has row locking, unique index and primary key. The database is in
> logging mode buffered.
>
> We open two sessions: In the first session we open a transaction and update
> a certain row selected by the primary key. The transaction is not yet
> commited.
>
> In the second session we try to update certain other rows as well
> selected by the primary key. Some rows could be updated. But with some rows
> it appears following error:
> 244: Could not do a physical-order read to fetch next row.
> 107: ISAM error: record is locked.>
> Why does this happen? We assumed that row locking would only lock the one
> row of session 1. But obviously the update statement of session 2 is blocked
> by a lock.
>
> How can we avoid such a situation (it simulates that a user session is in a
> transaction while the user isn't able to finish it).
>
> May be we are understanding sonething wrong, but our software developer
> affirm that many of our programs depend on the ability of DBMS to update
> several rows of a table simultaneously even though a transaction on the
> table hangs.
>
> Can anybody help? It's rather urgend.
>
> TIA,
> Reinhard.
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
_________________________________________________________________
Hotmail® has ever-growing storage! Don’t worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009
Ian Michael Gumby wrote:
> Color me silly...
>
> But does row level locking really only lock the row being updated or
> does it also put a lock on the adjacent row.
We stopped using adjacent key locking in 6.0. Also - adjacent key
locking only was used if a key was deleted, not when a row was updated.
> If memory serves me there was an issue on this going way, way, way back
> to the Hyatt project (Before most everyone who post here's time.) Dana
> L. gave a presentation on this, back in the turbo/Online 5 days.
>
> But this doesn't solve your problem....
>
> I guess we could suppose that if the locks occurred on only the one row,
> that you'd still have problems if two users try to update the same row
> at the same time.
>
> I think OTC has it right... take a 'clue by 4' upside your developer's head.
> What sort of application is this and why are you holding locks for so long?
> What is the isolation level of your reads?
>
> Without knowing more, its possible that you could have a very high
> volume OLTP system and unless you revisit your application design,
> you're holding the locks for way to long. (Or you're really doing a
> very, very fast OLTP app.)
>
> Please provide some more information.
>
> Thx
>
> -G
>
>
> > From: RHabichtsberg@arz-emmendingen.de
> > To: informix-list@iiug.org
> > Subject: update impossible in spite of row locking
> > Date: Tue, 23 Jun 2009 11:42:12 +0200
> >
> > Hi all,
> >
> > we have a strange behaviour of IDS while trying to update rows. IDS
> Version
> > 11.50.FC3 and 9.40.FC9.
> >
> > The table has row locking, unique index and primary key. The database
> is in
> > logging mode buffered.
> >
> > We open two sessions: In the first session we open a transaction and
> update
> > a certain row selected by the primary key. The transaction is not yet
> > commited.
> >
> > In the second session we try to update certain other rows as well
> > selected by the primary key. Some rows could be updated. But with
> some rows
> > it appears following error:
> > 244: Could not do a physical-order read to fetch next row.
> > 107: ISAM error: record is locked.> >
> > Why does this happen? We assumed that row locking would only lock the one
> > row of session 1. But obviously the update statement of session 2 is
> blocked
> > by a lock.
> >
> > How can we avoid such a situation (it simulates that a user session
> is in a
> > transaction while the user isn't able to finish it).
> >
> > May be we are understanding sonething wrong, but our software developer
> > affirm that many of our programs depend on the ability of DBMS to update
> > several rows of a table simultaneously even though a transaction on the
> > table hangs.
> >
> > Can anybody help? It's rather urgend.
> >
> > TIA,
> > Reinhard.
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
>
> ------------------------------------------------------------------------
> Hotmail' has ever-growing storage! Don't worry about storage limits.
> Check it out.
> <http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009>;
> Date: Tue, 23 Jun 2009 07:03:59 -0500 > From: mpruet1@verizon.net > To: im_gumby@hotmail.com > CC: rhabichtsberg@arz-emmendingen.de; informix-list@iiug.org > Subject: Re: update impossible in spite of row locking > > Ian Michael Gumby wrote: > > Color me silly... > > > > But does row level locking really only lock the row being updated or > > does it also put a lock on the adjacent row. > > We stopped using adjacent key locking in 6.0. Also - adjacent key > locking only was used if a key was deleted, not when a row was updated. > > Yeah I figured something like that. Again please note that one of my gray cells ticked back to the turbo days of the initial Hyatt reservation system. (I'm sorry but there really is a lot of information locked away in my brain, but unfortunately, the recall mechanism doesn't always work when we want it to. (Like suddenly remembering where they buried Hoffa... ;-) ) > > If memory serves me there was an issue on this going way, way, way back > > to the Hyatt project (Before most everyone who post here's time.) Dana > > L. gave a presentation on this, back in the turbo/Online 5 days. > > See what I mean? I forgot that I already wrote that. _________________________________________________________________ Bing™ brings you maps, menus, and reviews organized in one place. Try it now. http://www.bing.com/search?q=restaurants&form=MLOGEN&publ=WLHMTAG&crea=TEXT_MLOGEN_Core_tagline_local_1x1
Madison Pruet wrote: > Ian Michael Gumby wrote: >> Color me silly... >> >> But does row level locking really only lock the row being updated or >> does it also put a lock on the adjacent row. > > We stopped using adjacent key locking in 6.0. Also - adjacent key > locking only was used if a key was deleted, not when a row was updated. > Hmmm... I could dig... but what about RR? We must make sure no new rows that match are allowed...? -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Fernando Nunes wrote: > Madison Pruet wrote: >> Ian Michael Gumby wrote: >>> Color me silly... >>> >>> But does row level locking really only lock the row being updated or >>> does it also put a lock on the adjacent row. >> >> We stopped using adjacent key locking in 6.0. Also - adjacent key >> locking only was used if a key was deleted, not when a row was updated. >> > > Hmmm... I could dig... but what about RR? We must make sure no new rows > that match are allowed...? > > RR uses the lock manager. If an RR read hits an index there is no reason to lock the adjacent key. We just lock the hit key itself.
Madison Pruet wrote: > Fernando Nunes wrote: >> Madison Pruet wrote: >>> Ian Michael Gumby wrote: >>>> Color me silly... >>>> >>>> But does row level locking really only lock the row being updated or >>>> does it also put a lock on the adjacent row. >>> >>> We stopped using adjacent key locking in 6.0. Also - adjacent key >>> locking only was used if a key was deleted, not when a row was updated. >>> >> >> Hmmm... I could dig... but what about RR? We must make sure no new >> rows that match are allowed...? >> >> > RR uses the lock manager. If an RR read hits an index there is no > reason to lock the adjacent key. We just lock the hit key itself. I'm not sure if I'm following you... I meant repeatable read. That's what you understood? -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Fernando Nunes wrote:
> Madison Pruet wrote:
>> Fernando Nunes wrote:
>>> Madison Pruet wrote:
>>>> Ian Michael Gumby wrote:
>>>>> Color me silly...
>>>>>
>>>>> But does row level locking really only lock the row being updated
>>>>> or does it also put a lock on the adjacent row.
>>>>
>>>> We stopped using adjacent key locking in 6.0. Also - adjacent key
>>>> locking only was used if a key was deleted, not when a row was updated.
>>>>
>>>
>>> Hmmm... I could dig... but what about RR? We must make sure no new
>>> rows that match are allowed...?
>>>
>>>
>> RR uses the lock manager. If an RR read hits an index there is no
>> reason to lock the adjacent key. We just lock the hit key itself.
>
> I'm not sure if I'm following you... I meant repeatable read. That's
> what you understood?
>
Nevermind... I tested it...:
IBM Informix Dynamic Server Version 11.50.TC1 -- On-Line -- Up 00:05:30 --160640 Kbytes
Locks
address wtlist owner lklist same type tblsnum rowid key#/bsiz
c1dd680 0 13f23108 0 c1dd920 HDR+S 100002 202 0
c1dd8c0 0 13f22578 0 0 S 100002 202 0
c1dd920 0 13f236d0 0 c1dd8c0 S 100002 202 0
c1dd980 0 13f23c98 0 c1de040 HDR+S 100002 206 0
c1ddaa0 0 13f24260 c1de100 0 HDR+X 1001e7 106 K- 1 I
c1ddbc0 13f24260 13f23c98 c1ddc80 0 HDR+SR 1001e7 ffffffff K- 1
c1ddc80 0 13f23c98 c1ddd40 0 HDR+SR 1001e7 105 K- 1
c1ddce0 0 13f23c98 c1dd980 c1de1c0 HDR+IS 1001e6 0 0
c1ddd40 0 13f23c98 c1ddce0 0 HDR+SR 1001e7 104 K- 1
c1de040 0 13f24260 0 0 S 100002 206 0
c1de100 0 13f24260 c1de1c0 0 HDR+X 1001e6 106 0 I
c1de1c0 0 13f24260 c1de040 0 IX 1001e6 0 0
12 active, 20000 total, 16384 hash buckets, 0 lock table overflows
The SELECT is using "key > 4" and the INSERT is trying to insert key value 8.
So we lock the rowid "fff...."
This is getting a bit out of topic...
Thanks and regards.b
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...