Shulou(Shulou.com)06/01 Report--

Oracle 11g-Lock

Target:

1. Understand the concept of locking

2. Master the use of select for update

3. Understand the meaning of different locks

The concept of lock:

1. Lock is a mechanism used by database to control concurrent access to shared resources.

two。 Locks are used to protect data that is being modified

3. Other users can update data only after the commit is committed or the rollback transaction is rolled back

Advantages of locks:

1. Consistency: only one user is allowed to modify data at a time (delete,update,insert)

two。 Parallelism: allows multiple users to access the same data (select)

3. Integrity: provide correct data for all users. If a user makes changes and saves them, 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

two。 Table-level lock: locks the entire table and restricts other users' access to the table.

Row-level lock extension 1

1. A row-level lock is an exclusive lock that prevents other transactions from modifying this row

two。 When using the following statement

INSERT 、 UPDATE 、 DELETE

SELECT... FOR UPDATE [statement allows users to lock multiple records at a time for updates]

Release the lock using a COMMIT or ROLLBACK statement

Oracle automatically applies row-level locks, as well as table-level locks. Select queries are not locked.

Row-level lock extension 2: SELECT … FOR UPDATE [of colums] [wait n] [nowait]

SQL > SELECT * FROM emp WHERE sal=1000 FOR UPDATE

SQL > UPDATE emp SET sal = 3000 WHERE sal = 1000

SQL > COMMIT

SQL > SELECT * FROM scott.emp WHERE sal=1000

FOR UPDATE WAIT 5; [Pop out after 5 seconds without locking]

SQL > SELECT * FROM scott.emp WHERE sal=1000 FOR UPDATE NOWAIT

[do not wait, if the bank is locked, jump out immediately]

Check the lock: select * from V$lock

Where TX represents row-level lock and TM represents table-level lock

Table-level lock extension 1:

Syntax: lock table table_name in [mode] mode

Example: lock table scott.emp in row share mode

Table-level lock extension 2: from top to bottom, the restrictions are getting stronger and stronger

Row sharing (ROW SHARE)-exclusive locking of tables is prohibited [that is, other users are allowed to place exclusive locks, but users can add, delete, modify, check, and so on. ]

Row exclusive (ROW EXCLUSIVE)-exclusive and shared locks are prohibited

Shared lock (SHARE) [similar to select * from emp for update] locks a table, allowing only other users to query rows in the table

Prohibit other users from inserting, updating, and deleting rows

Multiple users can apply this lock on the same table simultaneously

Shared row exclusivity (SHARE ROW EXCLUSIVE)-more restrictions than shared locks, prohibiting the use of shared locks and higher locks

EXCLUSIVE-the most restrictive table lock that only allows other users to query the rows of the table. Modification and locking of tables are prohibited

= "understand sharing and exclusion:

Sharing: if a shared lock is set, other users can also add a lock

Exclusive: if an exclusive lock is set, other users cannot be shackled.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.