Basic principles and principles of locks

Basic principles and principles of locks
Basic Principle of oracle032 lock
1. Oracle lock type
Lock function: protects data, and no concurrency is available without locks. The lock is used to limit concurrency.
Slow database response may not be due to serious negative data, or the cause of the lock may prevent transactions: when two clients are opened to modify data, it is obvious that the second one will be very slow because the first user locks the row record.

Latch Lock: chain (server Pro protects the chain by obtaining the latch Lock during concurrent access)
LOCK: buffer, data block, data row
Exclusive lock (X): that is, access lock a x, and access lock B cannot.
Shared lock (S): The same shared S lock can be applied to both A and B.
2. Row-level locks: DML statements; are the minimum granularity locks of LOCK; oracle also has the most impact on concurrency Efficiency
Transaction lock TX: A transaction lock generated by a row-Level Lock.
Lock Structure
The process of locking and unlocking transaction locks: DML statements (locking); commit/rollback (unlocking)
Only exclusive locks (Row-level locks and transaction locks are of exclusive locks)
Read (CR block) is not affected)
3. Table-Level Lock: TM

Row exclusive RX lock
When we perform DML, the rx lock will be automatically added to the updated table. You can execute the LOCK command to explicitly add the rx lock to the table.
Allow other transactions to use DML statements to modify other data rows in the same table
Allow the use of the lock command to add RX locks to a table
Other transactions are not allowed to add X locks to tables.
Row-level Shared lock (RS lock)
Select... From for update generates a transaction, which generates a row-Level Lock like a common DML statement and adds an RS lock at the table level without the RX lock. However, the data is not modified.
Share (S lock)
Use the lock table in share mode command to add the S lock
Exclusive (X lock): changes the table structure and deletes the table. Other users cannot perform any operation.
Use the lock table in exclusive mode command to add an X lock
Share Row Exclusive (SRX)
Use the lock table in share row exclusive mode command to add the SRX lock

Lock table in [row share] [row exclusive] [share] [share row exclusive] [exclusive] mode;

The study focuses on two points: the compatibility of the lock and the cause of the lock
4. Lock compatibility
5. Lock statement and lock release
6. Lock-related view
V $ transaction
XIDUSN indicates the number of rollback segments used by the current transaction
XIDSLOT indicates the number of records (or slots) corresponding to the transaction table in the rollback segment header)
XIDSQN indicates the serial number
STATUS indicates whether the transaction is active
V $ lock
Records the locks obtained by the session and the information about the locks being requested.
SID indicates the session ID
TYPE indicates the lock level, mainly focusing on TX and TM
LMODE indicates the lock mode that has been obtained, expressed in numerical Encoding
REQUEST indicates the locking mode of the REQUEST, which is represented by numerical encoding.
BLOCK indicates whether other users are blocked from being locked. If the value is greater than 0, the BLOCK indicates yes. If the value is equal to 0, the BLOCK indicates no.

Lock mode lock abbreviation encoding Value
Row Exclusive RX 3
Row Shared RS 2
Share S 4
Exclusive X 6
Share Row Exclusive SRX 5
Null n/A 0 or 1

V $ enqueue_lock
The fields and meanings in this view are exactly the same as those in v $ lock.
However, the view only displays the information about the sessions applied to be locked but cannot be locked.
The records are arranged in sequence according to the time when the lock is applied. The sessions applied for first are listed at the top, and the sessions listed at the top are locked first.

V $ locked_object
Records the information of the currently locked object
XIDUSN indicates the number of rollback segments used by the current transaction
XIDSLOT indicates the number of records corresponding to the transaction table in the rollback segment header.
XIDSQN indicates the serial number
OBJECT_ID indicates the ID of the currently locked object. You can use this ID to search for the name of the locked object in dba_objects.
LOCKED_MODE indicates the numerical encoding of the lock mode.

V $ session
Records information about the current session.
SID indicates the session number.
SERIAL # indicates the SERIAL number
SID and SERIAL # can be considered as the primary key of the v $ session, which uniquely identifies a session test lock:

