Locks are the mechanism used to ensure data integrity while allowing maximum concurrent access to data.
Applying locks has to be decided on the following cases:
1. Types of Locks to be applied.
2. Level of Locks to be applied.
1. Types of locks:
Shared lock: placed on the resource whenever a 'Read' operation (select ) is performed. Multiple shared locks can be simultaneously set on a resource.
Exclusive locks:
placed on the resource whenever a 'Write' operation (insert , update or delete) is performed.
Only one exclusive lock can be simultaneously set on a resource.
2. Level of Locks:
1. Row Level: if the where clause evaluates to only one row in the table.
2. Page Level: if the where clause evaluates on the set of data.
3. Table level: if there is no where clause(that is the query access the entire table).
Explicit Locking:
the technique of the lock taken on a table or its resources by a user is called "Explicit Locking".
1. users can lock the tables which they own or the tables that have been granted to them.
2. tables and rows can be explicitly locked by using either select for update statement or lock table statement.
3. the "select ... for update "
used to signal the oracle engine hat data currently being used needs to be updated.
basic syntax:
lock table1,table2 in {row share|row exclusive|share update|share|share row exclusive|exclusive} [nowait]
Row Share: permits concurrent access to the locked table but prohibits user from locking. the entire table for exclusive access. row share is synonymous with share update, which is included with the compatibility with earlier versions of oracle database.
Row Exclusive: is similar as row share, but it also prohibits locking in shared mode, ROW EXCLUSIVE locks are automatically obtained when updating , inserting, or deleting share update.
Share: permits the concurrent queries ut prohibits updated to the locked tables.
Share Row Exclusive: Share Row exclusive used to look at the whole table and to allow others to look at the rows in the table but to prohibit others form locking the table i the share.
Exclusive: Exclusive permits queries on the locked table but prohibits any other activity on it.
Nowait : if you want the database to return the control to you immediately if the specified table, partition, or table sub partition is alreday locked by the other user.
if you want to omit this clause, then the database waits until the table is available, ocks it, and returns control to you.
locks are removed under following conditions:
1. when a transaction is committed successfully.
2. when a roll back is performed.
3 a roll back to a save point releases locks set after the specified save point.
Example :
Command:
lock table user in exclusive mode nowait;
Applying locks has to be decided on the following cases:
1. Types of Locks to be applied.
2. Level of Locks to be applied.
1. Types of locks:
Shared lock: placed on the resource whenever a 'Read' operation (select ) is performed. Multiple shared locks can be simultaneously set on a resource.
Exclusive locks:
placed on the resource whenever a 'Write' operation (insert , update or delete) is performed.
Only one exclusive lock can be simultaneously set on a resource.
2. Level of Locks:
1. Row Level: if the where clause evaluates to only one row in the table.
2. Page Level: if the where clause evaluates on the set of data.
3. Table level: if there is no where clause(that is the query access the entire table).
Explicit Locking:
the technique of the lock taken on a table or its resources by a user is called "Explicit Locking".
1. users can lock the tables which they own or the tables that have been granted to them.
2. tables and rows can be explicitly locked by using either select for update statement or lock table statement.
3. the "select ... for update "
used to signal the oracle engine hat data currently being used needs to be updated.
basic syntax:
lock table1,table2 in {row share|row exclusive|share update|share|share row exclusive|exclusive} [nowait]
Row Share: permits concurrent access to the locked table but prohibits user from locking. the entire table for exclusive access. row share is synonymous with share update, which is included with the compatibility with earlier versions of oracle database.
Row Exclusive: is similar as row share, but it also prohibits locking in shared mode, ROW EXCLUSIVE locks are automatically obtained when updating , inserting, or deleting share update.
Share: permits the concurrent queries ut prohibits updated to the locked tables.
Share Row Exclusive: Share Row exclusive used to look at the whole table and to allow others to look at the rows in the table but to prohibit others form locking the table i the share.
Exclusive: Exclusive permits queries on the locked table but prohibits any other activity on it.
Nowait : if you want the database to return the control to you immediately if the specified table, partition, or table sub partition is alreday locked by the other user.
if you want to omit this clause, then the database waits until the table is available, ocks it, and returns control to you.
locks are removed under following conditions:
1. when a transaction is committed successfully.
2. when a roll back is performed.
3 a roll back to a save point releases locks set after the specified save point.
Example :
Command:
lock table user in exclusive mode nowait;
No comments:
Post a Comment