Answer Posted / anil kumar jaiswal

Lock : Lock is a mechanism to prevent destructive interaction between users using same source at same time.
mainly lock is two types :

row level lock and table level lock.

Row level lock : in this a row is locked exclusively so others can not modify the rows until the transaction holding the lock is commited or rolled back.

syntax : lock table table_name where sal>3000(any condition) for update of comm(a column name);

table level lock consist 3 type :

a) share mode lock.
b)share update mode.
c)share exclusive mode.

share mode : allow users to query but not able to perform dml operation on the table being locked in shared mode. multi user can place share lock on same resource at same time.
syntax : lock table table_name in share mode;

share update lock : its used to lock the rows that need to be updated and also allow other user to access other rows of the table.
it just prevent users to use the same row is updating.
in share update mode users can perform all task like insert,delete,update.

syntax : lock table table_name in share update mode;

Exclusive mode : you can give same definition written for share lock. the only difference is only 1 user can place exclusive lock on a table at a time.

syntax : lock table emp in share exclusive mode;

Note : After explaining concept of locks i like to share some info about deadlock.

what is deadlock : when 2 user have a lock each on separate object, and wants to acquire a lock on each other objects when this happen. the first user need to wait for the 2nd user to release the lock but the 2nd user will not release the lock untill he found first user object freed. in this scenario oracle detect deadlock automatically and solve the problem by aborting one of the transaction.

What is the use of %rowtype?

1024

---

What are the methods of filing?

918

---

what are the 'mysql' command line options? : Sql dba

1057

---

define sql insert statement ? : Sql dba

1009

---

define join and explain different type of joins? : Sql dba

931

---

How many types of triggers are there in pl sql?

1049

---

What is the difference between a primary key and a clustered index?

968

---

what are all types of user defined functions? : Sql dba

1029

---

Hi am new to PLSQL & facing problems in writing code like in SP, Functions, so any one having some SP coding with in depth explanation please share with me my Email ID suvarnaatsuvarna@rediffmail.com Or taking tanning on this please do contact me

2001

---

What is file based approach?

924

---

how tsql statements can be written and submitted to the database engine? : Transact sql

965

---

How is sql used in oracle?

993

---

What is the best free sql database?

1040

---

what is 'mysqld'? : Sql dba

956

---

What does count (*) mean in sql?

1555

---