Concurrency and consistency

From: Sherlock <sherlock_at_mailmetoday.com>
Date: 22 Apr 2003 21:01:37 -0700
Message-ID: <4d1a3c52.0304222001.619241d2@posting.google.com>

Hello everyone,

I am a student from Purdue University and was going through "Data Concurrency and Consistency" chapter from the Oracle manual. I am not able to comprehend the concepts after 2 days of a hard struggle. The reason is that Oracle explains a lock something like this:

Share Table Locks(S)
A share table lock is acquired automatically using the following statementy
LOCK TABLE IN SHARE MODE
Permitted Operations: Other transactions allowed to query,....... Prohibited Operation LOCK TABLE IN EXCLUSIVE MODE not permitted....

Without a proper example on why and when i need to specify this, this really goes above me. Why for example should i use Share Table Lock and not an Shared Row Exclusive table lock?. If some kind soul can give me some hints on why these locks are used, i would be very much thankful. I am fairly familiar with Oracle (SQL,PLSQL, Normalization) and know what isolation levels (Read Committed, Serializable etc. )are. I am aware that Oracle uses row level locking and no explicit locking is needed most of the time. What i need is a little example on when each type of lock would prove useful (Some kind of a context would be great).

The locks i need an example are

1)Row Share Table Lock
2)Row Exclusive Table Lock
3)Share Table Lock
4)Share Row Exclusive Table lock
5)Exclusive Table Locks

Sorry for the length of the mail. I hope someone can throw some light on this.

Best Wishes
Karthik Received on Tue Apr 22 2003 - 23:01:37 CDT

Concurrency and consistency - Please help me.