Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to deal with the problem of oracle locking table". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The quick solution to "ORA-00054: resources are busy, but specify NOWAIT to get resources, or timeout expires" encountered the following problems when importing data from a temporary table, preparing to empty the data after export, and executing the truncate command:

ORA-00054: the resource is busy, but the resource is specified to be obtained by NOWAIT, or the timeout expires.

The solution is as follows:

=

SQL > select session_id from v$locked_object

SESSION_ID

-

fifty-six

SQL > SELECT sid, serial#, username, osuser FROM v$session where sid = 56

SID SERIAL# USERNAME OSUSER

-

56 2088 ghb fy

SQL > ALTER SYSTEM KILL SESSION '56jue 2088'

System altered

After executing the above command, prompt the session to disconnect. Reconnect to the database, and then perform the truncate operation, successful!

The following is the principle part

=

Lock type of Oracle database

According to the different objects protected, Oracle database locks can be divided into the following categories: DML locks (data locks, data locks), used to protect the integrity of data; DDL locks (dictionary locks, dictionary locks), used to protect the structure of database objects, such as the structural definition of tables, indexes, etc.; internal locks and latches (internal locks and latches) to protect the internal structure of the database.

The purpose of DML lock is to ensure data integrity in the case of concurrency. In Oracle database, DML lock mainly includes TM lock and TX lock, in which TM lock is called table-level lock, TX lock is called transaction lock or row-level lock.

When Oracle executes the DML statement, the system automatically applies a lock of type TM on the table to be operated on. When the TM lock is obtained, the system automatically applies for the TX type lock and sets the lock flag bit of the actual locked data row. In this way, when checking the compatibility of the TX lock before the transaction is added, there is no need to check the lock flag line by line, but only to check the compatibility of the TM lock mode, which greatly improves the efficiency of the system. The TM lock includes many modes, such as SS, SX, S, X and so on, which is represented by 0Mel 6 in the database. Different SQL operations produce different types of TM locks.

There is only an X lock (exclusive lock) on the data row. In an Oracle database, when a transaction initiates a DML statement for the first time, it acquires an TX lock that is held until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record in the table, the first session locks the record and the other sessions wait. When the first session is committed, the TX lock is released so that other sessions can be locked.

When TX lock waiting occurs in Oracle database, if it is not handled in time, it will often cause Oracle database to hang, or lead to deadlock, resulting in ORA-60 error. These phenomena will do great harm to practical applications, such as unresponsive for a long time, a large number of transaction failures and so on.

Pessimistic blockade and optimistic blockade

I. 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 on the data in the returned set to prevent modifications by other users.

If another user dml or ddl the data returned from the result set at this time, an error message will be returned or blocking will occur.

1: blocking occurs when you update or delete the returned result set.

2: ddl operation on the table will report: Ora-00054:resource busy and acquire with nowait specified.

Cause analysis

At this time, Oracle has added an exclusive row-level lock to the returned result set, and all other operations to modify or delete these data must wait for the lock to be released. The external phenomenon is that other operations will block, this operation commit or rollback.

Similarly, the transaction of this query will add a table-level lock to the table, and no ddl operation on the table will be allowed, otherwise an ora-00054 error will be reported:: resource busy and acquire with nowait specified.

II. Optimistic blockade

It is optimistic that the data will not be changed during the period from select to update. There is a potential danger that because the selected result set is not locked, there is a possibility that it may be changed by other users. So Oracle still recommends a pessimistic blockade because it would be safer.

Blockage

Definition:

Blocking occurs when one session maintains a lock on the resource that another session is requesting. The blocked session is suspended until the session that holds the lock gives up the locked resource.

Four common dml statements can cause blocking

INSERT

UPDATE

DELETE

SELECT... FOR UPDATE

INSERT

The only situation where blocking occurs in Insert is when the user has a table with primary key constraints. When two sessions try to insert the same data into the table at the same time, one session will be blocked until the other session commits or rolls. When one session is submitted, the other session receives a duplicate primary key error. When you roll back, the blocked session will continue to execute.

