Re: Rollback segment used in exclusive mode : Why ?

By exclusively locked I presume you mean you can see a 'TX' lock in mode 6 in v$lock. This is the normal state for a transaction.

It was always possible to lock up an Oracle system with N rollback segments simply by issuing (very carefully) N very small transactions and then not committing them. (This may no longer be true on recent versions - it's been a long time since I tested it).

Assume you have done 'update dual set dummy = 'w'' and the rollback segment used is R01, and there is no space left in the tablespace for R01 to grow -

As other transactions take place in R01, the head of the rollback segment wraps around the rollback segment until it finally gets back to your uncommitted rollback. At that point the head of the rollback is not allowed to 'jump over' your transaction, so whatever is going on has to wait until your transaction commits.

If you have very small, ungrowable rollback segments, this could be your problem.

Option 2 - your rollback segments have only 2 extents, If your first transaction grows from one extent into the second extent, then no new transaction is allowed to start in that extent. NB This slightly peculiar rule applies specifically to the transaction crossing the extent boundary; under normal circumstances it is perfectly legal for several transactions to start in the same rollback extent. It is because of this restriction (and there was a whitepaper on it about 10 years ago I think) that you are commonly advised about 20 extents to the optimal rollback size is a good model.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

>Emmanuel Guyot wrote in message <7kg9kf$hpr$1_at_wanadoo.fr>...
>>Each of my rollback segment is used in exclusive mode. I mean that only
one
>>transaction can use it. For example, if I force the use of one Rollback
>>Segment (set transaction use rollback segment ...) I update a table and I
>>don't quit.
>>If I do the same thing from another session, the second one is locked!
>>
>>If I verify with Lock Manager, it confirms that the rollback segment is
>used
>>in exclusive mode.
>>
>>The question is Why! I've asked the Oracle support but they don't know.
>>
>>I've dropped and created back the rollback segment (create rollback
segment
>>XXX tablespace YYYY) but the problem still occurs.
Received on Sat Jun 19 1999 - 12:08:13 CDT