This content has been marked as final. Show 11 replies
I shall try to answer.
Exclusive as the word means, "just for you".For example answer me when you update a row in a table, is some one else able to modify the same row?If the answer is No than we proceed.So no one is able to modifythe row other than you means that row is "exclusively " locked for you.Only you own it!
Shared-well its like as the words says,"shared by all".So you have a table having 10 rows.5 are locked by you by some DML.They can't be locked by some one else,but what about rest of 5 they can be right! Now both of you users have locked one common aka shared resourse and that is the table.So in order to preserve ,maximum concurrency oracle puts your table in the shared lock so that mutiple people can shareably lock but till the time the DML is going on , no one is able to drop,alter or in other words take an exclusive lock over it.
Hope that helps
"exclusively " locked for you.Only you own it!Ok fine...I mean here if i can login through 2 sessions using same user name...then I think exclusive mode is session dependent ??? (not user dependent)
So in order toMultiple people can sharebly lock means......
preserve ,maximum concurrency oracle puts your table
in the shared lock so that mutiple people can
shareably lock but till the time the DML is going on
I did not understand properly this setence Aman....
thanks for your patience........
Illustrate a SHARE lock on a primary key table when going to perform inserts into a foreign key table: BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films
WHERE name = 'Star Wars: Episode I - The Phantom Menace';
Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES
(_id_, 'GREAT! I was waiting for it for so long!');
Yes as two Scott "sessions" who can access the same EMP table, you will see a exclusive lock happening for a particular row eg like if you update reord of Scott user and try to lock the same row from the other session,it will hang means you will be in queue to access the same row.Yes locks are acquired by sessions as one user can have multiple sessions.
Pick up the same example, how many people are simultaneously accessing one table,2 sessions of Scott right? Means EMP table is shareably locked by 2 sessions.
In Oracle locking is automatic. Oracle itself will put the table into the particular lock mode depending upon the operation involved. Share mode , as I explained is to make sure that locking is not going to stop or limit the concurrency.In oracle , the minimum locking level is the row level.Oracel neither goes for page level ( table lock) nor it escalates the locks from row level to page level as in Oracle, there is no such thing called "lock Manager" whic is there in other databases.
So in the answer for your question,both the lock types will be automatically held when the transaction is started.
thanks aman....A way of letting other sessions know that a session is using the object, so there may be some (self-imposed) restrictions on how others can.should use it.
in what scenarios...share locked mode is used..
- if you are using a view, you would be annoyed should that view be dropped while you are using it. So the simple act of using the view should raise a flag (eg: shared lock) indicating the view is in use and should not be altered.
- if you are reading from a table, you might be unhappy if the table structure changed because someone dropped a column. A "Please don't mess with the structure, but go ahead and do row updates (or other reads)" lock will maintain your level of happiness.