Oracle Lock Table Query and Unlock method

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, and the transaction will obtain exclusive locks on the table only if the transaction shows the definition of an exclusive lock displayed using the Lock table statement, or you can use the lock table to define a table-level shared lock (lock Please refer to the relevant documentation for specific use of table).

• Deadlock: A deadlock occurs when two transactions require a set of conflicting locks and cannot continue the transaction.
  such as transaction 1 in table a row record # # There is a row of it lock, 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 wait for each other, resulting in 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. An analysis lock is a unique type of DDL lock that 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 the Sql/plsql statement is referenced, Oracle re-parses the 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, respectively v$locked_object、v$session、dba_objects :
v$locked_objectAll locked object information in the session is recorded in the view.
v$sessionThe view records information about all sessions.
dba_objectsA 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 that I have not committed the DML statement (eg:) through the Scott user select * from emp for update;或者update scott.emp set sal = ‘2000‘ where empno=‘7788‘; , and then execute the SQL statement above querying the locked table in Oracle through the system user, we will find the following record:

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: This select * from scott.emp for update; is not a successful execution if the statement is executed through the system user.

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 Kill ' sid,seial# ';

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 Kill ' 10,15 ';

The DML statement (eg:) can now be executed again through system select * from scott.emp for update;或update scott.emp set sal = ‘2000‘ where empno=‘7788‘; .

Loop all unlock

Declare cursorMycur is    SelectT2.sid, t2.serial# fromv$locked_object T1, v$session T2, dba_objects T3wheret1.session_id=T2.sid andT1.object_id =T3.object_id Group  byT2.sid, t2.serial#; begin       forCurinchMycur LoopExecuteImmediate ('alter system kill session" "||Cur.sid|| ','||cur.serial#||" " '); EndLoop; End;

Mode of Lock

v$locked_objectThe Locked_mode field in the system represents the mode of lock, and there are several types of locks 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:

--查某session 正在执行的sql语句，从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.

SELECT /*+ ORDERED*/Sql_text fromV$sqltext aWHERE(A.hash_value, a.address)inch       (SELECTDECODE (Sql_hash_value,0, Prev_hash_value, Sql_hash_value), DECODE (Sql_hash_value,0, Prev_sql_addr, sql_address) fromv$session bWHEREB.sid= ' the')/*here 67 is Sid*/ ORDER  byPieceASC;

Oracle Lock Table Query and Unlock method