In-depth introduction to oracle locks-principles

In-depth introduction to oracle locks-principles: in modern multi-user multi-task systems, multiple users may access a shared object at the same time. This object may be a table, row, or memory structure, to solve the data security, integrity, and consistency problems caused by multi-user concurrent access, a mechanism is required to serialize the concurrent access to these shared resources, oracle locks can provide this function. Before a transaction operates on an object, it first sends a request to the system and adds the corresponding lock to the transaction, after the lock is applied, the transaction has certain control permissions on the Data Object. Before the transaction releases the lock, other transactions cannot update the data object (select can be performed, however, select uses the original image data in undo ). Www.2cto.com Oracle lock classification Oracle locks can basically be divided into two categories a: share locks also known as read locks, s lock B: exclusive locks also known as write locks, there are two basic lock types in the database: Exclusive Locks and Share Locks ). When an exclusive lock is applied to a data object, other transactions cannot read or modify it. Data Objects with a shared lock can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions. Lock-protected content classification oracle provides a multi-granularity blocking mechanism based on protected objects, which can be divided into a: dml locks and data locks, used to protect data integrity and consistency B: ddl lock, dictionary locks dictionary lock, used to protect the structure of data objects, such as table, index definition c: internal locks and latchs internal locks are used to protect the internal structure of the database, such as the sga memory structure DML lock: DML locks mainly include tmlock and TX locks, where tmlock is called table-level locks, tmlock types include S, X, SR, SX, and SRX. TX locks are called transaction locks or row-level locks. When Oracle executes the delete, update, insert, select for update DML statements, oracle first automatically applies for a TM lock on the table to be operated. After the tmlock is obtained, automatically apply for the TX lock and set the lock flag (lb) of the actually locked data row. After the record is locked by a session, other sessions that need to access the locked object will wait for the lock to be released in the first-in-first-out mode. For select operations, no lock is required, so even if the record is locked, the select statement can still be executed. In fact, in this case, oracle uses the undo content for consistent reading. In the Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time. The lock is kept until the transaction is committed or rolled back. There is only an X lock (exclusive lock) on the Data row. That is to say, the TX lock can only be an exclusive lock, and it makes no sense to set a shared lock on the record row. When two or more sessions execute DML statements on the same record of the table, the first session locks the record, and other sessions are in the waiting state. After the first session is submitted, the TX lock is released before other sessions can be locked. In a data table, oracle uses a shared lock by default. When executing a dml statement, oracle applies for a shared lock on the object to prevent other sessions from executing ddl statements on the object, after the shared lock on the application form is successfully applied, the affected records are added to the row to prevent other sessions from modifying the lock. In this way, when the consistency of the TX lock is checked before the transaction locks, the lock mark does not need to be checked row by row. Instead, you only need to check the compatibility of the tmlock mode, which greatly improves the system efficiency. The tmlock includes multiple modes, such as SS, SX, S, and X, which are represented by 0-6 in the database. Different SQL operations generate different types of tmlocks. See table 1. Introduction to the lock-related performance view: the sid of the v $ lockSID session. You can associate the TYPE with the v $ session to distinguish the TYPE of the lock protection object, such as tm, tx, rt, the ID1 locks such as mr indicate 1, and the ID2 locks indicate 2 for details. For details, see the LMODE locks. See the following description about the lock modes applied by the REQUEST, the same lmode CTIME has been held or wait for the lock time BLOCK whether to BLOCK other session lock apply 1: blocked 0: not blocked LMODE value 0, 1, 2, 3, 4, 5, 6, the larger the number, the higher the lock level, the more actions are affected. Level 1 lock: Select, sometimes in v $ locked_object. Level 2 (RS Lock): The corresponding SQL statements include Select for update, Lock xxx in Row Share mode, and select for update. When the dialog uses the for update substring to open a cursor, all data rows in the return set will be locked exclusively at the Row level (Row-X). Other objects can only query these data rows and cannot perform update, delete, or select for update operations. Level 3 locks, that is, RX locks: The corresponding SQL statements include Insert, Update, Delete, Lock xxx in Row Exclusive mode. If the same record is inserted before commit, no response is returned, because the last three locks will always wait for the last three locks, we must release the previous one to continue working. Level 4 Lock, that is, S Lock: The corresponding SQL includes: Create Index, Lock xxx in Share mode Level 5 Lock, that is, SRX Lock: The corresponding SQL includes: lock xxx in Share Row Exclusive mode. If the primary foreign key constraint Exclusive mode Exclusive, update/delete ...; locks may be 4 or 5. Level 6 Lock, that is, X Lock: The corresponding SQL statements include Alter table, Drop table, Drop Index, Truncate table, Lock xxx in Exclusive mode ID1, the value of ID2 varies according to the value of type. For TM lock ID1, The object_id of the locked table can be associated with the dba_objects view to obtain specific table information, the ID2 value is 0. For TX lock ID1, the rollback segment number occupied by the firm and the slot NUMBER of the transaction slot are expressed in decimal digits. The Group format is 0 xRRRRSSSS, RRRR = RBS/UNDO number, SSSS = SLOT NUMBERID2 represents the number of times that wrap in decimal format, that is, the number of times that the transaction SLOT is reused v $ locked_objectXIDUSN undo segment number, you can associate XIDSLOT undo slot number XIDSQN serial number OBJECT_ID of the locked object with v $ transaction. You can associate SESSION_ID with dba_objects to hold the session_id of the lock, you can associate the oracle account OS _USER_NAME with which ORACLE_USERNAME holds the lock with the oracle account OS _USER_NAME that holds the lock with the operating system account PROCESS. You can associate the process with the LOCKED_MODE lock mode, the meaning is the same as v $ lock. the content of lmode dba_locks is similar to that of v $ lock. For example, if a session is blocked because some rows are locked by other sessions, the following four fields in the View list the information about the objects to which these rows belong. ROW_WAIT_FILE # file number of the row to be waited; ROW_WAIT_OBJ # object_idROW_WAIT_BLOCK # blockROW_WAIT_ROW # manually release the lock alter system kill session 'sid, serial #';