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

This article introduces the relevant knowledge of "what are the interview questions of Oracle lock". 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!

What is the function of Oracle database lock?

When multi-user system accesses and modifies data concurrently, it protects the consistency and integrity of data.

Lock is a mechanism that can control concurrency when multiple transactions access a database object at the same time.

Oracle uses locking mechanism to achieve high concurrency of the system, using different types of exclusive locks or shared locks to manage concurrent session operations on data. The database is a shared resource used by multiple users. When multiple users access and modify the same data concurrently, if there is no control over the concurrent operation, incorrect data may be read and stored, thus destroying the consistency and integrity of the database. Oracle database provides data concurrency, consistency and integrity between transactions through its locking mechanism. Locking occurs automatically and usually does not require user action.

Excuse me, where is the Oracle lock stored?

In an oracle database, there is no lock that really belongs to an object or data. The information of the oracle lock is a physical attribute of the data block, not logically belonging to a table or row. The lock mechanism of Oracle is a lightweight locking mechanism, which does not manage the data by building a lock list, but stores the lock as an attribute of the data block directly in the head of the data block. In the Oracle database, it does not lock a table or several rows, the lock exists as an attribute of the data block. In other words, each data block itself stores the information of the data in its own data block, which is called ITL (Interested Transaction List). If there is an active transaction on this data block, its information will be recorded here for subsequent operations to query, so as to ensure the consistency of the transaction.

One: according to the way the lock is acquired, the lock is divided into the following two types

Exclusive locks (Exclusive Locks, or X locks) and shared locks (Share Locks, that is, S locks).

1.1 exclusive lock: after the transaction sets an exclusive lock, the transaction acquires this resource alone, and another transaction cannot acquire the shared or exclusive lock of the same object before the transaction commits.

It can be understood as a write lock, which prevents the sharing of resources and is used to modify data. If one thing adds an exclusive lock to some data, other things cannot add any more locks to it until the thing is over and the exclusive lock is released.

1.2 shared lock: a shared lock enables one transaction to share access to a specific database resource, and another transaction can also access that resource or acquire the same shared lock. Shared locks provide high concurrency for transactions, which can easily lead to deadlocks and loss of data updates.

It can be understood as a read lock, and the data with a shared lock can only be shared and can no longer be written with an exclusive lock.

Second: according to the concurrency of the system, locks are divided into the following two types

Pessimistic lock and optimistic lock.

2.1 pessimistic lock:

The so-called pessimistic lock: as the name implies, it is very pessimistic, thinking that someone else will modify the same book every time you modify the data, so it will be locked every time you modify the data. In this way, when others modify the data, they have to wait until the lock is released. To ensure the consistency of the data.

There are two ways to be pessimistic about locking (in terms of the difference in SQL statements):

1. When performing a select xxx for update operation, the data is locked and only when comit or rollover is executed will it be released

two。 When performing the select xxx for update nowait operation, the data will also be locked, and others may return an ORA-00054 error when accessing it, indicating that the resource is busy and corresponding business measures need to be taken to deal with it.

2.2 optimistic locks:

The so-called optimistic lock: it is optimistic that every time you modify the data, you think that others will not modify the same data, so it will not be locked, but when you update it, you will judge whether others have updated the data during this period.

Therefore, you need to ensure data consistency at the time of submission, and if inconsistent, an error is returned, which is handled by the logic of the program itself.

There are three main ways to implement optimistic locks:

a. Judge whether there is a data conflict by comparing whether the data before and after submission has changed.

b. Indicate whether there has been a change by adding a version stamp column to the table

c. Compare the timestamps of the tables to determine whether there has been a version change.

This optimistic lock can be implemented through trigger or stored procedures.

Oracle is usually optimistic when updating data, and any command that starts with UPDATE...SET and does not operate with SELECT...FOR UPDATE is an example of optimistic locking.

Pessimistic lock and optimistic lock usage scenarios:

1. If the system concurrency is small and dirty reads are not allowed, pessimistic locks can be used to solve concurrency problems.

two。 If the system concurrency is very large, pessimistic locks will bring great performance problems, so optimistic locks are generally used.

