ORA-00054: the resource is busy, but specifies to get the resource in Nowait mode, or timeout is invalid

"ORA-00054: the resource is busy, but specifies a quick solution to get the resource in Nowait mode, or timeout failure"

Today, we are importing data from a temporary table. After the export is complete, we prepare to clear the data and run the truncate command. The following problems are encountered:

ORA-00054: the resource is busy, but specifies to get the resource in Nowait mode, or timeout is invalid

The solution is as follows:

========================================================== ========================

SQL> select session_id from V $ locked_object;

Session_id
----------
56

SQL & gt; select Sid, serial #, username, osuser from V $ session where Sid = 142;

Sid serial # username osuser
--------------------------------------------------------------------------------
56 2088 GHB FY

SQL> alter system kill session '2017 8 ';

System altered

After executing the preceding command, the system prompts that the session is disconnected. Reconnect to the database and perform the truncate operation!

The principles are as follows:
====================

Oracle Database lock type

Oracle Database locks can be divided into the following categories based on different protected objects: DML locks (Data locks), used to protect data integrity; DDL locks (Dictionary locks, dictionary locks) are used to protect the structure of database objects, such as schema definitions for tables and indexes. Internal locks and latches protect the internal structure of the database.

The purpose of the DML lock is to ensure data integrity in the case of concurrency ,. In Oracle databases, DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks, and TX locks are called transaction locks or row-level locks.

When Oracle executes the DML statement, the system automatically applies for a TM lock on the table to be operated. After the tmlock is obtained, the system automatically applies for the Tx lock and places the lock flag of the actually locked data row. In this way, when the consistency of the Tx lock is checked before the transaction locks, the lock mark does not need to be checked row by row. Instead, you only need to check the compatibility of the tmlock mode, which greatly improves the system efficiency. The tmlock includes multiple modes, such as SS, Sx, S, and X, which are represented by 0-6 in the database. Different SQL operations generate different types of tmlocks.

Only the X lock (exclusive lock) exists on the Data row ). In the Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time. The lock is kept until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record of the table, the first session locks the record, and other sessions are in the waiting state. After the first session is submitted, the Tx lock is released before other sessions can be locked.

When the Oracle database has a TX lock wait, if not timely processing will often cause the Oracle database to suspend, or cause the occurrence of a deadlock, produce ORA-60 errors. These phenomena will cause great harm to the actual application, such as long time not responding, a large number of transactions failed, etc.

Pessimistic blockout and optimistic blockout

I. pessimistic Blocking
The lock takes effect before the user changes:
Select... for update (Nowait)
Select * From tab1 for update
After the user issues this command, Oracle will establish a row-level blocking for the data in the returned set to prevent modification by other users.
If other users perform DML or DDL operations on the data returned in the result set above, an error message or blocking will be returned.
1: The update or delete operation on the returned result set will be blocked.
2: DDL operations on the table will report: Ora-00054: Resource busy and acquire with Nowait specified.

Cause Analysis
At this time, Oracle has added row-level locks to the returned result set. All other modification or deletion operations on the data must wait for the lock to be released, the external phenomenon is that other operations will be blocked. This operation is commit or rollback.
Similarly, the transaction for this query will apply a table-Level Lock to the table, and does not allow any DDL operations on the table, otherwise the ora-00054 error will be reported :: resource busy and acquire with Nowait specified.

2. Optimistic Blocking
We are optimistic that the data will not be changed during the period from select to update and submit. There is a potential danger that the selected result set is not locked and may be changed by other users. Therefore, we recommend that you use pessimistic blocking for Oracle, because it is safer.

 

Blocking

Definition:
When one session keeps the lock on the requested resources of another session, blocking occurs. The blocked session will be suspended until the locked session gives up the locked resource. Four Common DML statements may cause blocking.
Insert
Update
Delete
Select... For update

Insert

Insert is blocked only when you have a table with primary key constraints. When two sessions attempt to insert the same data to the table at the same time, one session will be blocked until another session is committed or rolled. When one session is submitted, the other session will receive an error with duplicate primary keys. During rollback, the blocked session continues.

Update and delete when the data row that executes the update and delete operations has been locked by another session, it will be blocked until another session is committed or rolled.

Select... For update

When a user sends a select... for update error to prepare to modify the returned result set, if the result set has been locked by another session, blocking occurs. The execution can be continued only after another session ends. Select... For update Nowait statements to avoid blocking, if the resource is locked by another session, the following error is returned: Ora-00054: Resource busy and acquire with Nowait specified.

 

Deadlock-deadlock

Definition: a deadlock occurs when two users want to hold each other's resources.
That is, when two users wait for each other to release resources, Oracle determines that a deadlock occurs. In this case, the execution of another user will be continued at the cost of one user, transactions of the sacrificed users will be rolled back.
Example:
1: User 1 updates table A without submitting.
2: User 2 updates table B without submitting.
In this case, there is no resource sharing problem.
3: If user 2 updates table A at this time, blocking will occur. You need to wait until the end of user 1.
4: If user 1 updates table B, a deadlock occurs. In this case, Oracle will select one of the users for rollback, so that another user can continue the operation.
Cause:
In fact, the deadlock problem in Oracle is rare. If it occurs, it is basically caused by incorrect program design. After adjustment, the deadlock will basically be avoided.

