The setup blocking mechanism is mainly to control concurrent operations, block interference, and ensure the consistency and accuracy of data. There are three ways to block the Oracle database: shared blockade, exclusive blockade, shared update blocking [B: 8F4F63B9BB] Blocking type [/ b: 8f4f63b9bb] Oracle RDBMS blockade type can be divided into the following three categories:
1. Internal sealing interior sealing is used to protect the internal structure of the Oracle, implemented inside the system, users cannot access, so we don't have to do more understandings.
2, DDL level blockade (Dictionary / syntax) DDL level blockade is also controlled by Oracle RDBMS, which is used to protect data dictionaries and data definitions. Consistency and integrity. It is automatically locked when the system is automatically analyzed when the SQL definition statement is analyzed, without the need for users. Dictionary / syntax analysis blocks three categories: (1), dictionary operation lock: It is used to lock the data dictionary when the dictionary is operated, which is only one dictionary to be operated by one. (2), the dictionary definition lock: It is used to prevent grammar analysis when dictionary operations, which avoids the structure of a table while querying the dictionary. (3), the table definition lock: When a SQL statement is properly accessed, the project is prevented from being modified in the dictionary.
3, DML-level blocking DML-level blocking is used to control data manipulation in concurrent transactions, ensuring consistency and integrity of data, and its blocking object can be a table or row. For the user's data manipulation, Oracle can automatically block the manipulated data, but if there is a manipulation, it is necessary to perform a blockade to satisfy concurrent manipulation. The DML blockade can be locked by a user process in an explicit manner, or it can be implemented by some SQL statements. DML locks have three blockade methods: (1), shared blocked mode (2), exclusive (3), shared update blockade (Share Update)
SHARE, Exclusive is used to table blocking, and Share Update is used for row blocking. 1. The table blocking of the shared method of the shared method is blocked by all the data in the table, which is used to protect the consistency of query data, preventing other users from updating the blockped table. Other users can only apply a lock of the shared method to the table, without applying an exclusive block of the table, and the shared update lock can be applied, but the process with the shared update blocking is not allowed to update. Sharing all users of the table can only query the data in the table, but cannot be updated. The table blockade of the shared method can only be set by the user with the SQL statement. The base format is as follows: [quote: 04b72348bd] lock table
[,
] ... in Share Mode [NOWAIT] [/ quote : 04b72348bd] Execute this statement, a table blocking of one or more tables to apply a shared method. When the selection item NOWAIT is specified, if the block is temporarily unable to apply success, return and by the user decision to wait, or first perform other statements. Holding a shared lock, when there is one of the following conditions, the shared lock is released: a, execute the commit or rollback statement. B, exit the database (log off). C, the program stops running. The shared method is blocked frequently for consistency query, that is, the data in the query data period does not change.
2, exclusive means blocking exclusive mode table blocking is used to block all data in the block table, you can query the table, but can update the table, other users can no longer apply the table Any blockade (including sharing, exclusive or shared update blockade). Although other users cannot update the table, you can query the table. Doo-way table blocking can be displayed by the following SQL statement: Lock Table
[,
] ......... The statement INSERT, UPDATE, and DELETE are implied. With exclusive ways to block the blocked transaction, the blockade is released when there is one of the following conditions: (1), execute the commit or rollback statement. (2) Exit the database (3), the program stops running. Exone way blocking is usually used for updating data, and when a update transaction involves multiple tables, it can reduce the deadlock. The DML lock has three blockade methods: (1), shared a blocked mode (2), exclusive (3), shared update blocking (Share Update) where Share, Exclusive is used for table blocking, Share Update is used for row blocking.
1. The table blocking of the shared method of the shared method is blocked by all the data in the table, which is used to protect the consistency of query data, preventing other users from updating the blockped table. Other users can only apply a lock of the shared method to the table, without applying an exclusive block of the table, and the shared update lock can be applied, but the process with the shared update blocking is not allowed to update. Sharing all users of the table can only query the data in the table, but cannot be updated. The table blockade of the shared method can only be set by the user with the SQL statement. The base format is as follows: [quote: 04b72348bd] lock table
[,
] ... in Share Mode [NOWAIT] [/ quote : 04b72348bd] Execute this statement, a table blocking of one or more tables to apply a shared method. When the selection item NOWAIT is specified, if the block is temporarily unable to apply success, return and by the user decision to wait, or first perform other statements. Holding a shared lock, when there is one of the following conditions, the shared lock is released: a, execute the commit or rollback statement. B, exit the database (log off). C, the program stops running. The shared method is blocked frequently for consistency query, that is, the data in the query data period does not change.
2, exclusive means blocking exclusive mode table blocking is used to block all data in the block table, you can query the table, but can update the table, other users can no longer apply the table Any blockade (including sharing, exclusive or shared update blockade). Although other users cannot update the table, you can query the table. Doo-way table blocking can be displayed by the following SQL statement: Lock Table
[,
] ......... The statement INSERT, UPDATE, and DELETE are implied. With exclusive ways to block the blocked, the blockade is released when there is one of the following conditions: (1), execute the COMMIT or ROLLBACK statement. (2) Exit the database (3), the program stops running. Exone way blocking is usually used for updating data, and when a update transaction involves multiple tables, it can reduce the deadlock. 3, shared update blocking mode shared update blocking is blocked for a group or multi-line of a table, thus also referred to as a grade blockade. The table-level blockade ensures the consistency of the data, but the parallelism of operational data is reduced. The row-level blockade ensures that the user gets the updated line to this line is not modified by other users during this time. Therefore, the row-level lock can ensure the consistency of data and improve data operation. The row-level blockade can be obtained by two ways: (1), perform the following SQL blockade statement to obtain: Lock Table
[,
] .... in Share Update Mode [NOWAIT] (2), with the following Select ... for update statement: Select [, ] ... from
Where for Update of [, ] ..... [NOWAIT] Once the user applies a row-level blockade to a row, the user can query or update the blockked data line, other users can only query but not update Blocked data line. If other users want to update the data lines in the table, they must also apply a row lock. Even if multiple users use shared updates, they do not allow two transactions to update a table, and when they are updated, they are blocked in order to block the table, until the transaction is submitted or restored. Locks are always exclusive to lock. When there is one of the following conditions, the shared update lock is released: (1), execute the commit statement; (2), exit the database (3), the program stops running. Execute the Rollback operation cannot be released. From the above, it can be seen that Oracle RDBMS's locking mechanism solves compatibility and mutual exclusive problems of concurrent affairs. Compatible to ensure the concurrency of the transaction, mutual exclusion ensures the consistency of data. The compatibility and mutual exclusion relationship of different user locks are given by the following figure.
One of the last lines of the last row is provided as other users to set the Share Update lock on different lines. However, when the user 1 is updated on a line, the user 2 is only waiting for the user 1 to submit the transaction to update the row of the lock.
The last list of the last row is provided as other users to set the Share Update lock on different lines. However, when the user 1 is updated on a line, the user 2 is only waiting for the user 1 to submit the transaction to update the row of the lock. Dead lock
Although the blockade can effectively solve the concurrency operation, any resource exclusive is the risk of deadlock. For example, there are two transactions T1, T2, T1 applied exclusive blocked data A, and T2 applies exclusive blocked data B. It is assumed that T1 is to lock data B, since B has been blocked by T2, then T1 is placed in a waiting state, waiting B is released; now if t2 is blocked, because A has been blocked exclusive by T1, so T2 Also placed in a waiting state. This forms a state in which two transactions are waiting, and never end, this situation is called dead lock. In the Oracle system, the dead lock can be automatically discovered, and the minimum price is selected, that is, the least amount of work is complete, release all locks you have, and other transactions continue to work. From the system performance, you should minimize resource competition, increase throughput, so users should pay attention to the following points: 1. For Update and Delete operation, only blocks to do changes. , Submit immediately after completing the modification. 2, when multiple transactions are using shared updates to update, do not use shared blocked, and should be encapsulated, so other users can use row lock to increase parallelism. 3. Apply a concurrent transaction of a table as much as possible to improve the parallelism. 4. During the high application load, it is not appropriate to modify the underlying data structure (table, index, cluster and view).ッ ク and び び 说明
First, Shi Hao Book, Pro, Junyi, Sub-テ テ ブ ブ all updates:
1 亲 テ テ ブ レコ レコ レコ ド を ッ す す す す* Don't don't do it
3 pro テ テ ブ ブ レコ レコ ド ド 更* Finally updated the day-time だけ 更 更 す * Event up to the day when the day is blistering, the exclusive エ エ ー が Automatic に に, DB に に に き い
