ORA-00054: The resource is busy, but the resource is specified as NOWAIT, or the timeout expires

When I delete the data of a table in a database, I use TRUNCATE table name to delete the time, prompt this error, I from the internet Baidu, there is this method to solve

 from V$locked_object

session_id
1 163

SESSION_ID is 163.

SELECT  from where = 163

SID serial# USERNAME Osuser
1 163 5002 CREDITFW Administrator

ALTER KILL ' 163,5002 '

After execution, you can execute the statement TRUNCATE TABLE table name to delete data from the tables.

====================================================== the following principles of copying from the Internet:

The type of lock for an Oracle database depends on the object being protected, the Oracle database lock can be divided into the following categories: DML lock (data locks, lock), which is used to protect the integrity of the data; DDL locks (Dictionary locks, Dictionary locks), The structure used to protect database objects, such as the structure definition of tables, indexes, and internal locks and latches (internal locks and latches), protecting 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 blockade locks play a role before users modify: 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 the 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, allOther modifications or deletions to this 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 the ora-00054 error will be reported: Resource busy and Acquire with nowait specified. 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 can cause blocking insertupdatedeleteselect ... The only case where the for Updateinsertinsert 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 a statement of 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, 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, the dual-non-existent resourcesShared issues. 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 1 Oracle's TM lock type lock mode lock description Explanation SQL operation 0 none1 null null Select2 SS (row-s) row-level shared lock, other objects can only query these data rows Select for update, lock for UPDA Te, lock row share3 SX (row-x) row level exclusive lock, do not allow DML operations before committing Insert, Update, Delete, lock row Share4 S (Share) shared lock Create Index, lock Share 5 SSX (s/row-x) shared row-level exclusive lock lock share row Exclusive6 X (Exclusive) exclusive lock Alter table, drop able, DROP index, Truncate table, lock ex  Clusive 1. Description of the V$lock table and related views column Datatype descriptionaddr RAW (4 | 8) Address of lock state objectkaddr RAW (4 | 8) address of LOCKSID number Identifier for session holding or acquiring the LockTYPE VARCHAR2 (2) Type of user or system Lockthe Loc KS on the user types is 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 enqueuetx-transaction enqueueul-user supplied--We mainly focus on the TX and TM two types of lock--ul lock users themselves defined, generally rarely defined, basic no attention-the other is the system lock, will be automatically released immediately, 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 TM, ID1 is the object_id of the Dml-locked object---when the lock type is TX, ID1 is Usn+s Lot, and Id2 for seq. --When lock type is other, do not pay attention to Lmode number Lock mode in which the session holds the lock:0-none1-null (null) 2-row-s (SS) 3-r Ow-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-none1-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 , which indicates that the current session is blocked, other sessions occupy the mode of the lock CTime number time since present mode was Grantedblock number the lock is blocking another lock0, ' No T Blocking ', 1, ' Blocking ', 2, ' Global ',--whether the lock is blocking another lock 2. Other related views Description The view name describes the primary field description v$session the information of the query session and the lock information. sid,serial#: Represents the session information. Program: Represents the application information for the session. Row_wait_obj#: Indicates a waiting pairAnd dba_objects in the object_id. Lockwait: The address of the lock that the session waits for, corresponding to the kaddr of V$lock. V$session_wait queries the session information that is waiting. 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 the formatted view of the Enqueuedba_locks to 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. The following is the command line section ================1. Query the lock in the database select * FROM V$lock;select * from V$lock where block=1;2. Query Locked Object 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&G T;0 and type in (' TM ', ' TX '); 4. Query the process where the database is waiting for a lock select * from v$session where lockwait are not null;5. Relationship between query session lock waits Select A.sid h  Oldsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from V$lock a,v$lock bwhere 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 the Session_idselect SID of the locked object, serial#, username, osuser from v$session 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 session ' Sid,serial '; Example:alter system kill session ' 13, 8 ';

ORA-00054: The resource is busy, but the resource is specified as NOWAIT, or the time-out expires