UPDATE and DELETE block when the rows of data performing Update and delete operations are locked by another session until another session commits or rolls.

Select... For update

When a user issues a select..for update error to prepare to modify the returned result set, blocking occurs if the result set has been locked by another session. You need to wait for another session to finish before you can continue. You can send out a select. For update nowait statement to avoid blocking, and if the resource is already 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 considers that there is a deadlock, in this case, at the expense of one user, the other user continues to execute, and the transaction of the sacrificed user will be rolled back.

Example:

1: user 1 Update table An and does not submit it.

2: user 2 Update table B and does not submit.

At this time, there is no problem of resource sharing.

3: if user 2 update the A table at this time, it will block and need to wait until the end of user one's transaction.

4: if user 1 update table B at this time, a deadlock occurs. At this point, Oracle selects one of the users to roll so that the other user can continue with the operation.

Cause:

Deadlock problems in Oracle are actually rare. If they occur, they are basically caused by incorrect programming. After adjustment, deadlocks are basically avoided.

DML lock classification table

Table 1 TM lock types for Oracle

Lock mode lock description interprets SQL operation

0 none

1 NULL empty 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, DML operations Insert, Update, Delete, Lock row share are not allowed before submission

4 S (Share) shared locks Create index, Lock share

5 SSX (S/Row-X) shared row level exclusive lock Lock share row exclusive

6 X (Exclusive) exclusive locks Alter table, Drop able, Drop index, Truncate table, Lock exclusive

1. Notes on V$lock tables 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 two types of locks: TX and TM

-- UL locks are defined by users themselves, but they are rarely defined and basically need no attention.

-- other locks are system locks, which will be released automatically soon, so don't worry about it.

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 and id2 is seq.

-- when lock type is something else, don't worry about it

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)

A value greater than 0 indicates that the current session occupies the lock in some mode, and a value equal to 0 indicates that the current session is waiting for the lock resource, indicating that the session is blocked.

When TX locks occur, they are often accompanied by TM locks. For example, a sid=9 session has a TM lock and usually has one or more TX locks, but their id1 and id2 are different.

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 it is greater than 0, the current session is blocked and other sessions are in lock mode.

CTIME NUMBER Time since current mode was granted

BLOCK NUMBER A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

two。 Description of other related views

View name description main field description

V$session queries session information and lock information.

Sid,serial#: represents session information.

Program: represents the application information for the session.

Row_wait_obj#: represents the waiting object, corresponding to the object_id in dba_objects.

Lockwait: the address of the lock that the session is waiting for, corresponding to the kaddr of v$lock.

V$session_wait queries the waiting session information. Sid: indicates the session information that holds the lock.

Seconds_in_wait: indicates the duration of the wait

Event: indicates the event that the session is waiting for, and the lock is equal to enqueue

Dba_locks 's formatted view of v$lock.

Session_id: corresponds to Sid in v$lock.

Lock_type: corresponds to 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 lock information for DML, including rollback segment and session information.

Xidusn,xidslot,xidsqn: indicates the rollback segment information. Associated with v$transaction.

Object_id: indicates the identity of the locked object.

Session_id: indicates the session information that holds the lock.

Locked_mode: indicates the lock mode information that the session is waiting for, which 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

two。 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 to block other session locks

Select * from v$lock where lmode > 0 and type in ('TM','TX')

4. The process of querying that the database is waiting for a lock

Select * from v$session where lockwait is not null

5. Query the relationship of lock waiting between sessions

Select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a dint vault 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:

-- first get the session_id of the locked object

Select session_id from v$locked_object

Use the session_id obtained above to get the sid and serial#, of v$session and then terminate the process.

SELECT sid, serial#, username, osuser FROM v$session where sid = session_id

-- finally kill the conversation.

ALTER SYSTEM KILL SESSION 'sid,serial'

This is the end of the content of "how to deal with the oracle Table Lock problem". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.