Explain how to query and unlock Oracle lock tables and explain how to unlock oracle locks

Explain how to query and unlock Oracle lock tables and explain how to unlock oracle locks
Database Operation statement category

DDL: database mode definition language; Keyword: create

DML: data manipulation language; keywords: Insert, delete, and update

DCL: Database Control Language, Keyword: grant, remove

DQL: Database Query Language, Keyword: select

When Will oracle tables be locked?

DML locks can be divided into row locks, table locks, and deadlocks.

Row lock: when a transaction performs database insert, update, or delete operations, the transaction automatically obtains the row exclusive lock of the operation table.

Table-Level Lock: after a transaction acquires a row lock, the transaction automatically acquires the table lock (shared lock) of the row to prevent other transactions from affecting the update of record rows by using DDL statements. A transaction can also obtain a shared or exclusive LOCK during the process. Only when the transaction displays the definition of an exclusive LOCK displayed by the lock table statement will the transaction obtain the exclusive LOCK on the TABLE, you can also use the lock table display to define a TABLE-level shared LOCK (refer to the relevant documentation for specific usage of the lock table ).

Deadlock: a deadlock occurs when two transactions need a set of conflicting locks and cannot continue the transaction.

For example, if transaction 1 records row #3 in Table A and waits for transaction 2 to record in Table A #4 to release the exclusive lock, transaction 2 has an exclusive lock in Table A record row #4, and waits for transaction 1 to record in Table A #3 to release the exclusive lock. Transaction 1 and transaction 2 wait for each other, therefore, a deadlock occurs. Deadlock is generally caused by poor transaction design.

Only SQL statements can be used for deadlocks: alter system kill session "sid, serial #"; or commands for kill processes in the relevant operating system, such as kill-9 sid in UNIX, or use other tools to kill the deadlock process.

DDL locks can be divided into: exclusive DDL locks, shared DDL locks, and analysis locks.

Exclusive DDL lock: create, modify, and delete DDL statements of a database object to obtain the exclusive lock of the operation object. For example, when an alter table statement is used, the transaction obtains a row of DDL locks to maintain data integrity, consistency, and legitimacy.

Share DDL lock: DDL statements that need to establish dependency between database objects usually need to share to obtain the DDL lock.

If you create a package, the process in the package is different from that in the function reference database table. When this package is compiled, the transaction obtains the shared DDL lock of the referenced table.

Analysis lock: ORACLE uses a shared pool to store analysis and optimized SQL statements and PL/SQL programs, making applications that run the same statement faster. An object cached in the Shared Pool obtains the analysis lock of the database object it references. Analytics lock is a unique DDL lock type. ORACLE uses it to track Shared Pool objects and dependencies between referenced database objects. When a transaction modifies or deletes a database object holding an analysis lock in the Shared Pool, ORACLE invalidate the object in the shared pool. The next time you reference this SQL/PLSQL statement, ORACLE re-analyzes and compiles this statement.

Oracle lock Table query and unlock

You can solve the following problems:

During batch DML operations, the program was interrupted, and a table was locked for further execution and query. Therefore, it was no longer executed.

Step 1: query the locked table information with the administrator privilege

If you suspect that the table is locked or the transaction is not properly closed, you can use the following statement in the Oracle database to query and obtain relevant information:

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;

We found that the preceding SQL statement uses two views and one table in Oracle, namely v $ locked_object, v $ session, and dba_objects:

The v $ locked_object view records all locked objects in all sessions.

The v $ session view records information about all sessions.

Dba_objects is a collection of oracle user objects and System Objects. By associating this table, you can obtain detailed information about the locked objects.

Eg: Now I use the scott user to execute the DML Statement (eg: select * from emp for update; or update scott. emp set sal = '000000' where empno = '000000';) after that, the system user does not submit the SQL statement. Then, the system user executes the SQL statement used to query the locked table in oracle, the following record is found:

Note:

Username: oracle user name

Sid: process ID

Serial #: serial number

Object_name: Table Name

Osuser: Operating System User Name

Machine: machine name

Program: Operation Tool

Logon_time: Logon Time

Lockwait: indicates whether the table is waiting for other users to unlock.

Locked_mode: Lock table mode (detailed description below)

Note: At this time, if the system user executes select * from scott. emp for update; the statement cannot be successfully executed.

Step 2: remove the locked table (SID, SERIAL) from the database by a user with administrator permissions)

Find the table to be locked and execute the following statement to unlock the table:

alter system kill session 'sid,seial#';

Note: sid and seial # Are the process numbers and serial numbers found in step 1.

Eg: Unlock the table lock in step 1

alter system kill session '10,15';

Now run the DML Statement (eg: select * from scott. emp for update; or update scott. emp set sal = '000000' where empno = '000000.

Lock mode

The LOCKED_MODE field in v $ locked_object indicates the lock mode. There are several lock modes in oracle:

0: none

1: null

2: Row-S Row sharing (RS): Shared table lock, sub share

3: Row-X exclusive (RX): used for Row modification, sub exclusive

4: Share share lock (S): block other DML operations, Share

5: S/Row-X shared Row exclusive (SRX): block other transaction operations, share/sub exclusive

6: exclusive (X): used for independent access, exclusive

The larger the number, the higher the lock level, the more operations affected.

Level 1 locks include Select, which sometimes appears in v $ locked_object.

Level 2 locks include Select for update, Lock For Update, and Lock Row Share.

Select for update when the dialog uses the for update substring to open a cursor, all data rows in the returned set will be locked exclusively at the Row level (Row-X, other objects can only query these data rows and cannot perform update, delete, or select for update operations.

Level 3 locks include Insert, Update, Delete, and Lock Row Exclusive.

Inserting the same record before the commit operation does not respond, because the last 3 lock will always wait for the last 3 lock, and we must release the previous one to continue working.

Level 4 locks include: Create Index, Lock Share

Locked_mode is 2, 3, 4 does not affect DML (insert, delete, update, select) operations, but DDL (alter, drop, etc.) operations will prompt a ora-00054 error.

00054,000 00, "resource busy and acquire with NOWAIT specified"

// * Cause: Resource interested is busy.

// * Action: Retry if necessary.

Level 5 locks: Lock Share Row Exclusive

Specifically, update/delete... ; May generate 4 or 5 locks.

Level 6 locks include Alter table, Drop table, Drop Index, Truncate table, and Lock Exclusive.

There is also a more practical SQL:

-- Check the SQL statement being executed by a session to quickly locate the operations or code that causes the transaction to remain unfinished. SELECT/* + ORDERED */SQL _text FROM v $ sqltext a WHERE (. hash_value,. 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;