Oracle lock table

1. Lock

To prevent users from concurrently accessing and modifying resources at the same time, ORACLE uses different types of locks to control concurrent access to data to prevent destructive interaction between users.
Oracle automatically locks resources for processing transactions.

Locks are obtained when SQL statements start their interaction with data and are valid throughout the transaction process.

Oracle9i uses two lock modes:
Exclusive mode: no other concurrent sessions are allowed to share locked resources in any way. This lock is required when you modify data.
. Sharing Mode: Allows concurrent read access to the same piece of data. Rose to exclusive mode when changing data

 

1) Row-Level Lock
Insert update delete implicit row lock (exclusive)
Select... for update: Display row lock (SHARE)

Select... for update is used to display the data rows to be updated to prevent other users from operating this row before the update.
For example, select * from emp where deptno = 30 for update
Update emp set ename = 'job' where empno = 7499;

Before the lock is released, other users cannot perform (modify, delete) operations on the locked data rows.

If other users want to lock the same resource, you can use the wait clause to control the lock wait time.
For example, in another user, select * from emp where deptno = 30 for update wait 2 (wait 2 seconds)
If the resource has not been released in 2 seconds, the system will prompt

 

2) Table-Level Lock

In share mode)
In share update mode)
Exclusive lock mode

General syntax for locking a table:

Lock table name in <share or share update or exclusive mode>;

1) Sharing Mode
Other users are not allowed to insert, update, or delete rows. Multiple users can set a shared lock on the same table at the same time. In this way, multiple users with locks can only perform queries.

Lock table EMP in share mode;

2) In share update mode)
Multiple users are allowed to lock different rows in the table at the same time, and other users are allowed to perform the DML (insert update Delete select) operation, except for the locked rows.

For example, lock table EMP in share update mode;
Select * from EMP where deptno = 30 for update // the row to be locked
Other users cannot delete or update employee information of Department 30.

Other users can view the locked row: Select * from EMP where deptno = 30

3) exclusive lock mode (strong restriction)
Other users are not allowed to insert, update, or delete rows and view data. However, only one user can place an exclusive lock in the table.

Lock table EMP in exclusive mode;

3) deadlock

For example, usera: Lock table Scott. EMP in share mode;
Userb: Lock table Scott. EMP in share mode;

USERA: update scott. emp set ename = 'Smith 'where empno = 7369;
USERB: update scott. emp set job = 'cler' where empno = 7521;
Deadlock