row level locking and dead-lock prevention

row level locking and dead-lock prevention

Post by Jonathan Leffl » Thu, 20 Oct 1994 01:13:22




>> I have a question with respect to locking, dead-lock prevention and row
>> (or key) level locking. [...]  We are designing a automatic message
>> exchange system.  Some data is to be maintained and used [...]  Our
>> customer wanted us to use Informix [...]
>> There are [few] different [...] transaction [types].  Transactions are
>> very short, no human interaction.  Different transactions access tables
>> in different sequences as the logic of these transactions demand.  I'm
>> afraid that if nothing is done to prevent, dead-lock is possible to
>> occur.  Due to the required real-time behaviour, dead lock repair
>> (rollback and retry) is out of the question.
>> [..] it seems possible to use a locking scheme in which locks for
>> required rows are acquired in a pre-defined order. [...]
>> This could be done on table level (LOCK TABLE), but I don't see how this
>> can be achieved using row level locking, since this seems only possible
>> to take place implicitly during execution of the query logic.
>> Because of the short transaction time, table level locking might produce
>> sufficient throuhgput, but I'm not (yet) sure about this.
>> Are my fears about deadlock correct?
>> Is there a way, or work around, to lock on row level explicitly?

If you are worried about locking problems in the scenario you describe,
I cannot think of a much better way of ensuring that you run into locking
problems than applying table locks.  That guarantees conflicts!

I assume that your engine will be OnLine, and that the database will have a
transaction log on it.  If not, think again.

Your schema will be careful to ensure that most (probably all) tables use
row-level locking rather than page level locking.  This ensures maximum
concurrency with minimum lock contention.  When doing INSERT operations,
there are minimal locking problems.  When doing DELETE operations, other
transactions should not be trying to access the deleted rows.  So only
UPDATE operations should cause any problems.  Your code will be written to
use cursors with the FOR UPDATE clause.  These are usable only inside a
transaction.  You will automatically lock the rows as you fetch them with
this cursor, and the row will be updated using WHERE CURRENT OF.  You'll be
designing your code to identify exactly the rows that need to be updated,
not scanning large numbers of rows in the application to see what might
need updating.  The remaining issue is: how often will two different
transactions need to update the same row?  The answer had better be "not
often", otherwise you have an inherently non-concurrent, non-real-time
design.  Set the lock mode to wait with a timeout.  Since all your
transactions are short, your timeout can be short too.  If there is a
single table where there is contention, then try to ensure that it is
processed last in each transaction, so that the rows are locked for the
minimum possible time.

Yours,

 
 
 

row level locking and dead-lock prevention

Post by Jim Gord » Thu, 20 Oct 1994 01:43:11


Online does have deadlock detection and will abort transactions it
finds in deadlock.  It doesn't have the ability to restart the
transaction.   That is left upto the programmer, who may not want to
do a restart.

In Bart's original question though the discussion is about an
automated system that must recover from a deadlock position.
Normally the easiest way to handle this is to arrange the design
so that a deadlock condition never occurs.

Cheers - Jim
My opinions are my own.  They may vary with time but they remain MINE!
----------------------------------------------------------------------
Name: Jim Gordon         Company: DHL Systems Inc, Burlingame, CA, USA
----------------------------------------------------------------------

} I'm not an expert on this, but I assume the DBMS will handle deadlock
} for you automatically  - either by avoiding it or by detecting it and
} aborting and restarting the transaction.  The exception would be
} if you're using Informix SE and the CISAM library.
}
} I hope somebody will confirm or deny this assumption.
}

}

} > Hi, informix news readers!
} >
} > We are designing a automatic message exchange system. Some
} > data is to be maitained and used; this is where informix comes in.
} > Our customer wanted us to use Informix, no choice here....
} >
} > There are a limmited ammount of different possible transactions
} > on this database. Transactions are very short, no human interaction.
} >
} > Due to the limmited ammount of different queries and update
} > actions, it seems possible to use a locking scheme in which
} > locks for required rows are aquired in a pre-defined order.
} > Old fashioned perhaps, but save......


 
 
 

row level locking and dead-lock prevention

Post by Andy Ken » Fri, 21 Oct 1994 05:14:56


