transaction enqueue lock 
Author Message
 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



Sat, 29 Jan 2005 21:43:53 GMT
 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,

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Quote:
-----Original Message-----


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



Sat, 29 Jan 2005 22:39:50 GMT
 transaction enqueue lock
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.
Quote:

> 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.

> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------

> -----Original Message-----


> 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



Sun, 30 Jan 2005 02:32:26 GMT
 transaction enqueue lock
Hi Jon,

did you export/drop/import the tables (and obviously the indexes) after
setting the new value? If you didn't then only new blocks allocated to
the table will have the extra slots !

Cheers,
Norman.

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Quote:
-----Original Message-----


Posted At: Tuesday, August 13, 2002 7:32 PM
Posted To: server
Conversation: transaction enqueue lock
Subject: Re: transaction enqueue lock

Thanks for responding Norm.  In fact our MINTRANS was 2 and we kicked
it up to 4.  (did not think of the indexes!)<SNIP>



Sun, 30 Jan 2005 17:27:27 GMT
 transaction enqueue lock
Norm,
 We did dat.  export table, create table, import table, rebuild
indexes.
Gave the table and indexes intrans of= 2 maxtrans = 20, pctfree (from
20 ) to 40.
Ran 7  programs   concurrently .  Four go and three lock on the first
insert .  As the blockers finish the locked get on.  We have no
exclusive table locks.  No two programs are modifying the same row.
Still get the enqueue transaction lock.  Somehow being limited to four
tasks.  We were thinking of going through this again but to set the
inittrans to 6 to see if we get an increase.
jonl
Quote:

> Hi Jon,

> did you export/drop/import the tables (and obviously the indexes) after
> setting the new value? If you didn't then only new blocks allocated to
> the table will have the extra slots !

> Cheers,
> Norman.



Mon, 31 Jan 2005 03:01:43 GMT
 transaction enqueue lock

Quote:

>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

Hello Jon,

try to find the (requested) lock mode of the blocked session(s), from
v$lock. If it 4, there is a contention for space (either from the
update or the insert statements). If it is 6, there is a 'real' row
lock.

BTW: I assume that in this post, MINTRANS should be corrected to
INITRANS.

Kind Regards,

Herman de Boer
sr. consultant
IT Consultancy Group bv



Mon, 31 Jan 2005 14:57:17 GMT
 transaction enqueue lock
Jon,

first of all, well spotted for noticing that when I wrote 'MINTRANS' I
didn't have a clue what I was talking about, I meant to write INITRANS
instead :o)

Next up, I asked on this NG many moons ago about how to set INITRANS on
an index when the table has 'n'. I was told that the index should have
'n+1'.

I assume that having PCTFREE set to 40 should leave enough space in the
block for a slot - if one cannot be obtained in the block header, so
silly stuff now :

what are the settings for :

DML_LOCKS, ENQUE_RESOURCES and TRANSACTIONS in initSID.ora ?

It could be that these are set pretty funny and are holding you back.

Maybe, but unlikely :o(

Cheers,
Norman.

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------



Mon, 31 Jan 2005 16:47:46 GMT
 transaction enqueue lock

Quote:

> 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

In addition to other people's advice, you may also look at INITRANS on
the indexes for the table. Consider setting INITRANS to the same for
their base table plus 1, after you increase the setting for the
tables. For 8i, the quickest way to increase INITRANS is ALTER TABLE
tablename MOVE TABLESPACE tablespacename. You don't need to specify
another tablespace; i.e. the end result is not really moving the table
to another tablespace. Remember to rebuild indexes after this, since
they'll be UNUSABLE unless there's no row in the table.

Yong Huang



Tue, 01 Feb 2005 05:28:56 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. library cache lock | transaction enqueue lock

2. SMON Having lock on temporary segment (enqueue lock)

3. DBMS_AQ.Enqueue gives incorrect enqueue time

4. Waited Too Long For Row Cache Enqueue Lock

5. waited too long for row cache enqueue lock

6. Problem with enqueue locks

7. Transaction Locks: Rerunning transaction

8. Advanced Queuing: remote enqueuing/dequeuing

9. enqueue timeouts ?

10. enqueue waits

11. how to get a msgid from enqueue in Visual Basic (using oo4o)

12. advanced queueing - how to enqueue to remote queue


 
Powered by phpBB® Forum Software