Oracle 11g-Lock

Oracle 11g-Lock

Target:

1. Understanding the concept of locking

2. Mastering the use of select for update

3. understand the meaning of different locks

The concept of Lock:

1. A lock is a mechanism that the database uses to control concurrent access to shared resources;

2. locks are used to protect data that is being modified;

3. Other users can update data only after committing a commit or rolling back a ROLLBACK transaction

Advantages of the Lock:

1. Consistency: Allow only one user to modify data at a time (Delete,update,insert)

2. parallelism: Allows multiple users to access the same data (select);

3. completeness: For all users to provide the correct data, if a user has been modified and saved, the changes will be reflected to all users;

Type of Lock:

1. Row-level Lock: Locks the row being modified, and other users can access rows other than the locked row;

2. table-level Lock: locks the entire table and restricts access to the table by other users.

Row-level Lock expansion 1

1. Row-level locks are an exclusive lock that prevents other transactions from modifying this row

2. When the following statement is used

INSERT , UPDATE , DELETE

SELECT ... for Update "statement allows the user to lock multiple records at once for updates";

Use COMMIT or ROLLBACK statement release lock;

Oracle row-level locks are automatically applied and, of course, table-level locks are available. Select queries are not locked.

row-level lock expansion 2: SELECT ... For UPDATE[of Colums][wait n][nowait]

<1> sql> SELECT * from EMP WHERE sal=1000 for UPDATE;

sql> UPDATE EMP SET sal = WHERE sal =1000;

Sql> COMMIT;

<2> sql> SELECT * from Scott.emp WHERE sal=1000

For UPDATE WAIT 5; [wait 5 seconds cannot be locked out]

<3> sql> SELECT * from Scott.emp WHERE sal=1000 for UPDATE NOWAIT;

[Do not wait, if the line is locked, jump out immediately]

View Lock: SELECT * from V$lock;

where TX represents a row-level lock, TM represents a table-level lock

Table level Lock Expansion 1:

Syntax: LOCK table table_name in [mode] mode

Example: Lock table scott.emp in row share mode;

Table-Level Lock expansion 2: From top to bottom, restrictions are getting stronger

row Sharing (ROW SHARE) – Prohibit exclusive lock table "is to allow other users to put Exclusive lock, but the user can make the operation of the increase and deletion check. "

Row Exclusive (ROW EXCLUSIVE) – prohibit use of exclusive and shared locks

Shared Locks (SHARE) " with the SELECT * from emp for update is similar to "locking tables, allowing only other users to query rows in a table

Prevent other users from inserting, updating, and deleting rows

Multiple users can apply this lock on the same table at the same time

shared Row Exclusive (SHARE ROW EXCLUSIVE) – more restrictions than shared locks, prohibit the use of shared locks and higher locks

Exclusive (EXCLUSIVE) – restricts the strongest table locks, allowing only other users to query the rows of the table. Prohibit modifying and locking tables

= = = " understanding sharing and exclusivity:

Sharing: If a shared lock is set, other users can also lock;

Exclusive: If an exclusive lock is set, no other user can shackle.  

This article is from the "8898156" blog, please be sure to keep this source http://8908156.blog.51cto.com/8898156/1427282