The problem is as follows:
Sql> Conn Scott/[email Protected]_database
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as Scott
。。。。。。
sql> drop table Student2;
drop table Student2
ORA-00054: The resource is busy, but the resource is specified as NOWAIT, 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 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
Connected 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, the pessimistic blockade
Locks work before the user modifies them:
Select: For update (NOWAIT)
Select * from TAB1 for update
After the user issues this command, 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.
Ii. 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
Defined:
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
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:
Oracle's deadlock problem 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 TM Lock types for Oracle
Lock mode lock description Interpreting SQL operations
0 None
1 NULL NULL Select
2 SS (row-s) row-level shared locks, other objects can only query these data rows for the Select for update, the lock for update, the 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 locks lock share row exclusive
6 X (Exclusive) Row It locks 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 Identifier for session holding or acquiring the lock
Type VARCHAR2 (2) Type of user or system lock
The locks on the user types is obtained by the user applications. Any process which is blocking others are likely to being holding one of these locks. The user type locks is:
Tm-dml Enqueue
Tx-transaction Enqueue
Ul-user supplied
--we focus primarily on TX and TM two types of locks
--ul lock user Defined, generally rarely defined, basically do not pay attention to
--the other is 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 the Dml-locked object
---When lock type is TX, ID1 is Usn+slot, and Id2 is seq.
--When lock type is other, do not 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 indicates that the current session occupies the lock in a certain mode, or 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
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 and other sessions occupy a locked mode
CTIME number time since current mode is granted
BLOCK number The lock is blocking another lock
0, ' not Blocking ',
1, ' Blocking ',
2, ' Global ',
--Does the lock block another lock?
2. Other related View description
View name Description Main field Description
V$session information about the query session and the lock. 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 waiting for the duration
Event: Indicates the session waits for events, 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: And V$lock
The lmode,request corresponds to the corresponding.
V$locked_object contains only the lock information for DML, including rollback segments and session information. XIDUSN,XIDSLOT,XIDSQN: Indicates rollback segment information. And
V$transaction associated.
OBJECT_ID: Represents the identity of the locked object.
SESSION_ID: Represents the session information that holds the lock.
Locked_mode: The letter that represents the lock mode of the session waiting
V$lock and Lmode in the same.
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 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. 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 the lock wait event
SELECT * from v$session_wait where event= ' enqueue ';
Solution:
Select session_id from V$locked_object; --First get the session_id of the locked object
SELECT SID, 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 sess
ION ' sid,serial ';
Example
ALTER SYSTEM KILL SESSION ' 8 ';
"Turn" Oracle Error: ORA-00054: Resource is busy, request to specify NOWAIT
