Re: Concurrency and consistency - Please help me.

From: Sherlock <sherlock_at_mailmetoday.com>
Date: Wed, 23 Apr 2003 01:59:49 -0500
Message-ID: <3EA639E5.2070107@mailmetoday.com>

The mystery gets bigger and bigger

<<ROW SHARE and ROW EXCLUSIVE table locks offer the highest degree of concurrency. You might use these locks if: Your transaction needs to prevent another transaction from acquiring an intervening share, share row, or exclusive table lock for a table before the table can be updated in your transaction.

If another transaction acquires an intervening share, share row, or exclusive table lock, no other transactions can update the table until the locking transaction commits or rolls back. Your transaction needs to prevent a table from being altered or dropped before the table can be modified later in your transaction.>>

Jim Kennedy wrote:

> In real life it just doesn't get that complex.  I let the database handle
> the lock levels itself based upon what I am doing.  I don't ever issue those
> locks explicitly.  On the other hand, if I was dropping a table then Oracle
> is going to take out an exclusive lock on the table. (right?  I can't drop a
> table with transactions against it.)
> Jim
> 
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family.  Remove the negative part, keep the minus sign.  You can figure
> it out.
> "Sherlock" <sherlock_at_mailmetoday.com> wrote in message
> news:4d1a3c52.0304222001.619241d2_at_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 Wed Apr 23 2003 - 01:59:49 CDT