Re: Article about supposed "murky" future for Oracle

[Huge snip]

> I have been told that Oracle's concept of lock management is to write
> the lock information in the data block itself, instead of centralized
> lock manager. That means a SELECT FOR UPDATE will be two writes. One for
> the lock and one for the actual write of the data. Not OK for us
> in the given scenario.

I haven't been following this thread ever since it turned into a 'let's knock the other product'-fest (and you aren't the greatest culprit there, I think). But that last statement is not correct.

A select for update most certainly has to take locks on rows, and that means modifying blocks for sure. But they are blocks in memory, read there because you issued your select... statement. When you then issue the ensuing update statement, the blocks are again modified... but in memory where they already were (OK, they could have aged out in the meantime, but that would be unusual and a sign of bad configuration or bad application coding). So this sequence:

select * from emp for update;
update emp set sal=900 where ename='Bob';

...performs one read from disk, one locking operation and two in-memory modifications.

Eventually, the blocks will get aged to disk, and at that point you have one disk write.

The recording of lock information in the data block itself is trivial in terms of overhead. A few bytes at worst. Bytes which are already in memory at the time they need modifying. I doubt you would ever be able to measure the impact of storing lock information with the rows versus a centralised lock manager. On the other hand, centralised lock management has been responsible for that daft idea of lock escalation: if you regard locks as a scare commodity, you are prepared to turn 100,000 row locks into a single table lock because it's cheaper to look after 1 lock than 100,000... at which point, you've just locked 50,000 rows that shouldn't have been! That is (IIRC) exactly what Informix did, and was something I found utterly incomprehensible after luxuriating for so long in Oracle's "another lock, no problems" methodology.

Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
===========================================