Oracle Series-Lock table and Unlocking Solution (Basic edition)

"Oracle Lock Table Query and Unlock solution"

First, understand the reason (draw on the collation)

Classification of database Operation statements

DDL: Database schema definition language, keyword: create
DML: Data manipulation language, keywords: Insert, delete, update
DCL: Database control Language, keyword: Grant, remove
DQL: Database Query Language, keywords: select
Under what circumstances will Oracle tables be locked

DML locks can also be divided into, row, table, deadlock

Row lock: When a transaction performs a database insert, UPDATE, delete operation, the transaction automatically obtains an exclusive lock on the action row in the action table.

Table-level Lock: When a transaction obtains a row lock, this transaction also automatically obtains the row's table lock (shared lock) to prevent other transactions from making DDL statements that affect the update of the record row. A transaction can also acquire a shared or exclusive lock during the process, only if the transaction is displayed using the
The lock table statement shows the definition of an exclusive lock, the transaction does not get an exclusive lock on the table, or you can use the lock table to define a table-level shared lock (for the specific use of lock table, refer to the relevant documentation).

Deadlock: A deadlock occurs when two transactions require a set of conflicting locks and cannot continue the transaction.
For example, transaction 1 has a row of locks in table A, and waits for transaction 2 to record the release of the exclusive lock in table A, while transaction 2 has a row lock in table a record line # # #, and waits for transaction 1 to record the release of the exclusive lock in Table A, transaction 1 and transaction 2, etc.
So that it creates a deadlock. Deadlocks are generally caused by poor business design.
Deadlocks can only be used under SQL: Alter system kill session "sid,serial#", or commands that use the associated operating system kill process, such as the Kill-9 Sid under Unix, or use other tools to kill the deadlock process.

DDL locks can also be divided into: Exclusive DDL locks, shared DDL locks, analysis locks

Exclusive DDL Lock: A DDL statement that creates, modifies, and deletes a database object obtains an exclusive lock on the action object. When using the ALTER TABLE statement, the transaction obtains a row of its DDL locks in order to maintain the completion, consistency, and legitimacy of the data.

Shared DDL Locks: DDL statements that need to establish interdependencies between database objects typically need to be shared to obtain DDL locks.
If you create a package, the procedures and functions in the package refer to different database tables, and when the package is compiled, the transaction obtains the shared DDL lock of the referencing table.

Analytics Lock: Oracle uses shared pool storage to analyze and optimize SQL statements and PL/SQL programs to make applications running the same statement faster. An object cached in a shared pool obtains an analysis lock on the database object it references. The analysis lock is a unique
DDL lock type, which Oracle uses to track dependencies between shared pool objects and the database objects it references. When a transaction modifies or deletes a database object in which the shared pool holds an analysis lock, Oracle invalidates the objects in the shared pool, and the next time you reference this
Sql/plsql Statement, Oracle re-parses this statement.

Oracle Lock table Query and Unlock

This is explained by solving the following problems:

In the bulk of the DML operation, the program should be interrupted, no longer execute, query a table is locked, and therefore no longer down execution.

The first step: Query the locked table information through administrator rights user

If the table is suspected to be locked, or if the transaction is not shut down properly, in the Oracle database we can query for the relevant information through the following statement:

Select T2.username,
T2.sid,
t2.serial#,
T3.object_name,
T2. Osuser,
T2. Machine,
T2. Program,
T2. Logon_time,
T2.command,
T2. Lockwait,
T2. SADDR,
T2. PADDR,
T2. TADDR,
T2. Sql_address,
T1. Locked_mode
From V$locked_object T1, v$session T2, dba_objects T3
where t1.session_id = T2.sid
and t1.object_id = t3.object_id
Order BY T2.logon_time;
It is found that the above SQL statement uses Oracle's two views and a table, namely V$locked_object, V$session, and dba_objects:
All locked object information in all sessions is recorded in the V$locked_object view.
The V$session View records information about all sessions.
Dba_objects is a collection of Oracle user objects and system objects that can be associated with this table to obtain detailed information about the objects being locked.

Eg: now I execute the DML statement via the Scott User (Eg:select * from EMP for update, or update scott.emp set sal = ' 7788 ' where empno= '; ) and then continue without committing, and then through the system user
Executing the SQL statement above querying the locked table in Oracle, you will find the following records:
Write a picture description here

Description
Username:oracle User Name
SID: Process Number
serial#: Serial Number
object_name: Table Name
Osuser: Operating System User name
Machine Name:
Program: Operating Tools
Logon_time: Login Time
Lockwait: Indicates whether the table is currently waiting for another user to unlock the table
Locked_mode: Lock Table Mode (detailed below)

Note: If a select * from scott.emp for update is executed through the system user, the statement cannot be executed successfully.

Step two: Unlock the locked tables in the database by a user with Administrator privileges (sid,serial)

After finding the locked table with the information from the first step, execute the following statement to unlock the table:

Alter system kill session ' sid,seial# ';
1
Note: Sid and seial# are the process numbers and serial numbers that were queried in the first step.

Eg: Remove the lock from the table in the first step

Alter system kill session ' 10,15 ';
1
The DML statement is now executed again through system (Eg:select * from scott.emp for update, or update scott.emp set sal = ' 7788 ' where empno= ';) is available.

Mode of Lock

The Locked_mode field in V$locked_object represents the mode of lock, and there are several modes of lock in Oracle:
0:none
1:null Empty
2:row-s line Sharing (RS): Shared table lock, Sub share
3:row-x Line Exclusive (RX): For row modification, sub exclusive
4:share shared Lock (S): block other DML operations, Share
5:s/row-x shared Row Exclusive (SRX): Block other transactional operations, Share/sub exclusive
6:exclusive Exclusive (X): Standalone access use, exclusive

The larger the number, the higher the lock level, and the more actions are affected.

Level 1 locks are: Select, which sometimes appears in V$locked_object.
Level 2 locks are: Select for Update,lock for Update,lock Row Share
Select for update when a dialog uses a for update substring to open a cursor, all data rows in the returned set are at row level (ROW-X) exclusive locking, and other objects can only query those data rows, not update, delete, or select
For update operation.
Level 3 Lock: Insert, Update, Delete, lock Row Exclusive
Inserting the same record without a commit will not react, because the next 3 lock will wait for the last 3 lock and we must release the previous one to continue working.
Level 4 Lock: Create Index, lock Share
Locked_mode for 2,3,4 does not affect DML (Insert,delete,update,select) operations, but DDL (Alter,drop, etc.) operations prompt for ora-00054 errors.
00054, 00000, "resource busy and acquire with NOWAIT specified"
*cause:resource interested is busy.
*action:retry if necessary.
Level 5 Lock: Lock Share Row Exclusive
Specifically, there are primary foreign key constraints when update/delete ...; A lock of 4, 5 may be generated.
Level 6 locks are: Alter table, drop table, drop Index, Truncate table, lock Exclusive

There is also a more practical sql:

--Check the SQL statement being executed by a session, so that you can quickly navigate to which operations or code cause the transaction to continue without end.
SELECT/*+ ORDERED */
Sql_text
From V$sqltext A
WHERE (A.hash_value, a.address) in
(SELECT DECODE (sql_hash_value, 0, Prev_hash_value, Sql_hash_value),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
From V$session b
WHERE b.sid = ' 67 ')/* Here 67 is sid*/
ORDER by Piece ASC;

Oracle Series-Lock table and Unlocking Solution (Basic edition)