Oracle 10g Reading Notes lock

The lock mechanism is used to manage concurrent access to shared resources.

In Oracle, the transaction should be committed at the appropriate time, because the transaction is very long or very large, generally there is no pressure on the system; row-level locks have no related overhead, the number of "Resources" that one or 1000000 row locks are dedicated to locking this information is the same;

Do not think that the lock upgrade is "better for the system" (for example, the use of table locks instead of row locks). The oracle lock upgrade does not have any benefits for the system and does not save any resources; it can also get concurrency and consistency, the data reader is not blocked by the writer.

Optimistic Locking using ORA_ROWSCN:

ORA_ROWSCN is built on the internal oracle system clock (SCN. In oracle, the SCN will be promoted every time it is submitted. Oracle maintains the ORA_ROWSCN at the block level unless it supports row-level maintenance during table creation.

Enable ROWDEPENDENCIES when creating a table, or use the online reconstruction function in DBMS_REDEFINITION.

Query statement: select id, dbms_rowid.rowid_block_number (rowid) blockno, ora_rowscn from table

Execution of INSERT, UPDATE, DELETE, MERGE, and select for update will be blocked, and the last addition of NOWAIT will not be blocked.

Oracle never locks the upgrade, but it performs lock conversion or lock escalation. There are mainly three types of locks:

1. DML lock: used to ensure that only one person can modify a row at a time. When you are processing a table, others cannot delete the table.

A. TX lock (transaction lock): The TX lock is obtained when the transaction initiates the first modification, and the lock is held until the transaction is committed or rolled back.

Oralce does not have a traditional lock manager and does not use the lock manager to maintain a long list for each row locked in the system. It simply finds the row to be locked and locks it.

There is an overhead space at the beginning of the data block where the row to be locked is located. A transaction table of the block is stored here, the size is determined by the two parameters of the CREATE statement when an object is created:

INITTRANS: the initial pre-allocated size. The default value is 2 for indexes and tables. This value is added to a frequently modified table, and the PCTFREE value needs to be increased accordingly.

MAXTRANS: maximum value that can be extended. The default value is 255, that is, the maximum number of concurrent transactions in this block. Oracle 10g. this parameter is no longer used.

B. TM lock: used to ensure that the table structure does not change when the table content is modified.

Each transaction can only get one TX lock, but how many objects can be modified to get the number of TM locks. The total number of locks can be defined by the DML_LOCKS parameter.

If the parameter is set to 0, DDL is not allowed. Use the alter table tablename disable table lock command to DISABLE the tm lock one by one.

2. DDL lock: DDL locks are automatically applied to objects in DDL operations to prevent these objects from being modified by other sessions.

A. exclusive lock: prevent other sessions from getting their own DDL lock or TM lock. This indicates that tables can be queried but cannot be modified during DDL operations.

Most DDL statements have an exclusive DDL lock. For example, alter table t add new_column date;

Exception: create index t_idx on t (x) online, which only attempts to obtain a low-level (mode 2) tmlock on the table, therefore, during the execution of DDL statements, the changes made to the table maintain a record. When the CREATE statement is executed, these changes are applied to the new index.

B. Shared lock: protects the structure of the referenced object so that it is not modified by other sessions, but data can be modified. When creating a stored compilation object (such as a process or view), the shared DDL lock is applied to the dependent object.

C. Disruptive parsing lock: allows an object to register its Dependencies with another object. When a session parses a statement, a parsing lock is applied to each object referenced by the statement. The purpose is to set the cached statement to invalid if the referenced object is modified.

View DBA_DLL_LOCKS to view the information. View Script: [ORACLE_HOME]/RDBMS/ADMIN \/catblock. SQL

3. Internal locks and latches: the latches are lightweight serializing devices used to coordinate multi-user access to shared data structures, objects, and files. Designed to maintain a very short period of time. Use atomic commands such as "test and setup" and "comparison and exchange" to handle latches.

Since the commands for setting and releasing latches are atomic, although multiple processes may request them at the same time, the operating system itself can ensure that only one process can test and set latches.