Enable three sessions: SQL> select sid from v $ mystat where rownum = 1; SID ---------- 29 SQL> SQL> select sid from v $ mystat where rownum = 1; SID ---------- 35 SQL> SQL> select sid from v $ mystat where rownum = 1; SID ---------- 38 SQL> Use sessionID 29 to update the t table SQL> update t set name = 'qq' where id = 2; 1 row updated. SQL> Query transactions: select xidusn, xidslot, xidsqn, status from v $ transaction; 5 30 1099 ACTIVE Indicates that an active transaction exists. The following is the transaction information generated by querying the connection with sessionID 29: Select sid, type, id1, id2, Decode (lmode, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive') lock_mode, Decode (request, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive ') Request_mode, block From v $ lock Where sid = 29; SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK 29. AE 100 0 Share None 0 29 TM 74585 0 Row Exclusive None 0 // table lock 29 TX 327710 1099 Exclusive None 0 // row-Level Lock SID indicates sessionID, TYPE indicates the lock TYPE, ID2 indicates the number of times the transaction slot is overwritten, and ID1 contains two types of information: 1. transaction table 2. row in the transaction table Pass: Split ID1 Select trunc (327710/power (327710) as undo_blk #, bitand (, to_number ('ffff', 'xxxx') + 0 as slot # from dual; UNDO_BLK #, SLOT #5 30 // displays the transaction information, which also shows that ID1 represents the two types of information. LOCK_MODE indicates the lock mode, and REQUEST_MODE indicates whether to request a lock. BLOCK indicates the transaction to be locked: select OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS, NAMESPACE from dba_objects where object_id =; // locked object OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS, namespace sys t 74585 TABLE 22-12 months-14 04-1 month-15 VALID 1 for tmlock, ID1 indicates the ID of the locked object, and ID2 is always 0 For the TX lock, ID1 indicates the rollback segment number used by the transaction and the corresponding record number in the transaction table. ID2 indicates the number of times the record number is reused (wrap) Use sessionID 35 to update the t table: SQL> update t set name = 'ww 'where id = 2; // If no execution result is displayed, the following transaction information is generated for querying connections with sessionID 29 and 35: select sid, type, id1, id2, Decode (lmode, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive') lock_mode, Decode (request, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive ') Request_mode, block From v $ lock Where sid in (29,35) Order by sid; SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK29 TM 74585 0 Row Exclusive None 0 29. AE 100 0 Share None 0 29 TX 327710 1099 Exclusive None 1 // a transaction is locked 35 TM 74585 0 Row Exclusive None 0 35 AE 100 0 Share None 0 35 TX 327710 1099 None Exclusive 0 // request an Exclusive lock because the 29 above locks Use sessionID 38 to update the t table: SQL> update t set name = 'ww 'where id = 2; // If no execution result is displayed, it indicates that it is locked. Query v $ enqueue_lock to obtain the session information in the lock queue: Select sid, type, Decode (request, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive ') Request_mode From v $ enqueue_lock Where sid in (35,38 ); SID, TYPE, REQUEST_MODE35 AE None 38 AE None 35 TX Exclusive 38 TX Exclusive can know that the session ID is 35, and the 38 connection needs to be locked to the X lock. Query the relationship between several locks: select a. sid blocker_sid, a. serial #, a. username as blocker_username, B. type, Decode (B. lmode, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive') lock_mode, B. ctime as time_held, c. sid as waiter_sid, Decode (c. request, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive') request_mode, C. ctime time_waited From v $ lock B, v $ enqueue_lock c, v $ session Where. sid = B. sid and B. id1 = c. id1 (+) and B. id2 = c. id2 (+) and c. type (+) = 'tx 'and B. type = 'tx 'and B. block = 1 Order by time_held, time_waited; BLOCKER_SID, SERIAL #, BLOCKER_USERNAME, TYPE, LOCK_MODE, TIME_HELD, WAITER_SID, REQUEST_MODE, TIME_WAITED 29 557 sys tx Exclusive 2452 38 Exclusive 274 29 557 sys tx Exclusive 2452 35 Exclusive 712BLOCKER_SID: The sessionID of the lock is 29, BLOCKER_USERNAME: the user name of the lock is sys, TYPE: the lock TYPE is TX (Row-Level Lock) LOCK_MODE: the lock model is exclusive lock. TIME_HELD: the duration of the lock. WAITER_SID: The sessionID lock wait for the lock. REQUEST_MODE: the type of the Request lock. TIME_WAITED: the request time can be used to know the lock time held by a connection through the above information. If the request time is too long, it indicates that the connection is faulty, and the connection alter system kill session '123' can be killed '; // The first parameter is BLOCKER_SID, and the second parameter is SERIAL #. A transaction modifies multiple rows to generate a TX lock. Select sid from v $ mystat where rownum = 1; SQL> update t set name = 'rr 'where id = 2; 1 row updated. SQL> update t set name = 'rr 'where id = 2; 1 row updated. SQL> update t set name = 'rr 'where id = 2; 1 row updated. SQL> Select sid, type, id1, id2, Decode (lmode, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive') lock_mode, Decode (request, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row Exclusive', 4, 'share ', 5, 'share Row Exclusive ', 6, 'clusive') request_mode, block From v $ lock Where sid = 38; SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK38 AE 100 0 Share None 0 38 TM 74585 0 Row Exclusive None 0 38 TX 327680 1099 Exclusive None 0 The total number of TX locks that can be obtained is determined by the initialization parameter transactions, and the number of TM locks that can be obtained is determined by the initialization parameter dml_locks: Select name, value from v $ parameter where name in ('transactions', 'dml _ locks '); NAME, VALUEdml_locks 1084 Transactions 1, 271 Query resource usage: Select resource_name as "R_N", current_utilization as "C_U", max_utilization as "M_U", initial_allocation as "I _U" From v $ resource_limit Where resource_name in ('transactions', 'dml _ locks '); R_N, C_U, M_U, I _U dml_locks 1 1084 Transactions 1 1 271 the preceding parameters show that one dml_locks resource is currently used and only one is used at most. The maximum number of resources is 1084transactions, this can be used to determine whether the resource settings are reasonable. If the M_U value is equal to or close to the I _U value, the I _U value needs to be increased. Because: the total number of TX locks available is determined by the initialization parameter transactions, the number of TM locks that can be obtained is determined by the initialization parameter dml_locks.