3. If the system reads more and writes less, optimistic locks should also be used to improve throughput

Third, according to the different protection objects, Oracle database locks can be divided into the following categories

(1) DML lock (data locks, data lock): used to protect the integrity of data

(2) DDL lock (dictionary locks, dictionary lock): the structure used to protect database objects (such as the structure definition of tables, views, indexes)

(3) System Locks: protect the internal database structure.

3.1 DML Lock

DML locks (also known as data locks) ensure the integrity of data accessed concurrently by multiple users.

The DML statement automatically acquires the following types of locks:

3.1.1 Row locks (TX)

Row-level lock is the finest-grained DML lock, which is mainly used to control the modification and deletion of data rows. When you modify a row of data in the table, you need to add a row-level exclusive lock to it to prevent other things from modifying it, and release it automatically when the data is modified and things are submitted.

3.1.2 Table lock (TM)

The TM lock is used to ensure that the structure of the table does not change when the contents of the table are modified, such as preventing the related table from being removed during the execution of the DML statement. When a user performs a DDL or DML operation on a table, it acquires a table-level lock for that table.

When a transaction acquires a row lock, the transaction also automatically acquires the table lock of the row to prevent other transactions from making DDL statements to affect the update of the record row.

The TM lock includes many modes, such as SS, SX (RX), S, SRX (SSX), X and so on, which are represented by 0Mel 6 in the database. Different SQL operations result in different types of TM locks.

R is the ROW line, S is SHARE sharing, and X is exclusive exclusive, which means exclusive lock.

Lock mode: 0 lock description: none

Description: no lock, general SELECT, level 0 lock on both tables and rows

Lock mode: 1 lock description: NULL

Description: Select sometimes appears on v$locked_object. In fact, it does not play the role of locking, it just has a notification function, which can not stop DDL at all, similar to notifying the object in the execution plan to the session to which the object belongs.

Lock mode: 2 lock description: SS (Row-S) row-level shared lock

Description: table structure shared lock, only conflict with X, because others are shared lock, although RX,SRX also has X, but it is the X of the row, the table still means shared, level 2 lock does not conflict with level 0-5.

This lock, also known as a child shared table lock (SS), indicates that the transaction holding the lock on the table has locked the rows in the table and intends to update them. Row sharing lock is the least restrictive mode in table locks, which provides the highest degree of concurrency for tables.

Related operation: Select for update,Lock For Update,Lock Row Share

Lock mode: 3 lock description: SX (Row-X) row-level exclusive lock

Description: table structure shared lock + exclusive lock of the operated record (if there is a DML operation), this lock, also known as subexclusive table lock (SX), usually indicates that the transaction holding the lock has updated the table row or issued a SELECT. For update . SX locks allow other transactions to query, insert, update, delete, or lock rows in the same table concurrently. Therefore, SX locks allow many transactions to acquire SX and child shared table locks for the same table at the same time.

Related operations: Insert, Update, Delete, Lock Row Exclusive

Lock mode: 4 lock description: s (Share) shared lock

Description: table structure shared lock + all records shared lock (implied), shared table lock held by transaction allows other transactions to query the table (SELECT … Except for for update), but updates are allowed only if a single transaction holds a shared table lock. Because multiple transactions may hold a shared table lock at the same time, holding this lock is not sufficient to ensure that a transaction can modify the table.

Related operations: Create Index, Lock Share

Lock mode: 5 lock description: SRX (S/Row-X) shared row-level exclusive lock

Description: table structure shared lock + all records exclusive lock (implied), this lock, also known as share-subexclusive table lock (SSX), is more restrictive than shared locks. Only one transaction at a time can acquire an SSX lock on a given table. The SSX lock held by the transaction allows other transactions to query the table (except SELECT … For update) but does not update the table.

Related operation: Lock Share Row Exclusive

Lock mode: 6 lock description: X (Exclusive) exclusive lock

Description: table structure exclusive locks, which are the strictest, prohibit other transactions from executing any type of DML statement or placing any type of lock on the table.

Related operations: Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

Whether these modes seem particularly messy and difficult to understand, there is an easy-to-understand example on the Internet as follows:

