Re: Row level locking ?

In article <251h3e$r7i_at_usenet.INS.CWRU.Edu> yangying_at_ces.cwru.edu (Ying Yang) writes:
>In article <24unfq$hu2_at_infochi.com> rfinkel_at_infochi.com (Richard Finkelstein) writes:
>
>>I re-read my comments and would like to make one clarification. Oracle
>>updates a data page with an exclusive lock every time it needs to lock a
>>record. So if three records are locked on a single page, Oracle places
>>three locks on that page - one for each record. The important thing to
>>understand is that the locks are physically placed on the page.
>
>Since physical I/O's are implemented on the page level, row-level locking
>in database systems might be only a LOGICAL concept. Database systems may
>still need to hold a page lock when a physical I/O is requested.

This is true. As I remember, what REALLY happens in Oracle when you go to lock a row is that, via some extremely low latency in memory operation such as a spin-lock, you do momentarilly lock the entire page that the row sits on. Then you change a couple bytes on that page, still in memory, to indicate that one of the rows is locked. Finally you release your page lock. All of this happens literally in microseconds. The whole bit about physically changing data pages to get row locks isn't really true, all you change are some bits on the page in memory (Is this physical ? Ah, a philosophy question) I don't believe anything is actually written to disk as a result of a row lock operation, unless the block is physically flushed out of the cache back to disk, which happens sometime later.

The parallel server works in much the same way, except the distributed lock manager keeps track of the pages or regions of pages that are locked across a cluster.

The important point is that you should think of these page locks as physical locks, outside of the knowledge or control of the application. The application sees row locks. One of the arguments for this is that networks and humans are much slower than database server CPU's, and that you always want the human (who may lock the row on his screen and walk away for coffee) to control the smallest amount of resources. Hence all the work that has been done with Optimistic Locking that has been less of an issue for Oracle users than it would be if Oracle used page locks at the database level.

>I doubt there are many real production Oracle systems using the row-lock
>option.
>

Actually row locking is NOT at option in Oracle. You get it whether you want it or not. The only alternative is to hand code in Table level locking statements, which very very few people do for non-specialized reasons. In reality EVERY real production Oracle system uses row level locking.

Hope this helps,

• Dan

Daniel Druker
Anderson Graduate School of Management at UCLA                    

| Dan Druker                                                               |
| agsm mail 	: ddruker                                                  |
| internet 	: ddruker_at_agsm.ucla.edu                                    |
| oracle*mail	: unix:ddruker_at_agsm.ucla.edu                               |
----------------------------------------------------------------------------

Disclaimer: None. I'm a student now and I don't care what you think. Received on Fri Aug 20 1993 - 18:03:28 CEST