Grant select on v _ $ mystat to hr;

Deadlock
Two sessions (represented by A and C). If A holds the lock that C is applying for and C also holds the lock that A is applying for, A deadlock may occur. A deadlock is A typical "double-lose" situation. If it is used for development, both session A and session C cannot end. Therefore, the DML statement that causes a deadlock in the Oracle database will be revoked. Deadlocks are always caused by unreasonable application design.
When a session transaction causes a deadlock, Oracle will automatically cancel the corresponding DML statements in other transactions that block the transaction, other DML statements in other transactions that block the transaction are not revoked.

Session 1
Select sid from v $ mystat where rownum = 1;
Update employees set last_name = last_name | 'A'
Where employee_id = 100

Session 2
Select sid from v $ mystat where rownum = 1;
Update employees set last_name = last_name | 'B'
Where employee_id = 101;

Session 1
Update employees set last_name = last_name | 'C' where Fig = 101;

Session 2
Update employees set last_name = last_name | 'd 'where employee_id = 100;

Unlock the HR user and set the password: SQL> select username, password, account_status from dba_users where username = 'hr '; USERNAME PASSWORD ------------------------------------------------------------ ACCOUNT_STATUS -------------------------------- HR EXPIRED & LOCKED // EXPIRED and LOCKED status SQL> alter user hr account unlock; User altered. SQL> select username, password, account_status from dba_users where username = 'hr '; USERNAME PASSWORD ------------------------------------------------------------ ACCOUNT_STATUS -------------------------------- HR EXPIRED // at this time, hr only expires and is not locked SQL> alter user hr identified hr; Alter user hr identified hr * ERROR at line 1: ORA-00924: missing BY keyword SQL> alter user hr identified by hr; User altered. SQL> select username, password, account_status from dba_users where username = 'hr '; USERNAME PASSWORD ------------------------------------------------------------ ACCOUNT_STATUS -------------------------------- HR OPEN // OPEN status SQL>