Table locking in oracle

Table locking in oracle
Lock Concept
The lock is used in data sharing to ensure data consistency. When multiple sessions modify a table at the same time, the data needs to be locked accordingly.

Locks include read-only locks, exclusive locks, and shared exclusive locks. Each type also has row-level locks (locking a record at a time ), "Page-level locks" (one page is locked at a time, that is, the minimum allocable unit for storing records in the database) and "table-level locks" (locking the entire table ).

If it is a row-level exclusive lock, other rows in the table can be updated or deleted by other users ). If it is a table-level exclusive lock, all other users can only perform the select Operation on the table, but cannot modify or delete any records. After the program commits or rolls back the modifications, the locked resources are released, allowing other users to perform operations.
If two transactions lock a part of the data separately and wait for the other party to release the lock to complete the transaction operation, a deadlock will occur.

Implicit and explicit locks
In Oracle databases, an implicit exclusive lock is required for data modification to lock the modified row until the modification is submitted or revoked. If a session locks data, the second session can only wait until the first session submits the modification using the COMMIT command or rolls back the modification using the ROLLBACK command,. Therefore, we should develop a good habit: After performing the modification operation, we should submit or withdraw it as soon as possible to avoid affecting the data modification by other sessions.

Modify SCOTT employee records in the emp table to test implicit locks. Step 1: start the first SQL * Plus, log on to the database with the SCOTT account (the first session), modify the SCOTT record, and implicitly lock. UPDATE emp SET sal = 3500 where empno = 7788; Step 2: start the second SQL * Plus, log on to the database (the second session) with the SCOTT account, and modify the record. UPDATE emp SET sal = 4000 where empno = 7788; Step 3: Unlock the first session: COMMIT; Step 4: view the second session. The output result is displayed. Step 5: submit the second session to prevent lock for a long time.

Explicit table locking

Lock row
Apply an explicit lock to employee records of Department 10 in the emp table and test.

Apply an explicit lock to department 10: SELECT empno, ename, job, sal FROM emp WHERE deptno = 10 for update;

Step 1: Apply an explicit lock to department 10: SELECT empno, ename, job, sal FROM emp WHERE deptno = 10 for update; Step 2: start the second SQL * Plus (second session), log on to the database with the SCOTT account, and modify CLARK, an employee of Department 10. UPDATE emp SET sal = sal + 100 where empno = 7782; Step 3: Unlock the first session: COMMIT; Step 4: view the second session with output results:

Lock table
The LOCK statement is used to LOCK the entire table.
A table can be locked in SHARE or EXCLUSIVE mode. In shared mode, shared locks can be applied to other sessions, but exclusive locks cannot be applied. In exclusive mode, other sessions cannot be shared or exclusive.
Training 1: add an exclusive lock to the emp table.
Step 1: Apply an exclusive lock to the emp table:
Lock table emp in exclusive mode;

Step 2: unlock a table:
COMMIT;