Thanks for responding Norm. In fact our MINTRANS was 2 and we kicked
it up to 4. (did not think of the indexes!) But we did some other
figuring. In that the average size of the table in question is 38
that means there could be a lot of rows on a block (8k) and that many
could be updates. The application process will update thousands of
these and because the charges are usually inserted in the order which
the may be updated, its concievable that a high percentage of the rows
could be updated within the same small group of transactions. We
thought of expanding the pctfree from 20 to 30 or 40. This has yet to
be done but we will give it a try.
Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA7027FC61F_at_lnewton.leeds.lfs.co.uk>...
> Jon,
>
> You most probably have your tables defined with the default setting for
> MINTRANS (=1) which means that there is room for one single transaction
> slot in each block header (indexes have 2 by default BTW), so when a
> second transaction comes along and tries to use the slot, it cannot.
> However, the MAXTRANS setting is 255 so it goes off into the free space
> for the block and attempts to create a new transaction slot in the free
> space - if there is room. If there is no room - you deadlock and wait.
>
> You are correct in saying that there is no row contention - because
> there isn't - you are actually hitting contention on the transaction
> slots in the block header. Have a gander over at http://www.ixora.com.au
> and search for ITL or transaction slot for some very interseting and
> in-depth details.
>
> Now you can alter table (and alter index) to increaes the numbers in
> MINTRANS if you like, but bear in mind that you only get the new number
> of slots in any new blocks - the old blocks still have only one slot. I
> would try alter table (and index - remember to give one extra slot to
> the index) then export the table(s) in question, drop them and re-import
> from the dump file. This will create them anew with extra slots and
> bring the data back in.
>
> It will also get rid of those spare slots which have been spread all
> over your free space and which are never deleted !
>
>
> HTH
>
> Regards,
> Norman,
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: jon.m.landenburger_at_verizon.com (Jon Landenburer)
> [mailto:jon.m.landenburger_at_verizon.com]
> Posted At: Tuesday, August 13, 2002 2:44 PM
> Posted To: server
> Conversation: transaction enqueue lock
> Subject: transaction enqueue lock
>
>
> We have a process which moves charges from one account to another. To
> do this it updates the charge on the losing account (giving it an end
> date) and then inserts the charge to the gaining account. Many cases
> though the movement of an account may have thousands of charges. In
> order to increase throughput we have attempted to run many of these
> processes at the same time. There should be no row contention because
> each process is working with different accounts and charges. When we
> get above 4 processes running we encounter transaction enqueue locks
> (TX). The holder of the lock is just going on updating and inserting
> where as the blocked process is stopped on the first insert --
> waiting. There is no sharing here the blocked processes will not move
> on until the blockers have completed. Effectively we are being held
> to 4 of these concurrent processes.
>
> looking in v$rollstat there is almost no movement (inserts have no
> before image and the updates are just a date field long-- so no
> rollback contention.
>
> v$session_wait is where I see the enqueue
>
> Not sure where to go from here.
> We are confident there is no row lock, no tables are being held
> exclusively
>
> Any ideas are welcome
> Jon Landenburger
Received on Tue Aug 13 2002 - 13:32:26 CDT