Http://blog.itpub.net/30126024/viewspace-2156232/

Refer to Maclean's lecture and use a jewelry store as an analogy to illustrate:

The jewelry store can be visited for free, you can book it, you can buy it after a trial, and you can buy it all.

Category 0, people who visit the jewelry store for free

The first category is the old, weak and pregnant guests who visit the jewelry store for free.

The second category of people, booked a probation period, buy it for a few days first, and then buy it if you feel good after the trial.

In the third category, the purpose of going directly to the store is to buy immediately.

The fourth category of people wrap up the jewelry of the whole store for others to visit and book, but cannot buy or sell (this is called a read-only lock in ORACLE, only others are allowed to read, that is, people of category 0,1 are only allowed to come to the jewelry store, let others visit the jewelry store in a read-only way, and buying and selling are not allowed, and a fourth kind of person is still allowed, because although everyone wants to pack, but everyone's goal is to share, not monopolize. So it is compatible)

Type 5 people, there is only one difference between category 5 people and type 4 people, that is, after type 5 people take out the whole jewelry store, another type 5 people are no longer allowed to do so (this is called write locking in ORACLE), that is, type 5 people are single-aisle. You can only find one category 5 person in the jewelry store, but it is impossible to find the second category 5 person, but after category 5 people take out the jewelry store, they can still let the number 0, 1. Two types of people visit, but trading is not allowed.

The sixth category of people, it put the whole jewelry store down, do not allow anyone to visit purposefully, only allow free visit, it is exclusive, only allow 0BI people to visit, other people are not allowed

-- booked by category 2 above, so category 3 is not compatible with category 6

The above category 3 people want to buy jewelry, so category 3 is not compatible with category 4, 5, and 6 people.

3.2 DDL lock

The data dictionary (DDL) locks the definition of the schema object when an ongoing DDL operation acts on or references the object.

Only a single schema object that is modified or referenced is locked during a DDL operation. The database never locks the entire data dictionary.

The Oracle database automatically acquires DDL locks on behalf of any DDL transaction that requires DDL locks. The user cannot explicitly request an DDL lock. For example, if the user creates a stored procedure, the Oracle database automatically acquires DDL locks for all schema objects referenced in the procedure definition. The DDL lock prevents these objects from being changed or deleted before the process is compiled.

3.2.1 DDL exclusive lock

Exclusive DDL locks prevent other sessions from acquiring DDL or DML locks.

Most DDL operations require exclusive DDL locks on one resource to prevent disruptive interference with other DDL operations, which may modify or reference the same schema object. For example, when ALTER TABLE adds a column, DROP TABLE does not allow you to delete a table, and vice versa.

3.2.2 DDL shared Lock

Shared DDL locks for resources prevent destructive interference with conflicting DDL operations, but allow data concurrency for similar DDL operations.

These locks protect the structure of the referenced object from being modified by other sessions, but allow data to be modified.

For example, when you run a CREATE PROCEDURE statement, the included transaction acquires a shared DDL lock for all referenced tables. Other transactions can concurrently create procedures that reference the same table and acquire concurrent shared DDL locks on the same table, but no transaction can acquire exclusive DDL locks on any referenced table.

Shared DDL locks are valid during DDL statement execution and autocommit. Therefore, a transaction that holds a shared DDL lock ensures that the definition of the referenced schema object remains unchanged during the transaction.

3.2.3 DDL decomposable parsing lock

A parsing lock is called a decomposable parsing lock because it does not prohibit any DDL operations and can be decomposed into DDL operations that allow conflicts.

These locks allow one object, such as a query plan cached in a shared pool, to register its dependency with another object. Executing DDL,Oracle on a dependent object looks at a list of objects that have registered dependencies on that object and invalidates those objects. Therefore, these locks are "decomposable" and they do not prevent DDL from appearing.

3.3 system Lock

Oracle databases use various types of system locks to protect internal database and memory structures. These mechanisms are transparent to users.

3.3.1 Latch Latches

Latches are a simple low-level serialization mechanism used to coordinate multi-user access to shared data structures, objects, and files.

Generally speaking, latch consists of three memory elements: pid (process id), memory address, and memory length. Latch guarantees exclusive access to shared data structures to ensure that the integrity of the memory structure is not damaged. When multiple sessions modify or inspect the same memory structure in the sga at the same time, access must be serialized to ensure the integrity of the data structure in the sga. Latch will not cause blocking, but will only wait. After the current process releases latch, if multiple other processes request at the same time, there will be competition between them, without a queue mechanism. Once the previous process releases the lock, the latter process will rush forward, without the concept of first-come-first-come, and it all happens very quickly, because the characteristic of Latch is fast and short.

Most of the latch contention needs to consider the design of the system and database itself, such as whether the binding variables, hot blocks and parameter settings are reasonable.

If multiple users delete or modify the primary key, whether there are users using select. Syntax such as for update, whether foreign keys create indexes, and so on.

Latches protect shared memory resources from corruption when accessed by multiple processes. Specifically, latching protects the data structure from the following situations:

Concurrent modification of multiple sessions

When read by one session, it is modified by another session

Release reallocated memory when accessed

Typically, a single latch protects multiple objects in SGA, for example, background processes (such as DBWn and LGWR) allocate memory from shared pools (shared pool) to create data structures, and to allocate this memory, these processes serialize access with a shared pool latch to prevent both processes from checking or modifying shared pools at the same time. After memory is allocated, other processes may need to access the shared pool, such as the library cache (library cache), for parsing, in which case the process latch locks only the library cache and does not lock the entire shared pool.

Unlike queue locks, such as row locks, latches do not allow session queuing. When a latch is available, the first session that initiates the request gets the latch. When a process repeatedly requests a latch in a loop, called Latch spinning, while a process waits for the request latch to sleep to release CPU, it is called latch sleeping.

Typically, an Oracle process only acquires latches for a very short period of time when manipulating or viewing a data structure; for example, when processing an employee's salary update, the database can acquire and release locks thousands of times. The implementation of the latch depends on the operating system, especially the wait on how to handle the latch.

An increase in latches means a decrease in parallelism, for example, too many hard operations will compete for cache latches. The V$LATCH view contains statistics on the usage of each latch, including the number of requests and waits for each latch.

3.3.2 Mutex Mutexes

As a substitute for Latch, Mutex has the advantages of faster acquisition and smaller size.

It takes about 30,000 35 instructions to get a mutex feed, while it takes 150 million 200 instructions for Latch. The size of a mutex structure is about 16 bytes, while in version 10.2 a latch requires 112 bytes, and in earlier versions it was 200 bytes.

Mutexes (mutexes) are a low-level mechanism that prevents objects in memory from aging or corruption when accessed by concurrent processes. A mutex is similar to a latch, but a latch usually protects a set of objects, while a mutex protects a single object.

Mutexes have several benefits:

Mutexes can reduce contention.

Because a latch protects multiple objects, it can become a bottleneck when multiple processes try to access them at the same time. Mutexes slow down contention by serializing access to individual objects rather than groups.

Mutexes consume less memory than latches.

In shared mode, mutex locks allow many sessions to reference concurrently.

3.3.3 Internal lock Internal Locks

Internal locks are more advanced and complex mechanisms than latches and mutexes and can be used for a variety of purposes.

The database uses the following types of internal locks:

Dictionary cache lock

The duration of these locks is very short, and when dictionary cached entries are modified or used, they ensure that the parsed statement will not see inconsistent object definitions. Dictionary cache locks can be shared or exclusive. The shared lock is released when the parsing is complete, and the exclusive lock is released when the DDL operation is complete.

File and log management lock

These locks protect various files. For example, an internal lock protection control file ensures that only one process at a time can modify it. Another lock coordinates the use and archiving of online redo log files. The data file is locked to ensure that multiple instances mount the database in shared mode, or that one instance mounts the database in exclusive mode. Because file locks and log locks indicate the state of the file, these locks must be maintained for a long time.

Tablespaces and undo segment locks

These locks protect tablespaces and undo segments. For example, all instances that access a database must agree on whether the tablespace is online or offline. The undo segment is locked to ensure that only one database instance can be written to undo.

This is the end of the content of "what are the interview questions for the Oracle lock". 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.