> Online does have deadlock detection and will abort transactions it
> finds in deadlock.  It doesn't have the ability to restart the
> transaction.   That is left upto the programmer, who may not want to
> do a restart.
> > In Bart's original question though the discussion is about an
> automated system that must recover from a deadlock position.
> Normally the easiest way to handle this is to arrange the design
> so that a deadlock condition never occurs.
> > Cheers - Jim
> My opinions are my own.  They may vary with time but they remain MINE!
> ----------------------------------------------------------------------
> Name: Jim Gordon         Company: DHL Systems Inc, Burlingame, CA, USA
> ----------------------------------------------------------------------
> > } I'm not an expert on this, but I assume the DBMS will handle
deadlock
> } for you automatically  - either by avoiding it or by detecting it and
> } aborting and restarting the transaction.  The exception would be
> } if you're using Informix SE and the CISAM library.
> }
> } I hope somebody will confirm or deny this assumption.
> }

> }

> van der Worp):
> } > Hi, informix news readers!
> } >
> } > We are designing a automatic message exchange system. Some
> } > data is to be maitained and used; this is where informix comes in.
> } > Our customer wanted us to use Informix, no choice here....
> } >
> } > There are a limmited ammount of different possible transactions
> } > on this database. Transactions are very short, no human interaction.
> } >
> } > Due to the limmited ammount of different queries and update
> } > actions, it seems possible to use a locking scheme in which
> } > locks for required rows are aquired in a pre-defined order.
> } > Old fashioned perhaps, but save......

Another thing worth bearing in mind here is that if you've SET LOCK MODE
TO WAIT n (where n is a number of seconds), you get the "deadlock" error
if it times out. So in fact you may not have a *real* deadlock at all.

In this case your transaction will still be active and the decision you
need to make is whether to re-try the database statement, rather than the
entire transaction.


-------------------------------------

 
 
 

row level locking and dead-lock prevention

Post by David Kosen » Sat, 22 Oct 1994 03:00:19


Quote:Andy Kent writes:

|>
|> Another thing worth bearing in mind here is that if you've SET LOCK MODE
|> TO WAIT n (where n is a number of seconds), you get the "deadlock" error
|> if it times out. So in fact you may not have a *real* deadlock at all.

This is not true.  A "*real*" deadlock will return an isam error of -143.
A lock timeout will return -154.  There should never be any question as to
whether or not you have a "*real*" deadlock.

Dave Kosenko
Disclaimer: All opinions expressed in this message are well-reasoned and
insightful; needless to say, they are not those of Informix Software, its
partners or lackeys.  Anyone who says otherwise is itching for a fight.
****************************************************************************
"I look back with some satisfaction on what an idiot I was when I was 25,
 but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney

 
 
 

1. row level locking and dead-lock prevention

Hi, informix news readers!

I have a question with respect to locking, dead-lock prevention and
row (or key) level locking. I'm not a very experienced SQL programmer,
so I'm not like a fish in the water regarding this subject  (more like a
human.......)

We are designing a automatic message exchange system. Some
data is to be maitained and used; this is where informix comes in.
Our customer wanted us to use Informix, no choice here....

There are a limmited ammount of different possible transactions
on this database. Transactions are very short, no human interaction.
Different transactions access tables in different sequences as
the logic of these transactions demand. I'm afraid that if nothing
is done to prevent, dead-lock is possible to occur. Due to the
required real-time behaviour, dead lock repair (rollback and
retry) is out of the question.

Due to the limmited ammount of different queries and update
actions, it seems possible to use a locking scheme in which
locks for required rows are aquired in a pre-defined order.
Old fashioned perhaps, but save......

This could be done on table level (LOCK TABLE), but I don't
see how this can be achieved using row level locking,
since this seems only possible to take place implicitly
during execution of the query logic.

Because of the short transaction time, table level locking might
produce sufficient throuhgput, but I'm not (yet) sure
about this.

Are my fears about deadlock correct?
Is there a way, or work around, to lock on row level explicitly?

Thanks in advance,
Bart.

2. DAO Relations and ADOX Keys

3. table-level lock or row-level lock

4. Syntax Problem in SP

5. Page lock to simulate row level lock

6. Online: Bizarre disk interactions

7. Paradox 5.0: Row level locking or page locking?

8. default namespace (schema) confusion

9. table level locks, row level locks, column level locks?

10. row-level or page-level locking?

11. row level vs page level locking is it more than marketing

12. No DeadLocks ...Lock file locks

13. No Deadlocks...Lock file locks