Oracle Error: ORA-00054: The resource is busy and requires the specified NOWAIT
The problem is as follows:
Sql> Conn Scott/[email protected]_database Connected to Oracle database 11g Enterprise Edition Release 11.1.0.6.0 Conne CTED as Scott
。。。。。。
sql> drop table Student2;
drop table Student2
ORA-00054: The resource is busy, but specifies to obtain the resource in NOWAIT mode, or the timeout expires =========================================================
Here's how to fix it:
=========================================================
Sql> select session_id from V$locked_object;
SESSION_ID----------142
sql> SELECT SID, Serial#, username, osuser from v$session where sid = 142;
SID serial# USERNAME osuser--------------------------------------------------------------------------------142 38 SCOTT Lilwen
sql> ALTER SYSTEM KILL SESSION ' 142,38 ';
System Altered
Sql> Conn Scott/[email protected]_database Connected to Oracle database 11g Enterprise Edition Release 11.1.0.6.0 Conne CTED as Scott
sql> drop table Student2;
Table dropped
Types of locks for Oracle databases
Depending on the object being protected, the Oracle database lock can be divided into the following categories: DML lock (data locks, lock) to protect the integrity of the data, DDL locks (Dictionary locks, Dictionary locks) to protect the structure of database objects, such as tables, Structure definitions for indexes, internal locks and latches (internal locks and latches), protect the internal structure of the database.
The purpose of a DML lock is to ensure data integrity in the case of concurrency. In Oracle databases, DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.
When Oracle executes DML statements, the system automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the data row that is actually locked. In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks.
There is only an X lock (exclusive lock) on the data line. In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.
When a TX lock wait occurs on an Oracle database, failure to handle it often causes the Oracle database to hang or cause a deadlock to occur, resulting in ORA-60 errors. These phenomena can cause great harm to the actual application, such as long time not responding, large number of transaction failures, etc.
Pessimistic blockade and optimistic blockade
One, pessimistic lock locks play a role before the user changes: Select.. For update (NOWAIT) Select * When this command is issued by the TAB1 for update user, Oracle will establish a row-level blockade of the data in the returned set to prevent other users from modifying it. If a DML or DDL operation is performed by another user at this time on the data returned by the result set above, an error message is returned or a blockage occurs. 1: The update or delete operation on the returned result set is blocked. 2: DDL operation on this table will be reported:
Ora-00054: Resource busy and acquire with nowait specified.
Cause analysis at this point Oracle has added row-level locks to the returned result set, and all other modifications or deletions to the data must wait for the lock to be released, and the external phenomenon is that other operations will be blocked, this operation commits or rollback. The same transaction for this query will add a table-level lock to the table, do not allow any DDL operations on the table, or it will report
ora-00054Error:: Resource busy and acquire with nowait specified.
Second, optimistic blockade optimistic that the data in the select out to update the enterprising and submit the time data will not be changed. There is a potential danger that the chosen result set is not locked and there is a possibility that another user might be able to change it. So Oracle still suggests a pessimistic blockade, because it's safer.
Blocking
Definition: Blocking occurs when one session keeps a lock on the resource that another session is requesting. The blocked session is suspended until the session holding the lock discards the locked resource. 4 common DML statements will result in blocking INSERT UPDATE DELETE SELECT ... For UPDATE
INSERT
The only case where insert is blocked is if the user has a table with a PRIMARY key constraint. When 2 sessions attempt to insert the same data into the table at the same time, one of the sessions is blocked until another session commits or rolls. When one session commits, another session receives the primary key duplicate error. The blocked session will continue to execute when the rollback is rolled back.
Update and delete When the data row that performs the update and delete operations has been locked by another session, blocking occurs until another session commits or rolls.
Select ... for update
When a user issues a select: When the for update error is ready to be modified for the returned result set, blocking occurs if the result set has been locked by another session. You need to wait until another session finishes before you can continue. You can avoid blocking by issuing the statement for the Select ... for update nowait, and return the following error if the resource is already locked by another session:
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 is generated, in which case another user continues to execute at the expense of one user, and the transaction of the sacrificed user is rolled back. Example: 1: User 1 update to a table, no commit. 2: User 2 Update to B table, no commit. At this point, there is no resource sharing problem with double-anti. 3: If user 2 at this time to update a table, then the block will occur, you need to wait until the end of the user one thing. 4: If user 1 then update the B table, a deadlock is generated. At this point, Oracle chooses one of the users to roll over, allowing another user to continue the operation. Cause: The deadlock problem in Oracle is actually very rare, and if it happens, it's basically an incorrect design, and after the adjustment, it basically avoids the deadlock.
DML Lock Classification Table
Table 1 Oracle's TM lock type lock mode lock description Explanation SQL operation 0 None 1 null null Select 2 SS (row-s) row-level shared lock, other objects can only query these data rows Select for update, lock for Update, Lock Row Share
3 SX (row-x) row level exclusive lock, do not allow DML operation before commit Insert, Update, Delete, lock Row Share
4 S (Share) shared lock Create Index, lock Share 5 SSX (s/row-x) shared row level exclusive lock lock Share row exclusive 6 X (exclusive) row It lock Alter table, Drop Able, Drop index, Truncate table, Lock exclusive
1. Notes on 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 Ident Ifier for session holding or acquiring the lock type VARCHAR2 (2) Type of user or system lock the locks on the user types a Re obtained by user applications. Any process which is blocking others are likely to being holding one of these locks. The user type locks are:tm-dml enqueue tx-transaction Enqueue Ul-user supplied--we focus mainly on TX and TM two types of lock--ul lock users themselves defined, generally very Rarely defined, basic no attention-the other is the system lock, will be released automatically, do not pay attention to ID1 number lock identifier #1 (depends on type) ID2 number lock identifier #2 (depends On type)---when the lock type is a TM, ID1 is the object_id of dml-locked object---when the lock type is TX, ID1 is Usn+slot, and Id2 is seq. --When lock type is other, don't pay attention to 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)--greater than 0 means that the current session occupies the lock in a mode equal to 0, indicating that the current session is waiting for the lock resource, which means that the session is blocked. -often in the event of a TX lock, accompanied by a TM lock, such as a sid=9 session has a TM lock, generally will have one or several TX locks, but their ID1 and Id2 are different, please note the 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)--greater than 0 o'clock, indicates that the current session is blocked, other sessions occupy a locked mode CTIME number time since Curren T mode was granted BLOCK number the lock are blocking another lock 0, ' not blocking ', 1, ' Blocking ', 2, ' Global ',
--Does the lock block another lock?
2. Other related views Description The view name describes the primary field description v$session the information for the query session and the lock information. sid,serial#: Represents the session information. Program: Represents the application information for the session. Row_wait_obj#: Represents the awaited object, and corresponds to the object_id in Dba_objects. Lockwait: The address of the lock that the session waits for, corresponding to the kaddr of the V$lock. V$session_wait Query waiting session information. Sid: Represents the session information that holds the lock. Seconds_in_wait: Represents the time information to wait for an event: represents the events that the session waits for, and the lock equals enqueue
Dba_locks a formatted view of the V$lock. SESSION_ID: Corresponds to 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: Corresponds to Lmode,request in V$lock. V$locked_object contains only the lock information for DML, including rollback segments and session information. XIDUSN,XIDSLOT,XIDSQN: Indicates rollback segment information. associated with V$transaction. OBJECT_ID: Represents the identity of the locked object. SESSION_ID: Represents the session information that holds the lock. Locked_mode: Information that represents the lock mode that the session waits for, consistent with Lmode in V$lock.
1. Querying for locks in the database
SELECT * from V$lock; SELECT * from V$lock where block=1;
2. Querying for Locked objects
SELECT * from V$locked_object;
3. Query blocking
Check for blocked session select * from V$lock where lmode=0 and type in (' TM ', ' TX ');
Check blocking other session lock SELECT * from V$lock where lmode>0 and type in (' TM ', ' TX ');
4. Querying the process that the database is waiting for a lock
SELECT * from v$session where lockwait are NOT null;
5. Query the relationship between session lock waits
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 Event select * from v$session_wait where event= ' enqueue ';
Solution: Select session_id from V$locked_object;--First Get Lock object session_id Select Sid, Serial#, username, osuser from V$sessi on where sid = session_id; --Get the SID and serial# of V$session through the session_id obtained above, then terminate the process. ALTER SYSTEM KILL sess
ION ' sid,serial '; example:alter SYSTEM KILL SESSION ' 8 ';
Oracle Error: ORA-00054: The resource is busy and requires the specified NOWAIT
