| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> transaction enqueue lock
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,
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 - 09:39:50 CDT
 |
 |