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

This article mainly explains "introduction to the mechanism of ORACLE lock". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "introduction to the mechanism of ORACLE lock".

The database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions will access the same data at the same time in the database. If there is no control over concurrent operations, incorrect data may be read and stored, and the consistency of the database will be destroyed.

Locking is a very important technology to realize database concurrency control. When a transaction makes a request to the system and locks it before operating on a data object. After locking, the transaction has some control over the data object, and other transactions cannot update the data object until the transaction releases the lock.

There are two basic types of locks in the database: Exclusive Locks (X lock) and shared lock (Share Locks, S lock). When an exclusive lock is added to a data object, it cannot be read or modified by other transactions. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions.

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 locks is to ensure data integrity in concurrent situations. 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.

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.

Blocking:

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 action 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.

Fourth, DML lock classification table

5. Solution to the problem of oracle lock

1. Check which session locks:

SQL statement:

Select 'alter system kill session''| | sid | |','| | serial# | |';'

From v$session

Where sid in (select sid from v$lock where block = 1)

two。 Check the session lock.

Sql statement:

Select s.sid, q.sql_text

From v$sqltext q, v$session s

Where q.address = s.sql_address

And s.sid = & sid

Order by piece

3. The process of kill lock.

SQL statement: alter system kill session 'sid,serial#' immediate

4. Find out who locked who.

Select s1.username | | email ='@'@'/ email | | s1.machine |'(SID=' | | s1.sid | |') is blocking'| | s2.username | | email ='@'@'/ email | | s2.machine | (SID=' | | s2.sid | |')'AS blocking_status

From v$lock l1, v$session s1, v$lock l2, v$session s2

Where s1.sid = l1.sid

And s2.sid = l2.sid

And l1.BLOCK = 1

And l2.request > 0

And l1.id1 = l2.id1

And l2.id2 = l2.id2

Note:

>: redirect the output, redirect the standard output of the file to the file, or use the data file as standard input to another program.

|: UNIX pipe: use the output of one file as the input of another file.

Thank you for your reading, the above is the content of "introduction to the mechanism of ORACLE lock". After the study of this article, I believe you have a deeper understanding of the introduction of the mechanism of ORACLE lock, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.