Re: Oracle 7 cannot have concurrent updates & queries ???

Brian Graham (GrahamB_at_psac.com) wrote:
: >Lionel Lee (lionell_at_sp.ac.sg) wrote:
: >: The Oracle 7 application developers at our site have been writing apps
: >: which perform massive updates and we encountered database deadlocks
: >: frequently when concurrent updates (& queries) are attempted on the
: >: same table. So we even resort to queueing these Oracle apps via the
: >: flaky cron/at subsystem which reduces our multiprocessor SPARCcenter
: >: Unix mutiuser environment into an inefficient batch processing "single
: >: user" a-job-at-a-time environment.
 

: >: So is it true that Oracle RDBMS does not support concurrent updates or
: >: rather what is wrong here??? Please kindly advise...
: >

No, it's not true. Don't really have enough data to suggest what might be wrong though.

: It is my understanding the excessive fragmentation and limited free space
: in an Oracle block cause locking ALL data in that block. This problem can

This is not (strictly) true either. There is a transaction table in the block (the ITL, Interested Transaction List). Excessive fragmentation and/or limited free space could prevent this table from growing dynamically, in which case the number of rows in the block which can be concurrently locked is limited by the size that the transaction table could grow to. From the application's point of view, this would appear similar to the whole block being locked.

: be resolved by exporting the data and re-importing it (of course drop all
: the data to avoid duplicate data).

This would only help if the source of the problem was block-level fragmentation (as opposed to limited free space). In the latter case, you would need to increase PCTFREE or INITRANS to allow the ITL to grow beyond it's previous limit.

If you *know* that every block in the table will need to have every row concurrently locked at some stage during it's life, then I would suggest rebuilding the table with INITRANS set equal to expected # rows per block.

: For the life of me I couldn't find the reference in the DBA manual.
: Perhaps this info was from an Oracle course or at IOUG.
: Hope this helps.
: Brian
: --
: Brian Graham .
: Programmer / DBA ._______|_______. Tel: 613-560-5470
: Public Service Alliance \(*)/ Fax: 613-563-3492
: of Canada o/ \o Email: grahamb_at_psac.com

--
graeme
--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.