Re: Locking on UPDATES

In article <337A34A4.333AA9A@frankfurtbalkind.com>, Clay Amerault <camerault@frankfurtbalkind.com> writes >I have a question on the type of lock placed on a row during an UPDATE >statement that doesn't use a cursor. > It places an exlusive lock and fails if anyone else has an exclusive or shared lock on the row. It also places an Intent Exclusive lock on the table (IX+HDR locK). This means no other user can drop the table or alter the column definitions for the table whilst the lock is held. >I read Sybase's white paper on this, and basically you can do an update >with a select of the nextkey value embedded in it. This prevents >another process from reading the nextkey, since the update has an >exclusive lock. I want to do the same thing using Informix, but am a >little confused about shared, promotable, and exclusive locks. If I do Can someone remind me what a promotable lock is? >the UPDATE statement and there is already a shared loack on the row, a >promotable lock will be placed on the row. Can another process do >SELECT on the row, placing a second shared lock on the row, and prevent >the promotable lock from being promoted to an exclusive lock? Could >this go on indefinitely. > >Also, I assume that I should set the isolation level to repeatable read >for this. Is this correct? > No - commited read and cursor stability will also work. Anything but dirty read which ignores locks. >Thanks for your help. > >-Clay Amerault -- David Williams