Shulou(Shulou.com)06/01 Report--

"oracle's Lock"

There are a few things you must know in the database:

1. Transactions are the core of the database, and they are good things

2. Submission should be delayed until the appropriate time. Do not commit too quickly to avoid putting pressure on the system, because even if the transaction is long or large, it generally does not put pressure on the system. The corresponding principle is: submit only when necessary, not in advance. The size of the transaction should only be based on business logic.

3. Keep the lock on the data for as long as possible as long as necessary. These locks are tools you can use, not to hold you back. Locks are not rare resources. On the contrary, you should keep locks on your data for a long time as long as you need them. Locks are not rare, and they can prevent other sessions from modifying information

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

5. don't think that lock upgrades will be better for the system (for example, using table locks instead of row locks). In Oracle, lock upgrade does not benefit the system and does not save any resources. Sometimes we use table locks, such as in batches, to ensure that you have access to all the resources needed by your batch program in this case

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

1. Types of Oracle locks:

1.1. Pessimistic lock:

This method takes effect before the user modifies the numeric value. For example, if the user intends to perform an update on a specific row that he selects and is visible on the screen (for example, by clicking a button), the row will be added with a lock. This row lock will continue to be applied until the application performs the user's changes and submits in the database.

Note: pessimistic locks apply only to stateful or connected environments. This means that your application has a continuous connection to the database directly, and you are the only one using this connection (at least during the life cycle of your transaction).

1.2. Optimistic lock:

That is, all locking actions are delayed until the update is about to be performed, in other words, we will modify the information on the screen without locking it first. (this locking method is OK in all environments, but using this method, the possibility of update failure increases) that is, when a user wants to update his data row, only to find that the data has been modified, then he has to start all over again.

①: optimistic locks using version columns

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

②: optimistic lock using sum test

The method of this version column is similar, but he calculates a "virtual" version column based on the data itself, to help explain the goals and concepts related to the sum test or hash function.

Note: optimistic concurrency control is recommended for most applications, but in optimistic concurrency control methods, the version column method is more likely to be used to add a timestamp column (rather than just a number).

2. Blocking:

There are five common DML statements in the data that may cause blocking: insert update delete merge and select for update

For a blocking seelct for update solution: just add the nowait sentence, and it won't block.

2.1.blocking insert

Insert blocking is rare. The most common situation is that you have a table with a primary key, or there is a uniqueness constraint on the table, and only two replies try to insert a row with the same value. If so, one session will be blocked.

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

2.2, blocked merge update delete

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

Both pessimistic and optimistic locks can use select for update nowait queries to verify that rows have not been modified. Pessimistic locks use this statement the moment the user intentionally modifies the data. Optimistic locks use 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 correct the data integrity problem.

Note: merge is essentially insert and update

3. Deadlock

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

4. Lock type of Oracle

There are three main types of locks in Oracle:

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 specific data row, or a table-level lock that locks all rows in a table

DDL lock: DDL data definition language, which generally refers to: create alter statement

DDL locks protect object structure definitions

Note: internal locks and latches: Oracle uses these locks to protect its internal data structures. (latch is a lightweight, low-level serialization device used by Oracle, which is functionally similar to locks.) in fact, latches are a common cause of competition in databases

5. DML lock: used to ensure that only one person can modify a row at a time, and that others cannot delete the table at this time

①: TX lock-transaction lock

The transaction starts automatically and the TX lock is held until the transaction executes commit (commit) or rollback (rollback). The TX lock is used as a queuing mechanism so that other replies can wait for the transaction to complete

②: TM lock-

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

(in Oracle11G R2 and later, you can set ddl_lock_timeout while DDL waits)

6. DDL locks: objects are automatically locked during DDL operations to protect them from being modified by other sessions

For example, if I perform a DDL operation alter table t, there is usually an exclusive DDL lock on table T to prevent other sessions from acquiring the DDL and TM locks for this table

There are 3 clock types of DDL locks:

①: exclusive DDL lock:

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

②: shared DDL lock:

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

③: interruptible parsing lock:

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

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

8. Mutex: a serialized device that is very similar to a latch. Mutex is another serialized device used in the database.

View locked objects, usernames, 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 dbathing objects do where lo.session_id = s.sid and do.object_id = lo.OBJECT_ID

The Select For Update statement of Oracle can lock the relevant resources immediately after reading the data, and prevent the data from being modified by other 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 commit)

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.