Optimistic Or Pessimistic locking .

What changes and where to do the changes to be made to have my database support Optimistic Locking .

And also what is the default locking mechanism or Oracle DataBase ?(The context of locking means whether it it Optimistic Or Pessimistic)??

Please help

What changes and where to do the changes to be made to have my database support Optimistic Locking .

You'll need to incloude a versioning field in your tables.

And also what is the default locking mechanism or Oracle DataBase ?(The context of locking means whether it it Optimistic Or Pessimistic)?? [

Neither.

You'll need to include a versioning field in your tables.

Thanks paul ,

Do you mean i should add a Timestamp column in database ?

You could do, though timestamps have the problem that they can clash. Better using a different data type.

Paul , as you indicated i will add a different data type , but how the answer is related to my question .

How can i achieve this ?

Is my question so foolish ??

Ravi Pavan wrote:
What changes and where to do the changes to be made to have my database support Optimistic Locking .

We can only suggest oracle to lock a record, we can't enforce it.

What steps are needed?

In one transaction,
You have to reserve the record by using select .... from <yourtable> where <condition on a field with a unique index - preferably on the primary key> FOR UPDATE ;
The FOR UPDATE clause will reserve the record for you. However, Oracle may decide to apply a page or table lock.
But when you use the primary key as where clause, it will tipically apply a row lock.

You have now pessimistically locked, and the mlock will disappear when you end your transaction by committing or rollbacking.

More can be found by googling for Oracle locking strategy.

Is my question so foolish ??

Come on, you have had several replies on the same day as you posted the question. That is better than the response time of some Platinum service level agreements.

Jan Thank you

We can only suggest oracle to lock a record, we can't enforce it.

You have now pessimistically locked, and the mlock will disappear when you end your transaction by committing or rollbacking.

From these two statements , it indicates that Oracle follows a default of pessimistic locking mechanism ?

Am i correct ?

No. Re-read Jan's post.

From these two statements , it indicates that Oracle follows a default of pessimistic locking mechanism ?
Am i correct ?

No. Oracle uses optimistic locking.
Only when explicitely asking for a pessimistic lock (see the ...FOR UPDATE... clause in my example), it will switch to pessimistic.

How did you derive your conclusion from these two statements? In their context, they suggest the opposite.

Yes paul thanks for making a point .

But please tell me how page or table lock and a row lock are related to opitimistic or a pessimistic lock ?

Oracle uses optimistic locking.
Only when explicitely asking for a pessimistic lock (see the ...FOR UPDATE... clause in my example), it will switch to pessimistic.

Thanks Jan and Paul . I got my answer. Have a great time .