DML lock classification table

 

Table 1 Oracle tmlock types
Lock mode lock description Description SQL operations
0 none
1 null select
2 SS (Row-S) Row-level shared locks. Other objects can only query these data rows: Select for update, lock for update, lock row share

3 SX (Row-x) Row-level exclusive locks. Before submission, DML operations such as insert, update, delete, and lock row share are not allowed.

4 S (SHARE) share lock create index, lock share
5 ssx (S/row-x) share row-level exclusive lock share row exclusive
6 x (exclusive) exclusive lock alter table, drop able, drop index, truncate table, lock exclusive

 

1. Description of the V $ lock table and related views

Column datatype description
ADDR raw (4 | 8) Address of Lock State object
Kaddr raw (4 | 8) Address of lock
SID number identifier for session holding or acquiring the lock
Type varchar2 (2) Type of user or system lock
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
TM-DML enqueue
TX-transaction enqueue
Ul-user supplied
-- We mainly focus on the Tx and TM locks.
-- Ul locks are defined by users and are rarely defined.
-- All others are system locks and will be automatically released soon.
Id1 number lock identifier #1 (depends on type)
Id2 number lock identifier #2 (depends on type)
--- When lock type is TM, id1 is the object_id of DML-locked object
--- When lock type is Tx, id1 is USN + slot, while Id2 is seq.
-- When the lock type is other, do not pay attention
Lmode number lock mode in which the session holds the lock:
0-None
1-null (null)
2-row-s (SS)
3-row-X (SX)
4-share (s)
5-S/row-X (ssx)
6-exclusive (X)
-- If it is greater than 0, the current session occupies the lock in some mode. If it is equal to 0, the current session is waiting for the lock resource, indicating that the session is blocked.
-- A tx lock is usually accompanied by a tmlock. For example, if a SID = 9 session has a tmlock, it usually has one or several TX locks, but their id1 and Id2 are different, note:
Request number lock mode in which the process requests the lock:
0-None
1-null (null)
2-row-s (SS)
3-row-X (SX)
4-share (s)
5-S/row-X (ssx)
6-exclusive (X)
-- When the value is greater than 0, the current session is blocked, and other sessions occupy the lock mode.
Ctime number time since current mode was granted
Block number the lock is blocking another lock
0, 'not blocking ',
1, 'blocking ',
2, 'global ',

-- Whether the lock has blocked another lock

 

2. Other related views
Description of main fields in view Name Description
V $ session: queries session information and lock information. Sid, serial #: indicates the session information.
Program: indicates the application information of the session.
Row_wait_obj #: indicates the waiting object, which corresponds to the object_id in dba_objects.
Lockwait: the address of the lock waiting for the session, which corresponds to the kaddr of V $ lock.
V $ session_wait: query the waiting session information. Sid: indicates the session information holding the lock.
Seconds_in_wait: indicates the waiting duration.
Event: indicates the session waiting event. The lock equals to enqueue.

Dba_locks format the view of V $ lock. Session_id: corresponds to the SID in V $ lock.
Lock_type: corresponds to the type in V $ lock.
Lock_id1: corresponds to id1 in V $ lock.
Mode_held, mode_requested: and V $ lock
.
V $ locked_object only contains DML lock information, including rollback segments and session information. Xidusn, xidslot, and xidsqn: indicates the rollback segment information. And
V $ transaction is associated.
Object_id: The identifier of the locked object.
Session_id: indicates the session information holding the lock.
Locked_mode: a message indicating the lock mode in which the session is waiting
Is consistent with the lmode in V $ lock.

 

The following is the command line section
======================

1. query locks in the database

Select * from V $ lock;
Select * from V $ lock where block = 1;

2. query locked objects

Select * from V $ locked_object;

3. query Blocking

Check blocked sessions
Select * from V $ lock where lmode = 0 and type in ('Tm ', 'tx ');

Check for blocking other session locks
Select * from V $ lock where lmode> 0 and type in ('Tm ', 'tx ');

4. query the process in which the database is waiting for the lock

Select * from V $ session where lockwait is not null;

5. query the lock wait relationship between sessions

Select a. Sid holdsid, B. Sid waitsid, A. type, A. id1, A. Id2, A. ctime from V $ lock a, V $ lock B
Where a. id1 = B. id1 and A. Id2 = B. Id2 and A. Block = 1 and B. Block = 0;

6. query lock wait events
Select * from V $ session_wait where event = 'enqueue ';

Solution:
Select session_id from V $ locked_object; -- first obtain the session_id of the locked object
Select Sid, serial #, username, osuser from V $ session where Sid = session_id; -- use the session_id obtained above to obtain the SID and serial of V $ session #, then terminate the process.
Alter system kill session 'sid, serial ';
Example:
Alter system kill session '13, 8 ';