Re: INITRANS and MAXTRANS are confusing me?

On Tue, 11 Mar 2003 11:59:34 +0000, Wes Gamble wrote:

> Thanks for any help in advance.
>
> I just learned about INITRANS and MAXTRANS and what their purpose is.
> Why does information about the individual transactions have to be
> stored in data blocks to achieve locking?

How would we know WHO was locking which row in that case?

Basically, your process ID (unique to your session) is stored in the transaction slot. The row piece at the start of each row then stores the transaction slot number. Now I have a link from a row, back to who precisely has it locked.

>Why can't you just keep
> track of the number of transactions which are accessing data in the
> block in order to manage locking? Do individual transactions have to
> be included in the block in order to satisfy Oracle's read consistency
> model?

Not especially, though it certainly provides the mechanism that says "I am the one who locked that row prior to an update which I haven't committed yet, so I may see the uncommitted contents of that row" whereas you r server process comes along and says "Damn! I don't own that lock, because the Process ID is not right, therefore I may not view its contents. Time to start generating a read-consistent image of the data".

>
> If it helps to understand my confusion, I am used to the Informix
> world where lock data was centrally managed, not with the data.

Which is fair enough, but that is why Informix (like many other databases) do lock escalation. They see locks as a scarce resource because there is a central pool of locks which have to be divided amongst all possible users. So instead of letting you take 10000 row locks, I will escalate that to a single table lock, and to hell with the consequences (like the minor one that you've just locked 5000 rows that you weren't intending to touch, but I was... so now I have to wait).

Oracle's locks are stored down in the row, therefore they are never a 'scarce resource'; therefore Oracle never feels the need to limit the number of them that you can take; and hence there is never lock escalation in Oracle.

It's an entirely different approach (and a better one IMHO).

Regards
HJR
>
> Pointers to relevant references are welcomed.
>
> Thanks,
> Wes Gamble
Received on Tue Mar 11 2003 - 14:20:53 CST