Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Tx index contention

user5008104Jul 1 2020 — edited Jul 2 2020

Hi,

I have a session that performs an update that blocks a set of sessions that make inserts on the same table. The set of sessions with the insert statement have the event tx index contention, how is it possible?

Thanks for your help

Comments

jaramill Jul 1 2020

What is your full database version per the link on --> and answer #5

5) Database Version and IDE Version
Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.

If you're not sure what it is you can do the following:

select * from v$version;

in an SQL*Plus session and paste the results.

Always post this information when posting a new question.

John Thorton Jul 1 2020

user5008104 wrote:

Hi,

I have a session that performs an update that blocks a set of sessions that make inserts on the same table. The set of sessions with the insert statement have the event tx index contention, how is it possible?

Thanks for your help

does table have Primary Key which contains a SEQUENCE? Does INSERT trigger exist on this table?

user5008104 Jul 2 2020

Hi,

the oracle version is "

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production"

The table has a lot of indexes (a total of 43)

The primary key IDX_ZEUPH2A0 has no sequence but there is an ID column in the others indexes using a sequence generated with a trigger during insert.

indexes.png

Jonathan Lewis Jul 2 2020

The typical cause of "enq TX: Index contention" is index block splits. If your transaction is updating columns which appear in indexes then it will be deleting and re-inserting index entries, and either action could require a leaf block split to take place.  Under certain circumstances the split can take a "long" time and other sessions that want to insert into the splitting leaf block will report this enqueue wait.

You might be able to identify which index(es) from the ASH data, or from v$segstat / v$segment_statistics.  However if you give a better description of what the updating process is doing, and what the other processes are doing, this might give us enough information to explain where the problem is probably appearing.  (e.g. is your update changing a flag from Y to N in a large number of rows in a single update).

Regards

Jonathan Lewis

user5008104 Jul 2 2020

I must continue my analysis to obtain what the update is doing.

But the index block split is the consequence of the update or the insert ?blockedSession.pngblockingSession.png

Sven W. Jul 2 2020

See this somewhat older thread where Jonathan already gave an excellent analysis about various possible causes and strategies to avoid that problem:what causes enq: TX - index contention?

user5008104 Jul 2 2020

yes, I read a lot on the subject but here the origin of event "enq : TX - index contentions" seems to be the update carried out via the other session

Regards

Jonathan Lewis Jul 2 2020

If the blocker is the session doing the update then the update is the thing doing the block split and the inserting sessions are waiting for the update to finish the split. In theory the split should only take a very short time, so the waits for any single split should also be very short.

Regards

Jonathan Lewis

user5008104 Jul 2 2020

I do not understand in what situation, an update can cause a split of an index block, when the update leads to an increase in the size of the record and there is no more space in the block ?

can a fragmented table be the origin of this behavior ?

Jonathan Lewis Jul 2 2020

user5008104 wrote:

I do not understand in what situation, an update can cause a split of an index block,

Read my first response:

If your transaction is updating columns which appear in indexes then it will be deleting and re-inserting index entries,

An "update" to an index entry means the current entry has to be deleted from one leaf block and a new index entry created (usually) in another leaf block.

Regards

Jonathan Lewis

user5008104 Jul 2 2020

thanks,

how can i identify the index (or indexes) in question from the ASH data, or from v$segstat / v$segment_statistics.  i have to filter on statistic_name "enq:Tx index contention" ?

user5008104 Jul 2 2020

the problem here is that the insert is trying to access the same index block as the update, is that right ?

Regards

user5008104 Jul 2 2020

Sorry Jonathan,

you answered me in a previous response

Regards

user5008104 Jul 2 2020

Thanks all for your help

Regards

Mark D Powell Jul 2 2020 — edited on Jul 2 2020

user5008104, being that your post showed one session being responsible for pretty much all the blocking you should be very interested in what SQL statement the session was running, how many rows it updates, and how long the transaction runs for.  I am trying to remember if an index rebuild results in this wait or if it results in a different one, but hopefully no one was rebuilding indexes during production hours.

- -

HTH -- Mark D Powell --

1 - 15

Post Details

Added on Jul 1 2020
15 comments
750 views