Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: High "transaction rollbacks" value in v$sysstat

RE: High "transaction rollbacks" value in v$sysstat

From: <>
Date: Fri, 9 Sep 2005 11:44:35 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C450236157F@NT15.oneneck.corp>

Upon further examination, I don't think the description provided in the Metalink note below could explain what I'm seeing, because if that were the case, then I would expect a large number of 'enqueue waits' accompanying the 'transaction rollbacks', but I'm not seeing that. Here are the stats from the worst one hour period yesterday:

Statistic                                      Total     per Second    per Trans

--------------------------------- ------------------ -------------- ------------
enqueue waits 295 0.1 0.0 transaction rollbacks 389,303 108.1 2.1 rollback changes - undo records a 394,309 109.5 2.1 user commits 189,364 52.6 1.0 user rollbacks 0 0.0 0.0

Can anyone think of an explanation for this? How can I have 389,303 transaction rollbacks with 0 user rollbacks???

I'll open a TAR and let y'all know if/when I figure it out, but if you have any ideas, please let me know.


-----Original Message-----

From: Allen, Brandon
Sent: Thursday, September 08, 2005 4:58 PM To: 'Mladen Gogala'
Cc: Lou Fangxin;
Subject: RE: High "transaction rollbacks" value in v$sysstat

I found this explanation on Metalink, which might explain it:

From: Andrew Allen 26-Apr-05 15:29
Subject: Re : User rollbacks higher then user commits

Could be blocked updates causing the rollbacks. I do not know the source of the stat in the statspack report, but I will bet that this is it.

You see, when you do an update -- especially when more than one row is affected, your transaction could rollback and restart (possibly several times) before it completes. Oracle does not take locks on every row before it does the update. Instead it takes the row lock as it does the update on that row, so if you are updating more than one row in a query, you may successfully change the first few rows, then encounter a row that is locked. In this case your update will rollback and then attempt to do the update again once the lock is cleared. Then next pass may update all the rows or it may encounter another lock further down the line and rollback/restart again. All this happens behind the scenes so you never know about it.


Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

Received on Fri Sep 09 2005 - 13:45:03 CDT

Original text of this message