Oracle Lock Summary

"Oracle's Lock"

What you have to know in the database:

1, business is the core of the database, they are good things

2, should be deferred to the appropriate time before submission. Do not submit too quickly to avoid stress on the system because, even if the transaction is long or large, it generally does not cause pressure on the system. The corresponding principle is: when necessary to submit, do not advance. The size of the transaction should only be determined by the business logic.

3, as long as necessary, it should be as long as possible to maintain the data added to the lock. These locks are the tools you can use, not the wide berth, the lock is not a rare resource, but on the contrary, you should keep the lock on the data for a long time if you need it. Locks are not rare, and they can prevent other sessions from modifying information;

4. In Oracle, row-level locks have no associated overhead and none at all. Whether there is a row lock or 10,000 row locks, the number of resources dedicated to locking this information is the same. Of course, it's much more work to modify 10000 rows than to modify a row, but the number of resources required to lock 10000 rows is exactly the same as the number of resources required to lock a row, which is a fixed constant;

5. Do not assume that the lock escalation will be better for the system (for example, using a table lock instead of a row lock). In Oracle, lock escalation does not have any benefit to the system, nor does it save any resources. Sometimes we use table locks, for example, in batches, table locks are used to ensure that in this case you can access all the resources required for your batch process;

6, concurrency and consistency can be obtained at the same time. The data reader is not blocked by the data writer. The data writer is also not blocked by the data reader. This is one of the fundamental differences between Oracle and most other relational databases;

I. Types of Oracle Locks:

1.1, Pessimistic Lock:

This is done before the user modifies the value, for example, if the user intends to perform an update on a particular line that he chooses and is visible on the screen (for example, by tapping a button), the row will be added to the lock, and the row lock will continue to be applied to the application when it executes the user's modifications and commits in the database;

Note: Pessimistic locks are only available for stateful or connected environments. That means your app has a continuous connection to the database, and only you are using this connection (at least for the life of your transaction);

1.2. Optimistic Lock:

This means that all locked actions are deferred until the update is about to be performed, in other words, we modify the information on the screen without having to lock him first, (this method can be used in all environments, but with this approach, the likelihood of an update failure is increased) and that is, when the user wants to update his data row, But found that the data has been modified, then he must start again;

①: Optimistic lock with version column

This method is easy to implement, if you want to protect the database table from missing update problems, add a column on each of the corresponding tables, which is typically a column of number or date/timestamp type, usually maintained by a row trigger on the table. (The best way is to maintain it through the UPDATE statement itself)

②: Optimistic locking using the sum test

This version of the column method is very similar, but he is based on the data itself to calculate a "virtual" version of the column, in order to help explain the sum of the test or hash function objectives and concepts;

Note: Optimistic concurrency control is recommended for most applications, but in optimistic concurrency control, it is more likely to use the Version column method to add a timestamp column (not just a number)

2, blocking:

There are 5 common DML statements in the data that can cause blocking: Insert Update Delete Merge and select for update;

For a blocking SEELCT for update solution: Just add nowait words and he won't block;

2.1. Blocked insert

Insert blocking is rare, the most common case is: you have a table with a primary key, or a unique constraint on the table, there is a single two reply to try to insert a row with the same value, if so, one of the sessions will be blocked;

Note: Insert blocking occurs usually because the app allows the end user to generate primary key/unique column values. To avoid this, the easiest way to do this is to use a sequence or sys_guid () built-in function to generate the primary key/unique column value;

2.2. Blocked Merge Update delete

In an interactive application, you can query the data from the database, allow the end user to process the data, and then put it back in the database, and if an update or delete block occurs, there may be a missing update problem in your code; You can do this by using the Select for update nowait query to avoid this problem. This query can do this: Verify that the data has not been modified since you queried the data (to prevent loss of updates); Lock the line (prevent update or delete from being blocked)

Both pessimistic and optimistic locks can be used to verify that the row has not been modified by using the Select for Update nowait query. Pessimistic locks Use this statement at the moment the user intentionally modifies the data. Optimistic locking uses this statement when data is about to be updated in the database. This can not only solve the blocking problem in the application, but also can fix the data integrity problem;

Note: Merge is essentially insert and update

3. Deadlock

The important reason for the deadlock is that the foreign key is not indexed, and the second reason is that the bitmap index on the table is being updated concurrently

4. Type of lock for Oracle

Oracle has 3 main types of locks:

4.1. DML Lock: DML data Manipulation language, generally refers to: Select Insert Update Merge Delete

The DML lock mechanism allows data modifications to be performed concurrently, for example: a DML lock may be a lock on a particular data row, or a table-level lock on all rows in a table;

4.2. DDL Lock: DDL data Definition language, generally refers to: create ALTER statement

DDL locks can protect object structure definitions

Note: internal locks and Latches: Oracle uses these locks to protect its internal data structures. (The latch is a lightweight, low-level serialization device used by Oracle, functionally similar to a lock) in fact, the latch is a common cause of competition in the database;

5, DML Lock: To ensure that only one person at a time can modify a row, and then others can not delete the table;

①:TX Lock----Transaction lock

The start of the transaction is automatic, and the TX lock is held until the transaction commits (commits) or rolls back (rollback). The TX lock is used as a queueing mechanism so that other back-up can wait for the transaction to complete;

②:TM Lock----

TM locks are used to ensure that when the contents of a table are modified, the structure of the table does not change. For example, if you have updated a row in a table, you will also get a TM lock on the table, which will prevent another user from executing a drop or ALTER command on the table;

(In oracle11g R2 and later versions, you can set the ddl_lock_timeout when DDL waits)

6. DDL Lock: The object is automatically locked in the DDL operation, thus protecting the objects from being modified by other sessions;

For example, if I perform a DDL operation on the ALTER TABLE T, there will usually be an exclusive DDL lock on table T, to prevent other sessions from getting DDL Locks and TM locks for this table;

There are 3 clock types of DDL locks:

①: Exclusive DDL Lock:

This prevents other sessions from obtaining their own DDL or TM (DML) locks, which means that a table can be queried during DDL operations, but the table cannot be modified in any way;

②: Shared DDL Lock:

These locks protect the structure of the objects referenced by the lock so that they are not modified by other sessions, but allow the data to be modified;

③: Interruptible Parse Lock:

These locks allow an object, such as a query plan cached in a shared pool, to register its dependencies with other objects.

7. Latch: The latch is a lightweight serial device that coordinates multi-user access to shared data structures, objects, and files; (latch is a lightweight lock)

8, mutual exclusion Lock: is a very similar to the latch of the serialization device, the mutex is a database using another serialization device;

View locked objects, user names, and sessions

Sql> Select Lo.oracle_username,do.object_name,s.logon_time,lo.process,s.sid as session_id from V$locked_object Lo, V$session s,dba_objects do where lo.session_id = s.sid and do.object_id = lo. object_id;

Oracle's SELECT FOR UPDATE statement can be used to lock related resources immediately after reading the data, preventing the data from being modified by another session.

Manual Lock: Select ... for UPDATE statement

SELECT * from table_sfu where a = 1 for update; ----Lock the first row of data (unlocked only after a commit)

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1934428

Oracle Lock Summary